Performance Tuning – SQL Elemental

Estrategias para optimizar consultas en SQL Server

En el desarrollo de bases de datos, la optimización de consultas es una tarea fundamental para garantizar un buen rendimiento del sistema. En este artículo, exploraremos algunas estrategias prácticas para optimizar consultas en SQL Server.

Cuando se trabaja con bases de datos SQL Server, es común encontrarse con consultas que no están optimizadas, lo que puede resultar en un rendimiento deficiente del sistema. La optimización de consultas implica identificar y resolver cuellos de botella en el rendimiento de las consultas SQL, lo que a su vez mejora la eficiencia y la velocidad de ejecución.

El primer paso en el proceso de optimización de consultas es identificar aquellas consultas que están afectando el rendimiento del sistema. Esto se puede lograr mediante el monitoreo del rendimiento del servidor SQL Server y la identificación de consultas que consumen una cantidad desproporcionada de recursos. Para esto se pueden usar muchas herramientas, en el pasado compartí algunos ejemplos de consultas para monitorear procedimientos almacenados que en realidad funcionan practicamente para cualquier tipo de codigo. Cuando busco consultas que estan dandome problemas habitualmente uso el sp_who2 o bien el sp_whoisactive de Adam Machinic, que a pesar de tener rato de no recibir actualizaciones aún es muy útil.

Una vez identificadas las consultas lentas, es importante analizar los planes de ejecución para entender cómo se están ejecutando esas consultas. El plan de ejecución es una representación visual del proceso que SQL Server sigue para ejecutar una consulta, y puede proporcionar información valiosa sobre posibles cuellos de botella y áreas de mejora.

Los índices son una herramienta fundamental para optimizar consultas en SQL Server. Al crear y mantener índices adecuados, es posible mejorar significativamente el rendimiento de las consultas al permitir un acceso más rápido a los datos.

Una herramienta muy util es el utilizar la visualización de las estadísticas de ejecución con encendiendo el set statistics io, time on;

Acá podemos ver cuáles son las tablas que estamos leyendo mas, que tipo de lectura estamos haciendo.

El diseño del esquema de la base de datos también juega un papel crucial en la optimización de consultas. Al diseñar tablas y relaciones de manera eficiente, es posible minimizar el tiempo de búsqueda y recuperación de datos, lo que contribuye a un mejor rendimiento del sistema en su conjunto.

La optimización de consultas en SQL Server es un proceso continuo que requiere un enfoque proactivo y constante. Al implementar estrategias como la identificación de consultas lentas, el análisis de planes de ejecución y el uso eficiente de índices, es posible mejorar significativamente el rendimiento del sistema y proporcionar una experiencia más rápida y eficiente a los usuarios finales.

Contador Page life Expectancy

Ultimamente hemos observado uno que otro problema de memoria y hemos utilizado ese contador de MSSQL, que en castellano podria ser algo asi posible duracion de la pagina en memoria. Algunas veces ni siquiera traduzco los terminos tecnicos y creo que la mayoria lo hacemos de igual manera. Ahora bien este contador es mas menos eso; me indica que tanto va a estar alojada una pagina en memoria sin referencia alguna.

Microsoft hace referencia a que un PLE de 300 es un buen numero, el problema de este dato es que ese calculo nunca se actualizo cuando fue mejorando el hardware; por lo que ese numero puede ser bueno en equipos pequeños pero en máquinas poderosas puede estar muy lejos de la realidad.

Mi recomendación es mirar este contador con lupa y ver si de alguna manera podría tener problemas de memoria sin darse cuenta.

La siguiente consulta lista los numeros del PLE

SELECT  @@servername AS Instancia ,
        [object_name] Objeto ,
        [counter_name] Contador ,
        [cntr_value] AS PLE_SEGUNDOS ,
        [cntr_value] / 60 AS PLE_MINUTOS ,
        [cntr_value] / 3600 AS PLE_HORAS
FROM    sys.dm_os_performance_counters
WHERE   [object_name] LIKE '%Manager%'
        AND [counter_name] = 'Page life expectancy'

Leyendo varios foros encontré una formula que utilizan muchos de los MVPs de SQL Server para determinar un PLE apropiado para cada equipo:

PLE Recomendado = (Tamaño en GB del Datos en Cache/4GB *300) en ingles por aquello de la traduccion: (DataCacheSizeInGB/4GB*300), como lo dice don Jonathan Kehayias

Este query trae un valor bastante cercano a lo que deberíamos esperar o buscar en realidad:

DECLARE @PLE DECIMAL

SELECT @PLE = ( ( ( bpool_committed * 8 ) / ( 1024 * 1024 ) ) / 4 ) * 300
FROM   sys . dm_os_sys_info

SELECT 'Recommended PLE' = CASE
WHEN @PLE &lt; 300 THEN 300
ELSE @PLE
END

SQL Server WAIT STATISCTICS ()

Los contadores de SQL Server asociados o agrupados como WAIT STATS son muy útiles a la hora de diagnosticar problemas de desempeño. De su nombre en Ingles podemos sacar que esta es información de procesos que tienen que esperar por alguna razón.

Cuando alguna operación o proceso en SQL Server tiene que esperar su turno para ser ejecutado el SQLOS guarda la información referente como estas estadísticas. Las vistas que mas consultan para indagar en nuestros tiempos de espera son sys.dm_os_wait_stats y sys.dm_os_waiting_tasks. En la vista sys.dm_os_wait_stats encontramos un resumen de todas la espera que se dio durante la ejecución de cualquier tarea. En la vista sys.dm_os_waiting_tasks encontramos información de los procesos que estan esperando por un recurso en este momento. La información que esta detallada puede ser muy útil si sabemos que nos esta diciendo realmente.

Es importante saber cual son los tipos de espera (wait_type) y que nos estan diciendo, a continuación una breve explicación de los tipos mas importantes, profundizar en ellos llevaría muchísimo tiempo

CXPACKET: habitualmente indica que se esta dando paralelismo en la ejecución de alguna de nuestras consultas
LATCH_* y PAGELATCH_*: no estan relacionados con lectura/escritura en disco duro. Se pueden asociar a problemas de contención con recursos internos al SQLOS como TEMPDB especialmente cuando se usan tipos de datos tipo texto. Habitualmente cuando se trabaja y se mejora los escenarios con los otros tipos de espera se mejora la situacion los *LATCH*
PAGEIOLATCH_*,IO_COMPLETION, WRITELOG: estos tipos se asocian a cuellos de botella en el sistema de almacenamiento, un numero alto me indicaria que estamos ante un problema de contencion de lectura/escritura. El PAGEIOLATCH se asocia a problemas de lectura/escritura en los archivos de datos(data files) y el WRITELOG a problemas de escritura en el LOG de la base de datos.
LCK_*: este tipo de esperas indican que se esta dando bloqueos en el sistema, y que los procesos estan esperando para poder obtener uso exclusivo de un recurso.
THREADPOOL: Este es un tipo especial de tiempo de espera, se da a lo interno del SQLOS; indica que el proceso ha tenido que esperar para obtener un hilo para entrar en la cola de procesamiento. Puede darse en casos de muchos bloqueos en los cuales se da mucho paralelismo en las tareas que se estan ejecutando y el SQLOS se queda sin hilos (worker threads) o bien indica la necesidad de adicionar procesadores a nuestro sistema.
SOS_SCHEDULER_YIELD: indica que la consulta cumplió su tiempo de ejecución en el procesador y cedio el espacio al siguiente proceso en cola. Valores altos habitualmente indican problemas de CPU
ASYNC_NETWORK_IO: habitualmente se define como un cuello de botella en la red; pero esto no es realmente valido. Muchas veces se puede ver cuando un cliente esta procesando una llamada registro a registro; cambiar este comportamiento involucra cambiar el código de la aplicación.

Para tener mas informacion se puede acceder a la pagina de Microsoft o bien me pueden escribir y con gusto tratare de ayudarles

TECHNET WAIT STATISTICS

Monitorear el uso de Procedimientos Almacenados

Hace un tiempo habia encontrado estas consultas para monitorear el uso de procedimientos almacenados en SQL 2005 y posterior. Los comparto porque siempre es util conocer que tanto y que tan frecuentemente se ejecutan nuestros procedimientos almacenados.

1. [Conteo Total de Ejecuciones]

SELECT 
    Db_name(st.dbid) [Base de Datos],
    Object_schema_name(st.objectid, dbid) [Schema],
    Object_name(st.objectid, dbid)        [USP],
    Max(cp.usecounts)                     [Total Ejecuciones]
FROM   
    sys.dm_exec_cached_plans cp
    CROSS apply sys.Dm_exec_sql_text(cp.plan_handle) st
WHERE  
    Db_name(st.dbid) IS NOT NULL
    AND cp.objtype = 'proc'
GROUP  BY 
    cp.plan_handle,
    Db_name(st.dbid),
    Object_schema_name(objectid, st.dbid),
    Object_name(objectid, st.dbid)
ORDER  BY 
        Max(cp.usecounts)

2. [Procedimiento que Consume mas CPU]

 SELECT 
    Db_name(st.dbid)
    [Base de Datos, Object_schema_name(st.objectid, dbid) [Schema],
    Object_name(st.objectid, dbid) [USP],
    Max(cp.usecounts) [Total Ejecuciones],
    Sum(qs.total_worker_time) [Consumo Total CPU],
    Sum(qs.total_worker_time) / ( Max(cp.usecounts) * 1.0 )    [Consumo Promedio CPU]
FROM   
    sys.dm_exec_cached_plans cp
    INNER JOIN sys.dm_exec_query_stats qs
        ON cp.plan_handle = qs.plan_handle
    CROSS apply sys.Dm_exec_sql_text(cp.plan_handle) st
WHERE  
    Db_name(st.dbid) IS NOT NULL
    AND cp.objtype = 'proc'
GROUP  BY 
    Db_name(st.dbid),
    Object_schema_name(objectid, st.dbid),
    Object_name(objectid, st.dbid)
ORDER  BY 
    Sum(qs.total_worker_time) DESC

3. [Procedimientos con mayor tiempo de ejecucion]

SELECT 
    Db_name(st.dbid) [Base de Datos, 
    Object_schema_name(objectid, st.dbid) [Schema],
    Object_name(objectid, st.dbid) [USP],
    Max(cp.usecounts) [Total Ejecuciones],
    Sum(qs.total_elapsed_time) [Tiempo Total Ejecuciones],
    Sum(qs.total_elapsed_time) / Max(cp.usecounts) [Tiempo Promedio Ejecuciones]
FROM   
    sys.dm_exec_query_stats qs
    CROSS apply sys.Dm_exec_sql_text(qs.plan_handle) st
    INNER JOIN sys.dm_exec_cached_plans cp
        ON qs.plan_handle = cp.plan_handle
WHERE  
    Db_name(st.dbid) IS NOT NULL
    AND cp.objtype = 'proc'
GROUP  BY 
    Db_name(st.dbid),
    Object_schema_name(objectid,st.dbid),
    Object_name(objectid, st.dbid)
ORDER  BY 
    Sum(qs.total_elapsed_time) DESC

Fuente SQL Journal