Optimizar consultas con muchos campos en el SELECT

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.

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.

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 />

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?

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?

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.