SQL Server Performance

Optimizar consultas con muchos campos en el SELECT

Discussion in 'Preguntas sobre SQL Server en Español.' started by rubegir, May 29, 2007.

  1. rubegir New Member

    Hola.

    Tengo desde hace poco a mi cargo una serie de bases de datos SQL Server 2000, SP3 y SP4. Me han indicado que van muy lentas para algunas acciones de las aplicaciones que las usan y he revisado cómo están parametrizadas las instancias, la memoria y disco de los servidores, etc. También he monitorizado lo que indica Microsoft en cuanto a contadores se refire con el monitor de rendimiento. Todos los indicadores están dentro del rango correcto indicado por Microsoft.

    Tras todo el trabajo, al final he reducido el problema a optimizar una serie de consultas que se usan muchísimo en la aplicación, pero que son muy costosas en tiempo.

    Para situarnos, trabajo con un servidor de estas características:
    - Microsoft Windows Server 2003 SP1
    - 2 procesadores Intel Xeon 3GHz. Proliant G4.
    - 2GB de RAM
    - Dos unidades de disco (con espacio suficiente) en RAID 5 por hardware.

    - Servidor SQLServer 2000 SP3. Utiliza Memoria fija: 1GB.
    - Software de SQL Server y bases de datos por defecto (así como tempdb) en C:. La base de datos de trabajo está en D:
    - Ficheros de la BD de trabajo: (total, 5.4GB)
    xxx_data.mdf -> 2.82GB Crecimiento automático(10%)
    xxx__log.mdf -> 2.60GB Crecimiento automático(10%)

    Las consultas son del siguiente tipo:

    SELECT "BALANCE"."CDT_0", "BALANCE"."CDT_1", "FACGROUP"."CPY_0", "BALANCE"."BPR_0", "GACCOUNT"."TYP_0" ....... y otros 60 campos más
    FROM ("GAHESA"."FACGROUP" "FACGROUP" LEFT OUTER JOIN "GAHESA"."BALANCE" "BALANCE"
    ON "FACGROUP"."FCY_0"="BALANCE"."FCY_0") LEFT OUTER JOIN "GAHESA"."GACCOUNT" "GACCOUNT"
    ON "BALANCE"."ACC_0"="GACCOUNT"."ACC_0"
    ORDER BY "GACCOUNT"."BSECDT_0"

    Como podéis observar, no hay cláusula Where, y el ORDER BY he comprobado que lo ejecuta relativamente rápido, repecto del resto de la sentencia y no es significativo que aparezca o no.
    Los campos "JOIN" están indexados, pero el plan de ejecución prefiere no usarlos y hace recorridos enteros sobre todas las tablas en vez de usar índices (las estadísticas son autómáticas y están actualizadas).

    El número de filas por tabla es:
    FACGROUP (9 campos) -> 3 filas
    BALANCE (122 campos) -> 49142 filas
    GACCOUNT (84 campos) -> 760 filas

    Y la consulta, ejecutada desde el analizador de consultas (en remoto, con conexión TCP/IP de 100Mbps) retorna 24573 filas tras 12 segundos.

    Si ejecuto la consulta en el propio servidor SQLSERVER (con conexión LPC) el tiempo se reduce a 9 segundos. Pero esto no es real, ya que las aplicaciones que usan la base de datos van sobre TCP/IP

    Y ahora lo que pienso que es la clave: el número de campos que retorna la select: 64
    Si ejecuto la misma consulta en vez de con 64 campos con 4 (uno de cada tabla del FROM), el plan de ejecución se mantiene constante (sin índices) pero el tiempo de ejecución ¡¡¡ se ha reducido de 12 a 2 segundos !!!

    Si ejecuto la consulta que retorna 4 campos en local (conexión LPC), el tiempo se reduce de ¡¡¡ 8 segundos a 1 segundos !!!

    Por tanto el cuello de botella está en el volumen de la información retornada por la select. Cuanto más "ancha" sea la fila que se retorne, más tarda en acabar.

    ¿Qué tengo que hacer para mejorar el rendimiento de la consulta con los 64 campos en el SELECT? ¿aumentar la memoria, la caché de disco ... ? HELP ME!!!!

    Muchísimas gracias por haber leido hasta aquí este extenso "topic".

    Ruben
    DBA SQL Server.
  2. Luis Martin Moderator

    Has probado usar el Index Tuning Wizard para ver si es necesario algún índice adicional?



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. rubegir New Member

    Muchas gracias por reponder.

    No, no lo he probado ... pero creo que tampoco areglaría nada añadir índices, dado que por las pruebas que he hecho, le tiempo de resolución de la select y el de "elegir" las filas afectadas por la select es inferior a 2 segundos. En el supuesto de que se deban añadir índices, mejoraría esa cota superior de dos segundos a algo menos ... y lo que necesito es bajar los otros 10 segundos que se tarda en pasar al cliente todos las filas.

    Es que es muy significativo: select con 64 campos, 12 segundos. Con 4 campos, 2 segundos sin que el plan de ejecución haya variado un ápice.

    Sigo investigando, aunque ya me queda muy poco de dónde tirar.
    En el servidor, el tanto por ciento de uso del procesador es inferior al 40%, y las páginas/seg en la memoria tienden a 0. La red es suficiente y la longitud de cola de espera en disco es inferior a 2 (lo recomendado por Microsoft). La verdad es que no sé dónde mirar más.

    De nuevo, muchas gracias.

    Saludos,
    Rubén.
  4. Luis Martin Moderator

    No estoy seguro que otro índices no ayuden al tiempo de respuesta.

    Me gustaría ver el resultado de lo siguiente:

    Usando el analizador de consultas con los 64 campos,

    set statistics io on

    Tu consulta...

    set statistics io off

    a ver que trae como resultado.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  5. rubegir New Member

    Ok, ojalá sea problema de índices [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />He ejecutado la consulta con las estadísticas de tiempo y de I/O activas. Estos son los resultados:<br /><br /><br /><b>SELECT con 64 campos: Tiempo, 13 segundos.</b><br /><br /><br /><i>Tiempo de ejecución de SQL Server:<br /> Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.<br /><br />Tiempo de ejecución de SQL Server:<br /> Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.<br />Tiempo de análisis y compilación de SQL Server: <br /> Tiempo de CPU = 47 ms., tiempo transcurrido = 48 ms.<br /><br />(24573 filas afectadas)<br /><br />Tabla 'BALANCE'. Número de exploraciones 1, lecturas lógicas 9830, lecturas físicas 0, lecturas anticipadas 0.<br />Tabla 'FACGROUP'. Número de exploraciones 1, lecturas lógicas 1, lecturas físicas 0, lecturas anticipadas 0.<br />Tabla 'GACCOUNT'. Número de exploraciones 1, lecturas lógicas 41, lecturas físicas 0, lecturas anticipadas 0.<br /><br />Tiempo de ejecución de SQL Server:<br /> Tiempo de <b>CPU = 734 ms</b>, tiempo transcurrido = <b>12675 ms</b>.<br />Tiempo de análisis y compilación de SQL Server: <br /> Tiempo de CPU = 0 ms., tiempo transcurrido = 0 ms.</i><br /><br /><br /><b>SELECT con 32 campos: Tiempo, 7 segundos.</b><br /><br /><i>Tiempo de ejecución de SQL Server:<br /> Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.<br /><br />Tiempo de ejecución de SQL Server:<br /> Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.<br />Tiempo de análisis y compilación de SQL Server: <br /> Tiempo de CPU = 40 ms., tiempo transcurrido = 40 ms.<br /><br />(24573 filas afectadas)<br /><br />Tabla 'BALANCE'. Número de exploraciones 1, lecturas lógicas 9830, lecturas físicas 0, lecturas anticipadas 0.<br />Tabla 'FACGROUP'. Número de exploraciones 1, lecturas lógicas 1, lecturas físicas 0, lecturas anticipadas 0.<br />Tabla 'GACCOUNT'. Número de exploraciones 1, lecturas lógicas 41, lecturas físicas 0, lecturas anticipadas 0.<br /><br />Tiempo de ejecución de SQL Server:<br /> Tiempo de <b>CPU = 469 ms</b>, tiempo transcurrido = <b>6456 ms</b>.<br />Tiempo de análisis y compilación de SQL Server: <br /> Tiempo de CPU = 0 ms., tiempo transcurrido = 0 ms.</i><br /><br /><b>SELECT con 4 campos: Tiempo, 2 segundos.</b><br /><br /><br /><i>Tiempo de ejecución de SQL Server:<br /> Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.<br /><br />Tiempo de ejecución de SQL Server:<br /> Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.<br />Tiempo de análisis y compilación de SQL Server: <br /> Tiempo de CPU = 41 ms., tiempo transcurrido = 41 ms.<br /><br />(24573 filas afectadas)<br /><br />Tabla 'FACGROUP'. Número de exploraciones 1, lecturas lógicas 1, lecturas físicas 0, lecturas anticipadas 0.<br />Tabla 'GACCOUNT'. Número de exploraciones 2, lecturas lógicas 41, lecturas físicas 0, lecturas anticipadas 0.<br />Tabla 'BALANCE'. Número de exploraciones 2, lecturas lógicas 9830, lecturas físicas 0, lecturas anticipadas 0.<br /><br />Tiempo de ejecución de SQL Server:<br /> Tiempo de <b>CPU = 282 ms</b>, tiempo transcurrido = <b>1012 ms.</b><br />Tiempo de análisis y compilación de SQL Server: <br /> Tiempo de CPU = 0 ms., tiempo transcurrido = 0 ms.</i><br /><br />En fin, como ves, no varían nada los resultados de I/O en función del número de campos ... pero sí que varía el tiempo de respuesta.<br /><br />De nuevo, muchas gracias. <br />Saludos,<br />Rubén.<br /><br />
  6. rubegir New Member

    Bueno, sigo investigando y todo indica que mis sospechas son correctas. Las estadísticas de cliente para la consulta con 64 campos en el SELECT son:

    Estadísticas del perfil de la aplicación
    Resolución del temporizador (milisegundos)00
    Número de instrucciones INSERT, UPDATE, DELETE00
    Filas afectadas por instrucciones INSERT, UPDATE, DELETE00
    Número de instrucciones SELECT11
    Filas afectadas por instrucciones SELECT2459424594
    Número de transacciones del usuario55
    Tiempo medio de recuperación00
    Tiempo de recuperación acumulado00
    Número de recuperaciones00
    Número de controladores de instrucciones abiertas00
    Número máx. de controladores de instrucciones abiertas00
    Número acumulado de controladores de instrucciones00

    Estadísticas de red
    Número de viajes de ida y vuelta del servidor33
    Número de paquetes TDS enviados33
    Número de paquetes TDS recibidos26852685
    Número de bytes enviados34623462
    Número de bytes recibidos1,05162e+0071,05162e+007

    Estadísticas de tiempo
    Tiempo acumulado de procesamiento del cliente22
    Tiempo de espera acumulado de respuestas del servidor290290



    Lo interesante son los pauqetes TDS recibidos (2685). El volumen de datos recibido es unos 10MB.

    Las estadísticas para 4 campos en el SELECT son:
    Estadísticas del perfil de la aplicación
    Resolución del temporizador (milisegundos)00
    Número de instrucciones INSERT, UPDATE, DELETE00
    Filas afectadas por instrucciones INSERT, UPDATE, DELETE00
    Número de instrucciones SELECT11
    Filas afectadas por instrucciones SELECT2459424594
    Número de transacciones del usuario55
    Tiempo medio de recuperación00
    Tiempo de recuperación acumulado00
    Número de recuperaciones00
    Número de controladores de instrucciones abiertas00
    Número máx. de controladores de instrucciones abiertas00
    Número acumulado de controladores de instrucciones00

    Estadísticas de red
    Número de viajes de ida y vuelta del servidor33
    Número de paquetes TDS enviados33
    Número de paquetes TDS recibidos1961440,5
    Número de bytes enviados34703466
    Número de bytes recibidos7585175,63735e+006

    Estadísticas de tiempo
    Tiempo acumulado de procesamiento del cliente01
    Tiempo de espera acumulado de respuestas del servidor13151,5


    Los TDS recibidos son 196. Y el volumen de datos, no llega a 1MB.

    He intentado optimizar con diferentes tamaños de paquete TDS (por defecto 4KB), pero sin resultado. El rango del paquete TDS va de 0.5KB a 64KB. Con 64KB, los TDS recibidos son 470 en la consulta con 64 campos en el select ... pero a pesar de haber reducido el número de paquetes recibidos, el tiempo no ha bajado de los 12 segundos.

    ¿Cómo optimizar las comunicaciones cliente-servidor y sus bibliotecas de Red?
  7. rubegir New Member

    Y por último, he ejecutado el asistente de optimización de índices. Luis, acertaste y me sugirió un cambio. Uno de los índices que ya tenía recomendaba hacerlo agrupado.
    Así que lo he recosntruido (esta vez, agrupado) y este es el resultado:

    Tiempo de ejecución de SQL Server:
    Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.
    Tiempo de análisis y compilación de SQL Server:
    Tiempo de CPU = 60 ms., tiempo transcurrido = 60 ms.

    (24594 filas afectadas)

    Tabla 'BALANCE'. Número de exploraciones 3, lecturas lógicas 4930, lecturas físicas 0, lecturas anticipadas 0.
    Tabla 'FACGROUP'. Número de exploraciones 1, lecturas lógicas 1, lecturas físicas 0, lecturas anticipadas 0.
    Tabla 'GACCOUNT'. Número de exploraciones 1, lecturas lógicas 41, lecturas físicas 0, lecturas anticipadas 0.

    Tiempo de ejecución de SQL Server:
    Tiempo de CPU = 1266 ms, tiempo transcurrido = 13061 ms.
    Tiempo de análisis y compilación de SQL Server:
    Tiempo de CPU = 0 ms., tiempo transcurrido = 0 ms.



    Las lecturas lógicas se han reducido casi a la mitad ... pero el tiempo de respuestá sigue estando entorno a los 13 segundos ... lo dicho, ¿cómo optimizar la comunicación cliente/servidor?
  8. Luis Martin Moderator

    Entonces lo único que se me ocurre es que el problema está en la comunicación en sí.

    Es evidente que no hay problemas en el select en si.

    Quizás sería buena idea que la gente de redes revise que la parte física se encuentre en buenas condiciones.

    Lamento no poder sugerirte alguna otro cosa. Todo el resto para mi está bien y no pasa por tener más memoria u otros discos.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  9. rubegir New Member

    Ok, muchas gracias, Luis. Lo comentaré con la gente de Sistemas, aunque creo que no tiene que ver, ya que la red de 100Mbps pienso que es suficiente ... y el problema se reproduce también en local (la conexión no es vía red, sino por memoria compartida)

    Pienso que el cuello de botella está en la comunicación del servidor al cliente vía bibliotecas de Red, y el protocolo interno TDS (Tabular Data streams) que usan. ¿Cómo optimizarlo, no lo sé)

    De nuevo, muchas gracias.

    Saludos.
  10. Luis Martin Moderator

    Lamento no haberte ayudado.

    Saludos,


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



Share This Page