Solución avanzada de problemas y ajuste del rendimiento de SQL Server

Capítulo 4. Consultas ineficaces

Este trabajo se ha traducido utilizando IA. Agradecemos tus opiniones y comentarios: [e-mail]

Las consultas ineficaces a existen en todos los sistemas. Afectan al rendimiento de muchas formas, sobre todo aumentando la carga de E/S, el uso de CPU y los bloqueos. Es esencial detectarlas y optimizarlas.

Este capítulo trata de las consultas ineficaces y de su posible impacto en tu sistema, y proporciona directrices para detectarlas, empezando por un enfoque que utiliza estadísticas de ejecución basadas en la caché de planes. También cubre los Eventos Extendidos, las Trazas SQL y el Almacén de Consultas, y termina con algunas reflexiones sobre herramientas de monitoreo de terceros. En capítulos posteriores trataremos las estrategias para optimizar las consultas ineficaces.

El impacto de las consultas ineficaces

Durante mi carrera como ingeniero de bases de datos, todavía no he visto un sistema que no se beneficiara de la optimización de consultas. Estoy seguro de que existen; al fin y al cabo, nadie me llama para ver sistemas perfectamente sanos. Sin embargo, son pocos y siempre hay oportunidades para mejorar y optimizar.

Sin embargo, no todas las empresas dan prioridad a la optimización de las consultas. Es un proceso largo y tedioso, y en muchos casos es más barato, dadas las ventajas de acelerar el desarrollo y el tiempo de comercialización, lanzar hardware al problema que invertir horas en ajustar el rendimiento.

En algún momento, sin embargo, ese enfoque conduce a problemas de escalabilidad. Las consultas mal optimizadas afectan a los sistemas desde muchos ángulos, pero quizás el más obvio sea el rendimiento del disco. Si el subsistema de E/S no puede soportar la carga de grandes exploraciones, el rendimiento de todo tu sistema se resentirá.

Puedes enmascarar este problema hasta cierto punto añadiendo más memoria al servidor. Esto aumenta el tamaño de la reserva de memoria intermedia y permite a SQL Server almacenar en caché más datos, reduciendo la E/S física. Sin embargo, a medida que la cantidad de datos en el sistema crece con el tiempo, este enfoque puede resultar poco práctico o incluso imposible, especialmente en las ediciones no Enterprise de SQL Server que restringen el tamaño máximo de la reserva de memoria intermedia.

Otro efecto de que hay que vigilar es que las consultas no optimizadas queman CPU en los servidores. Cuantos más datos procesas, más recursos de CPU consumes. Un servidor puede gastar sólo unos microsegundos por lectura lógica y exploración de páginas de datos en memoria, pero eso se acumula rápidamente a medida que aumenta el número de lecturas.

De nuevo, puedes enmascararlo añadiendo más CPUs al servidor. (Ten en cuenta, sin embargo, que tienes que pagar licencias adicionales y también tendrías límites en el número máximo de CPUs en las ediciones que no sean Enterprise). Además, añadir CPU no siempre resuelve el problema, ya que las consultas no optimizadas seguirán contribuyendo al bloqueo. Aunque hay formas de reducir el bloqueo sin realizar un ajuste de las consultas, esto puede cambiar el comportamiento del sistema y tiene implicaciones para el rendimiento.

La conclusión es la siguiente: cuando resuelvas los problemas de un sistema, analiza siempre si las consultas están mal optimizadas. Una vez que lo hayas hecho, calcula el impacto de esas consultas ineficientes.

Aunque la optimización de consultas siempre beneficia a un sistema, no siempre es sencilla, ni siempre proporciona el mejor rendimiento de tus esfuerzos. No obstante, la mayoría de las veces, necesitarás al menos afinar algunas consultas.

Para poner las cosas en perspectiva, realizo el ajuste de consultas cuando observo un alto rendimiento del disco, bloqueos o una alta carga de CPU en el sistema. Sin embargo, inicialmente puedo centrar mis esfuerzos en otra cosa si los datos se almacenan en caché en la reserva de búferes y la carga de la CPU es aceptable. Sin embargo, tengo que tener cuidado y pensar en el crecimiento de los datos; es posible que los datos activos superen un día la reserva de búferes, lo que podría provocar un impacto repentino y grave en el rendimiento.

Afortunadamente, ¡la optimización de consultas no requiere un enfoque de todo o nada! Puedes conseguir mejoras espectaculares de rendimiento optimizando sólo un puñado de consultas ejecutadas con frecuencia. Veamos algunos métodos para detectarlas.

Estadísticas de ejecución basadas en la caché del plan

En la mayoría de los casos, SQL Server almacena en caché y reutiliza los planes de ejecución de las consultas. Para cada plan en la caché, también mantiene estadísticas de ejecución, incluyendo el número de veces que se ejecutó la consulta, el tiempo de CPU acumulado y la carga de E/S. Puedes utilizar esta información para localizar rápidamente las consultas que consumen más recursos y optimizarlas. (En el Capítulo 6 hablaré con más detalle del almacenamiento en caché de los planes).

Analizar las estadísticas de ejecución basadas en la caché de planes no es la técnica de detección más completa; tiene bastantes limitaciones. Sin embargo, es muy fácil de utilizar, y en muchos casos es suficientemente buena. Funciona en todas las versiones de SQL Server y siempre está presente en el sistema. No necesitas configurar ningún monitoreo adicional para recoger los datos.

En puedes obtener estadísticas de ejecución utilizando la vista sys.dm_exec_query_stats, como se muestra en el Listado 4-1. Esta consulta es un poco simplista, pero demuestra la vista en acción y te muestra la lista de métricas expuestas en la vista. La utilizaré para construir una versión más sofisticada del código más adelante en el capítulo. Dependiendo de tu versión de SQL Server y del nivel de parcheado, es posible que algunas de las columnas de los scripts de éste y otros capítulos no sean compatibles. Elimínalas cuando éste sea el caso.

El código te proporciona los planes de ejecución de las consultas. Hay dos funciones que te permiten obtenerlos:

sys.dm_exec_query_plan

Esta función devuelve el plan de ejecución de todo el lote de ejecución en formato XML. Debido a limitaciones internas de la función, el tamaño del XML resultante no puede superar los 2 MB, y la función puede devolver NULL para los planes complejos.

sys.dm_exec_text_query_plan

Esta función, que utilizo en el Listado 4-1, devuelve una representación textual del plan de ejecución. Puedes obtenerlo para todo el lote o para una sentencia concreta del lote proporcionando el desplazamiento de la sentencia como parámetro a la función.

En el Listado 4-1, estoy convirtiendo los planes a la representación XML utilizando la función TRY_CONVERT, que devuelve NULL si el tamaño de XML supera los 2 MB. Puedes eliminar la función TRY_CONVERT si necesitas tratar con planes de gran tamaño o si ejecutas el código en SQL Server 2005 a 2008R2.

Listado 4-1. Utilizar la vista sys.dm_exec_query_stats

;WITH Queries
AS
(
    SELECT TOP 50
       qs.creation_time AS [Cached Time]
       ,qs.last_execution_time AS [Last Exec Time]
       ,qs.execution_count AS [Exec Cnt]
       ,CONVERT(DECIMAL(10,5),
            IIF
            (
                DATEDIFF(SECOND,qs.creation_time, qs.last_execution_time) = 0
                ,NULL
                ,1.0 * qs.execution_count / 
                    DATEDIFF(SECOND,qs.creation_time, qs.last_execution_time)
            )
        ) AS [Exec Per Second]
        ,(qs.total_logical_reads + qs.total_logical_writes) / 
            qs.execution_count AS [Avg IO]
        ,(qs.total_worker_time / qs.execution_count / 1000) 
            AS [Avg CPU(ms)]
        ,qs.total_logical_reads AS [Total Reads]
        ,qs.last_logical_reads AS [Last Reads]
        ,qs.total_logical_writes AS [Total Writes]
        ,qs.last_logical_writes AS [Last Writes]
        ,qs.total_worker_time / 1000 AS [Total Worker Time]
        ,qs.last_worker_time / 1000 AS [Last Worker Time]
        ,qs.total_elapsed_time / 1000 AS [Total Elapsed Time]
        ,qs.last_elapsed_time / 1000 AS [Last Elapsed Time]
        ,qs.total_rows AS [Total Rows] 
        ,qs.last_rows AS [Last Rows] 
        ,qs.total_rows / qs.execution_count AS [Avg Rows]
        ,qs.total_physical_reads AS [Total Physical Reads]
        ,qs.last_physical_reads AS [Last Physical Reads]
        ,qs.total_physical_reads / qs.execution_count 
            AS [Avg Physical Reads]
        ,qs.total_grant_kb AS [Total Grant KB]
        ,qs.last_grant_kb AS [Last Grant KB]
        ,(qs.total_grant_kb / qs.execution_count) 
            AS [Avg Grant KB] 
        ,qs.total_used_grant_kb AS [Total Used Grant KB]
        ,qs.last_used_grant_kb AS [Last Used Grant KB]
        ,(qs.total_used_grant_kb / qs.execution_count) 
            AS [Avg Used Grant KB] 
        ,qs.total_ideal_grant_kb AS [Total Ideal Grant KB]
        ,qs.last_ideal_grant_kb AS [Last Ideal Grant KB]
        ,(qs.total_ideal_grant_kb / qs.execution_count) 
            AS [Avg Ideal Grant KB] 
        ,qs.total_columnstore_segment_reads
            AS [Total CSI Segments Read]
        ,qs.last_columnstore_segment_reads 
            AS [Last CSI Segments Read]
        ,(qs.total_columnstore_segment_reads / qs.execution_count)
            AS [AVG CSI Segments Read]
        ,qs.max_dop AS [Max DOP]
        ,qs.total_spills AS [Total Spills]
        ,qs.last_spills AS [Last Spills]
        ,(qs.total_spills / qs.execution_count) AS [Avg Spills]
        ,qs.statement_start_offset
        ,qs.statement_end_offset
        ,qs.plan_handle
        ,qs.sql_handle
    FROM 
        sys.dm_exec_query_stats qs WITH (NOLOCK)
    ORDER BY
        [Avg IO] DESC
)
SELECT
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
    ((
        CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2)+1) AS SQL
    ,TRY_CONVERT(xml,qp.query_plan) AS [Query Plan]
    ,qs.*
FROM 
    Queries qs
        OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
        OUTER APPLY 
            sys.dm_exec_text_query_plan
            (
                qs.plan_handle
                ,qs.statement_start_offset
                ,qs.statement_end_offset
            ) qp
OPTION (RECOMPILE, MAXDOP 1);

Puedes ordenar los datos de forma diferente en función de tus objetivos de ajuste: por E/S cuando necesites reducir la carga del disco, por CPU en sistemas con CPU, etc.

La Figura 4-1 muestra la salida parcial de la consulta de uno de los servidores. Como puedes ver, es fácil elegir las consultas que se van a optimizar en función de la frecuencia de ejecución de las consultas y de los datos de consumo de recursos en la salida.

Los planes de ejecución que obtienes en la salida no incluyen métricas de ejecución reales. En este sentido, son similares a los planes de ejecución estimados. Tendrás que tener esto en cuenta durante la optimización (hablaré más de esto en el Capítulo 5).

Este problema de puede solucionarse en SQL Server 2019 y posteriores, y en Azure SQL Databases, donde puedes habilitar la recopilación del último plan de ejecución real de la sentencia en las bases de datos con nivel de compatibilidad 150. También debes activar la opción de base de datos LAST_QUERY_PLAN_STATS. Como ocurre con cualquier recopilación de datos, habilitar esta opción introduciría una sobrecarga en el sistema; sin embargo, esta sobrecarga es relativamente pequeña.

Puedes acceder al último plan de ejecución real a través de la función sys.dm_exec_query​_plan_stats. Puedes sustituir la función sys.dm_exec​_text_query_plan por la nueva función en todos los ejemplos de código de este capítulo: seguirán funcionando.

Hay otras limitaciones importantes que debes recordar. En primer lugar, no verás ningún dato de las consultas que no tengan planes de ejecución almacenados en caché. Es posible que te pierdas algunas consultas ejecutadas con poca frecuencia con planes desalojados de la caché. Normalmente, esto no es un problema; las consultas ejecutadas con poca frecuencia rara vez necesitan ser optimizadas al principio de la sintonización.

Figura 4-1. Salida parcial de la vista sys.dm_exec_query_stats

Sin embargo, existe otra posibilidad. SQL Server no almacenará en caché los planes de ejecución si utilizas una recompilación a nivel de sentencia con sentencias ad-hoc o ejecutas procedimientos almacenados con una cláusula RECOMPILE. Necesitas capturar esas consultas utilizando el Almacén de Consultas o los Eventos Extendidos, de los que hablaré más adelante en este capítulo.

Si utilizas una recompilación a nivel de sentencia en procedimientos almacenados u otros módulos T-SQL, SQL Server almacenará en caché el plan de ejecución de la sentencia. El plan, sin embargo, no se va a reutilizar, y las estadísticas de ejecución sólo tendrán los datos de la única (última) ejecución.

El segundo problema está relacionado con el tiempo que los planes permanecen en caché. Esto varía según el plan, lo que puede sesgar los resultados cuando ordenas los datos por métricas totales. Por ejemplo, una consulta con un tiempo medio de CPU más bajo puede mostrar un número total de ejecuciones y un tiempo de CPU más altos que una consulta con un tiempo medio de CPU más alto, dependiendo del tiempo en que ambos planes estuvieron en caché.

Puedes utilizar cualquiera de estas métricas, pero ninguno de los dos enfoques es perfecto. Cuando ordenas los datos por valores medios, puede que veas consultas ejecutadas con poca frecuencia al principio de la lista. Piensa, por ejemplo, en los trabajos nocturnos que consumen muchos recursos. Por otro lado, ordenar por valores totales puede omitir las consultas con los planes que se han almacenado en caché recientemente.

Puedes consultar las columnas creation_time y last_execution_time, que muestran la última vez que se almacenaron en caché y se ejecutaron planes, respectivamente. Yo suelo mirar los datos ordenados según las métricas total y media, teniendo en cuenta la frecuencia de las ejecuciones (el número total y medio de ejecuciones por segundo). Cotejo los datos de ambas salidas antes de decidir qué optimizar.

El último problema es más complicado: es posible obtener múltiples resultados para consultas iguales o similares. Esto puede ocurrir con cargas de trabajo ad hoc, con clientes que tienen diferentes configuraciones de SET en sus sesiones, cuando los usuarios ejecutan las mismas consultas con un formato ligeramente diferente, y en muchos otros casos. También puede ocurrir en bases de datos con nivel de compatibilidad 160 (SQL Server 2022), debido a la función de optimización del plan sensible a parámetros (más sobre esto en el Capítulo 6).

Afortunadamente, puedes abordar ese problema utilizando dos columnas, query_hash y query_plan_hash, ambas expuestas en la vista sys.dm_exec_query_stats. Los mismos valores en esas columnas indicarían consultas y planes de ejecución similares. Puedes utilizar esas columnas para agregar datos.

Advertencia

La sentencia DBCC FREEPROCCACHE borra la caché del plan para reducir el tamaño de la salida en la demostración. ¡No ejecutes el código del Listado 4-2 en servidores de producción!

Te lo demostraré con un ejemplo sencillo. El listado 4-2 ejecuta tres consultas y luego examina el contenido de la caché de planes. Las dos primeras consultas son iguales, sólo que tienen un formato diferente. La tercera es diferente.

Listado 4-2. Las consultas query_hash y query_plan_hash en acción

DBCC FREEPROCCACHE -- Do not run in production!
GO
SELECT /*V1*/ TOP 1 object_id FROM sys.objects WHERE object_id = 1;
GO
SELECT /*V2*/ TOP 1 object_id 
FROM sys.objects
WHERE object_id = 1;
GO
SELECT COUNT(*) FROM sys.objects  
GO

SELECT 
    qs.query_hash, qs.query_plan_hash, qs.sql_handle, qs.plan_handle, 
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
    ((
        CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2)+1
    ) as SQL
FROM 
    sys.dm_exec_query_stats qs 
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY query_hash
OPTION (MAXDOP 1, RECOMPILE);

Puedes ver los resultados en la Figura 4-2. Hay tres planes de ejecución en la salida. Las dos últimas filas tienen los mismos valores query_hash y query_plan_hash y valores diferentes sql_handle y plan_handle.

Figura 4-2. Múltiples planes con los mismos valores query_hash y query_plan_hash

El Listado 4-3 proporciona una versión más sofisticada del script del Listado 4-1, agregando estadísticas de consultas similares. La sentencia y los planes de ejecución se recogen aleatoriamente de la primera consulta de cada grupo, así que tenlo en cuenta en tu análisis.

Listado 4-3. Utilización de la vista sys.dm_exec_query_stats con la agregación query_hash

;WITH Data
AS
(
    SELECT TOP 50
        qs.query_hash
        ,COUNT(*) as [Plan Count]
        ,MIN(qs.creation_time) AS [Cached Time]
        ,MAX(qs.last_execution_time) AS [Last Exec Time]
        ,SUM(qs.execution_count) AS [Exec Cnt]
        ,SUM(qs.total_logical_reads) AS [Total Reads]
        ,SUM(qs.total_logical_writes) AS [Total Writes]
        ,SUM(qs.total_worker_time / 1000) AS [Total Worker Time]
        ,SUM(qs.total_elapsed_time / 1000) AS [Total Elapsed Time]
        ,SUM(qs.total_rows) AS [Total Rows] 
        ,SUM(qs.total_physical_reads) AS [Total Physical Reads]
        ,SUM(qs.total_grant_kb) AS [Total Grant KB]
        ,SUM(qs.total_used_grant_kb) AS [Total Used Grant KB]
        ,SUM(qs.total_ideal_grant_kb) AS [Total Ideal Grant KB]
        ,SUM(qs.total_columnstore_segment_reads)
            AS [Total CSI Segments Read]
        ,MAX(qs.max_dop) AS [Max DOP]
        ,SUM(qs.total_spills) AS [Total Spills]
    FROM 
        sys.dm_exec_query_stats qs WITH (NOLOCK)
    GROUP BY
        qs.query_hash
    ORDER BY
        SUM((qs.total_logical_reads + qs.total_logical_writes) /
            qs.execution_count) DESC
)
SELECT 
    d.[Cached Time]
    ,d.[Last Exec Time]
    ,d.[Plan Count]
    ,sql_plan.SQL
    ,sql_plan.[Query Plan]
    ,d.[Exec Cnt]
    ,CONVERT(DECIMAL(10,5),
        IIF(datediff(second,d.[Cached Time], d.[Last Exec Time]) = 0,
            NULL,
            1.0 * d.[Exec Cnt] / 
                datediff(second,d.[Cached Time], d.[Last Exec Time])
        )
    ) AS [Exec Per Second]
    ,(d.[Total Reads] + d.[Total Writes]) / d.[Exec Cnt] AS [Avg IO]
    ,(d.[Total Worker Time] / d.[Exec Cnt] / 1000) AS [Avg CPU(ms)]
    ,d.[Total Reads]
    ,d.[Total Writes]
    ,d.[Total Worker Time]
    ,d.[Total Elapsed Time]
    ,d.[Total Rows] 
    ,d.[Total Rows] / d.[Exec Cnt] AS [Avg Rows]
    ,d.[Total Physical Reads]
    ,d.[Total Physical Reads] / d.[Exec Cnt] AS [Avg Physical Reads]
    ,d.[Total Grant KB]
    ,d.[Total Grant KB] / d.[Exec Cnt] AS [Avg Grant KB] 
    ,d.[Total Used Grant KB]
    ,d.[Total Used Grant KB] / d.[Exec Cnt] AS [Avg Used Grant KB] 
    ,d.[Total Ideal Grant KB]
    ,d.[Total Ideal Grant KB] / d.[Exec Cnt] AS [Avg Ideal Grant KB] 
    ,d.[Total CSI Segments Read]
    ,d.[Total CSI Segments Read] / d.[Exec Cnt] AS [AVG CSI Segments Read]
    ,d.[Max DOP]
    ,d.[Total Spills]
    ,d.[Total Spills] / d.[Exec Cnt] AS [Avg Spills]
FROM 
    Data d
        CROSS APPLY
        (
            SELECT TOP 1
                SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
                ((
                    CASE qs.statement_end_offset
                        WHEN -1 THEN DATALENGTH(qt.text)
                        ELSE qs.statement_end_offset
                    END - qs.statement_start_offset)/2)+1
                ) AS SQL
                ,TRY_CONVERT(XML,qp.query_plan) AS [Query Plan]
            FROM
                sys.dm_exec_query_stats qs 
                    OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
                    OUTER APPLY sys.dm_exec_text_query_plan
                    (
                        qs.plan_handle
                        ,qs.statement_start_offset
                        ,qs.statement_end_offset
                    ) qp
            WHERE
                qs.query_hash = d.query_hash AND ISNULL(qt.text,'') <> ''
        ) sql_plan
ORDER BY
     [Avg IO] DESC
OPTION (RECOMPILE, MAXDOP 1);

A partir de con SQL Server 2008, puedes obtener estadísticas de ejecución de los procedimientos almacenados a través de la vista sys.dm_exec_procedure_stats. Para ello, puedes utilizar el código del Listado 4-4. Al igual que con la vista sys.dm_exec_query_stats, puedes ordenar los datos por varias métricas de ejecución, dependiendo de tu estrategia de optimización. Cabe señalar que las estadísticas de ejecución incluyen las métricas del SQL dinámico y de otros módulos anidados (procedimientos almacenados, funciones, desencadenadores) llamados desde los procedimientos almacenados.

Listado 4-4. Utilizando la vista sys.dm_exec_procedure_stats

SELECT TOP 50
    IIF (ps.database_id = 32767,
         'mssqlsystemresource',
         DB_NAME(ps.database_id)
    ) AS [DB]
    ,OBJECT_NAME(
         ps.object_id, 
         IIF(ps.database_id = 32767, 1, ps.database_id)
    ) AS [Proc Name]
    ,ps.type_desc AS [Type]
    ,ps.cached_time AS [Cached Time]
    ,ps.last_execution_time AS [Last Exec Time]
    ,qp.query_plan AS [Plan]
    ,ps.execution_count AS [Exec Count]
    ,CONVERT(DECIMAL(10,5),
        IIF(datediff(second,ps.cached_time, ps.last_execution_time) = 0,
            NULL,
            1.0 * ps.execution_count / 
                datediff(second,ps.cached_time, ps.last_execution_time)
        )
    ) AS [Exec Per Second]
    ,(ps.total_logical_reads + ps.total_logical_writes) / 
        ps.execution_count AS [Avg IO]
    ,(ps.total_worker_time / ps.execution_count / 1000) 
        AS [Avg CPU(ms)]
    ,ps.total_logical_reads AS [Total Reads]
    ,ps.last_logical_reads AS [Last Reads]
    ,ps.total_logical_writes AS [Total Writes]
    ,ps.last_logical_writes AS [Last Writes]
    ,ps.total_worker_time / 1000 AS [Total Worker Time]
    ,ps.last_worker_time / 1000 AS [Last Worker Time]
    ,ps.total_elapsed_time / 1000 AS [Total Elapsed Time]
    ,ps.last_elapsed_time / 1000 AS [Last Elapsed Time]
    ,ps.total_physical_reads AS [Total Physical Reads]
    ,ps.last_physical_reads AS [Last Physical Reads]
    ,ps.total_physical_reads / ps.execution_count AS [Avg Physical Reads]
    ,ps.total_spills AS [Total Spills]
    ,ps.last_spills AS [Last Spills]
    ,(ps.total_spills / ps.execution_count) AS [Avg Spills]
FROM 
    sys.dm_exec_procedure_stats ps WITH (NOLOCK) 
        CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp
ORDER BY
     [Avg IO] DESC
OPTION (RECOMPILE, MAXDOP 1);

La Figura 4-3 muestra una salida parcial del código. Como puedes ver en la salida, puedes obtener los planes de ejecución de los procedimientos almacenados. Internamente, los planes de ejecución de los procedimientos almacenados y otros módulos T-SQL son sólo colecciones del plan individual de cada sentencia. En algunos casos -por ejemplo, cuando el tamaño del plan de ejecución supera los 2 MB- el script no incluirá ningún plan en la salida.

Figura 4-3. Salida parcial de la vista sys.dm_exec_procedure_stats

El Listado 4-5 ayuda a solucionar este problema. Puedes utilizar para obtener planes de ejecución en caché y sus métricas para sentencias individuales de módulos T-SQL. Tienes que especificar el nombre del módulo en la cláusula WHERE de la sentencia cuando ejecutes el script.

Listado 4-5. Obtener el plan de ejecución y las estadísticas de las sentencias de los procedimientos almacenados

SELECT 
    qs.creation_time AS [Cached Time]
    ,qs.last_execution_time AS [Last Exec Time]
    ,SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
    ((
        CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2)+1) AS SQL
    ,TRY_CONVERT(XML,qp.query_plan) AS [Query Plan]
    ,CONVERT(DECIMAL(10,5),
        IIF(datediff(second,qs.creation_time, qs.last_execution_time) = 0,
            NULL,
            1.0 * qs.execution_count / 
                datediff(second,qs.creation_time, qs.last_execution_time)
        )
    ) AS [Exec Per Second]
    ,(qs.total_logical_reads + qs.total_logical_writes) / 
        qs.execution_count AS [Avg IO]
    ,(qs.total_worker_time / qs.execution_count / 1000) 
        AS [Avg CPU(ms)]
    ,qs.total_logical_reads AS [Total Reads]
    ,qs.last_logical_reads AS [Last Reads]
    ,qs.total_logical_writes AS [Total Writes]
    ,qs.last_logical_writes AS [Last Writes]
    ,qs.total_worker_time / 1000 AS [Total Worker Time]
    ,qs.last_worker_time / 1000 AS [Last Worker Time]
    ,qs.total_elapsed_time / 1000 AS [Total Elapsed Time]
    ,qs.last_elapsed_time / 1000 AS [Last Elapsed Time]
    ,qs.total_rows AS [Total Rows] 
    ,qs.last_rows AS [Last Rows] 
    ,qs.total_rows / qs.execution_count AS [Avg Rows]
    ,qs.total_physical_reads AS [Total Physical Reads]
    ,qs.last_physical_reads AS [Last Physical Reads]
    ,qs.total_physical_reads / qs.execution_count 
        AS [Avg Physical Reads]
    ,qs.total_grant_kb AS [Total Grant KB]
    ,qs.last_grant_kb AS [Last Grant KB]
    ,(qs.total_grant_kb / qs.execution_count) 
        AS [Avg Grant KB] 
    ,qs.total_used_grant_kb AS [Total Used Grant KB]
    ,qs.last_used_grant_kb AS [Last Used Grant KB]
    ,(qs.total_used_grant_kb / qs.execution_count) 
        AS [Avg Used Grant KB] 
    ,qs.total_ideal_grant_kb AS [Total Ideal Grant KB]
    ,qs.last_ideal_grant_kb AS [Last Ideal Grant KB]
    ,(qs.total_ideal_grant_kb / qs.execution_count) 
        AS [Avg Ideal Grant KB] 
    ,qs.total_columnstore_segment_reads
        AS [Total CSI Segments Read]
    ,qs.last_columnstore_segment_reads 
        AS [Last CSI Segments Read]
    ,(qs.total_columnstore_segment_reads / qs.execution_count)
        AS [AVG CSI Segments Read]
    ,qs.max_dop AS [Max DOP]
    ,qs.total_spills AS [Total Spills]
    ,qs.last_spills AS [Last Spills]
    ,(qs.total_spills / qs.execution_count) AS [Avg Spills]
FROM 
    sys.dm_exec_query_stats qs WITH (NOLOCK)
        OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
        OUTER APPLY sys.dm_exec_text_query_plan
        (
            qs.plan_handle
            ,qs.statement_start_offset
            ,qs.statement_end_offset
        ) qp
WHERE 
    OBJECT_NAME(qt.objectid, qt.dbid) = <SP Name> -- Add SP Name here      
ORDER BY 
    qs.statement_start_offset, qs.statement_end_offset
OPTION (RECOMPILE, MAXDOP 1);

A partir de con SQL Server 2016, puedes obtener estadísticas de ejecución para desencadenadores y funciones escalares definidas por el usuario, utilizando sys.dm_exec_trigger_stats y sys.dm_exec​_func⁠tion_stats respectivamente. Puedes utilizar el mismo código que en el Listado 4-4; sólo tienes que sustituir allí el nombre DMV. También puedes descargar el código del material complementario de este libro.

Por último, cabe mencionar que SQL Server puede almacenar en caché miles de planes de ejecución. Además, las funciones para obtener planes de consulta y sentencias SQL consumen muchos recursos; por ello, utilizo la sugerencia de consulta MAXDOP 1 para reducir la sobrecarga. En algunos casos, puede ser beneficioso guardar el contenido de la caché de planes en una base de datos independiente utilizando las sentencias SELECT INTO y analizar los datos en servidores que no estén en producción.

La resolución de problemas basada en las estadísticas de ejecución basadas en la caché de planes tiene varias limitaciones, y puedes pasar por alto algunas consultas. No obstante, es un buen punto de partida. Lo más importante es que los datos se recogen automáticamente, y puedes acceder a ellos inmediatamente, sin necesidad de configurar herramientas de monitoreo adicionales .

Eventos extendidos y trazas SQL

Estoy seguro de que todos los ingenieros de SQL Server conocen las Trazas SQL y los Eventos Extendidos (xEvents). En puedes capturar diversos eventos de un sistema para analizarlos y solucionar problemas en tiempo real. También puedes utilizarlos para capturar consultas costosas y de larga ejecución, incluidas las que no almacenan en caché los planes de ejecución y, por tanto, no aparecen en la vista sys.dm_exec_query_stats.

Sin embargo, me gustaría empezar esta sección con una advertencia: no utilices SQL Traces y xEvents para este fin a menos que sea absolutamente necesario. Capturar las sentencias ejecutadas es una operación costosa que puede introducir una importante sobrecarga de rendimiento en sistemas con mucha actividad. (Ya viste un ejemplo de ello en el Capítulo 1.)

No importa la cantidad de datos que recopiles. Puedes excluir la mayoría de las sentencias de la salida filtrando las consultas con bajo consumo de recursos. Pero SQL Server seguirá teniendo que capturar todas las sentencias para evaluarlas, filtrarlas y descartar los eventos innecesarios.

No recojas información innecesaria en los eventos que estés recopilando o en las acciones xEvent que estés capturando. Algunas acciones -por ejemplo, callstack- son costosas y suponen un grave problema de rendimiento cuando están activadas. Cuando sea posible, utiliza xEvents en lugar de SQL Traces. Son más ligeros e introducen menos sobrecarga en el sistema.

La Tabla 4-1 muestra varios eventos de Rastreo Extendido y SQL que pueden utilizarse para detectar consultas ineficaces. Cada de ellos, excepto sqlserver.attention, tiene un evento correspondiente que se dispara al principio de la ejecución. A veces necesitas capturarlos para correlacionar cargas de trabajo de varias sesiones.

Tabla 4-1. Eventos Extended y SQL Trace para detectar consultas ineficaces

xEvento	Evento SQL Trace	Comentarios
sqlserver.sql_statement​_com⁠pleted	SQL:StmtCompleted	Se activa cuando la sentencia finaliza su ejecución.
sqlserver.sp_state⁠ment​_completed	SP:StmtCompleted	Se dispara cuando la sentencia SQL dentro del módulo T-SQL completa la ejecución.
sqlserver.rpc_completed	RPC:Completed	Se dispara cuando finaliza una llamada a procedimiento remoto (RPC). Las RPC son peticiones SQL parametrizadas, como llamadas a procedimientos almacenados o lotes parametrizados, enviadas desde las aplicaciones. Muchas bibliotecas cliente ejecutarán consultas a través de llamadas a sp_executesql, que pueden ser capturadas por ese evento.
sqlserver.module_end	SP:Completed	Se activa cuando el módulo T-SQL finaliza la ejecución.
sqlserver.sql_batch​_com⁠pleted	SQL:BatchCompleted	Se dispara cuando el lote SQL completa la ejecución.
sqlserver.attention	Error:Attention	Ocurren cuando el cliente cancela la ejecución de una consulta, ya sea debido a un tiempo de espera o explícitamente (por ejemplo, utilizando el botón rojo de cancelar en SSMS).

La elección de los eventos que debes capturar depende de la carga de trabajo del sistema, del diseño del nivel de acceso a los datos y de tu estrategia de resolución de problemas. Por ejemplo, los eventos sqlserver.sql_statement_completed y sqlserver.sp_statement​_com⁠pleted te permiten detectar consultas ad-hoc y de módulos T-SQL ineficientes. Alternativamente, puedes capturar lotes y procedimientos almacenados ineficientes utilizando los eventos sqlserver.rpc_completed y sqlserver.sql_batch_completed, reduciendo la sobrecarga.

Lo mismo se aplica a la elección de las acciones xEvent a capturar. Por ejemplo, podrías ignorar la información sobre el usuario y la aplicación cliente si no la necesitas durante la resolución de problemas. Alternativamente, podrías decidir recopilar las acciones query_hash y query_plan_hash y utilizarlas para identificar el impacto acumulativo de consultas y planes de ejecución similares.

Hay dos escenarios en los que suelo capturar consultas ineficaces. Puedo ejecutar una sesión durante unos minutos, capturando los resultados en el objetivo ring_buffer. Normalmente, hago esto cuando la carga de trabajo del sistema es relativamente estática y puede representarse con una pequeña muestra. Alternativamente, puedo ejecutar una sesión xEvent durante unas horas, utilizando event_file como objetivo.

El Listado 4-6 muestra este último enfoque de guardar los datos en la carpeta C:\ExtEvents (cámbiala en tu sistema). En se capturan las sentencias que consumen más de 5.000 ms de tiempo de CPU o producen más de 50.000 lecturas o escrituras lógicas. El código del Listado 4-6 y del Listado 4-7 funcionará en SQL Server 2012 y posteriores; puede requerir modificaciones en SQL Server 2008, que funciona de forma diferente con el objetivo de archivo y carece de las acciones query_hash y query_plan_hash.

Como advertencia, esta sesión introduce sobrecarga. La cantidad de sobrecarga dependerá de la carga de trabajo y de la cantidad de datos que estés capturando. No mantengas activa esta sesión a menos que estés solucionando problemas de rendimiento. Además, ajusta los valores umbral de cpu_time, logical_reads y writes a tu carga de trabajo, y evita capturar un número excesivo de consultas.

Del mismo modo, define una lista de acciones xEvents basada en tu estrategia de resolución de problemas. Por ejemplo, no es necesario recoger plan_handle si vas a realizar el análisis en otro servidor y no podrías obtener planes de ejecución de la caché de planes.

Listado 4-6. Captura de consultas intensivas en CPU y E/S

CREATE EVENT SESSION [Expensive Queries] 
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION
    (
        sqlserver.client_app_name
        ,sqlserver.client_hostname
        ,sqlserver.database_id
        ,sqlserver.plan_handle
        ,sqlserver.query_hash
        ,sqlserver.query_plan_hash
        ,sqlserver.sql_text
        ,sqlserver.username
    )    
    WHERE
    (
        (
            cpu_time >= 5000000 or -- Time in microseconds
            logical_reads >= 50000 or
            writes >= 50000
        ) AND 
        sqlserver.is_system = 0 
    ) 
)
,ADD EVENT sqlserver.sp_statement_completed
(
    ACTION
    (
        sqlserver.client_app_name
        ,sqlserver.client_hostname
        ,sqlserver.database_id
        ,sqlserver.plan_handle
        ,sqlserver.query_hash
        ,sqlserver.query_plan_hash
        ,sqlserver.sql_text
        ,sqlserver.username
    )    
    WHERE
    (
        (
            cpu_time >= 5000000 or -- Time in microseconds
            logical_reads >= 50000 or
            writes >= 50000
        ) AND 
        sqlserver.is_system = 0 
    ) 
)
ADD TARGET package0.event_file
(
    SET FILENAME = 'C:\ExtEvents\Expensive Queries.xel'
)
WITH
(
    event_retention_mode=allow_single_event_loss
    ,max_dispatch_latency=30 seconds
);

El listado 4-7 proporciona el código para analizar los datos recogidos. Como primer paso, carga los eventos recopilados en una tabla temporal utilizando la función sys.fn_xe_file_target_read_file. El asterisco al final del nombre del archivo indica a SQL Server que cargue todos los archivos de rollover de la sesión xEvent.

En segundo lugar, el código analiza los eventos recopilados, guardando los resultados en otra tabla temporal. Puede que necesites ajustar el código en la CTE EventInfo en función de los campos xEvent y las acciones que necesites para solucionar problemas. No analices información innecesaria: analizar XML es una operación cara y que lleva mucho tiempo.

Por último, si ejecutas este código en SQL Server 2016 o versiones anteriores, tienes que modificarlo y obtener la hora del evento del XML event_data. En SQL Server 2017, la función sys.fn_xe_file_target_read_file devuelve esto como parte de la salida.

Listado 4-7. Análisis de los datos recogidos de xEventos

CREATE TABLE #EventData
(
  event_data XML NOT NULL,
  file_name NVARCHAR(260) NOT NULL,
  file_offset BIGINT NOT NULL,
  timestamp_utc datetime2(7) NOT NULL -- SQL Server 2017+
);

INSERT INTO #EventData(event_data, file_name, file_offset, timestamp_utc)
  SELECT CONVERT(XML,event_data), file_name, file_offset, timestamp_utc 
  FROM sys.fn_xe_file_target_read_file
    ('c:\extevents\Expensive Queries*.xel',NULL,NULL,NULL);

;WITH EventInfo([Event],[Event Time],[DB],[Statement],[SQL],[User Name]
  ,[Client],[App],[CPU Time],[Duration],[Logical Reads]
  ,[Physical Reads],[Writes],[Rows],[Query Hash],[Plan Hash]
  ,[PlanHandle],[Stmt Offset],[Stmt Offset End],File_Name,File_Offset)
AS
(
  SELECT
    event_data.value('/event[1]/@name','SYSNAME') AS [Event]
    ,timestamp_utc AS [Event Time] -- SQL Server 2017+
    /*,event_data.value('/event[1]/@timestamp','DATETIME') 
        AS [Event Time] -- Prior SQL Server 2017 */
    ,event_data.value
       ('((/event[1]/action[@name="database_id"]/value/text())[1])'
         ,'INT') AS [DB]
    ,event_data.value
       ('((/event[1]/data[@name="statement"]/value/text())[1])'
         ,'NVARCHAR(MAX)') AS [Statement]
    ,event_data.value
       ('((/event[1]/action[@name="sql_text"]/value/text())[1])'
         ,'NVARCHAR(MAX)') AS [SQL]
    ,event_data.value
       ('((/event[1]/action[@name="username"]/value/text())[1])'
         ,'NVARCHAR(255)') AS [User Name]
    ,event_data.value
       ('((/event[1]/action[@name="client_hostname"]/value/text())[1])'
         ,'NVARCHAR(255)') AS [Client]
    ,event_data.value
       ('((/event[1]/action[@name="client_app_name"]/value/text())[1])'
         ,'NVARCHAR(255)') AS [App]
    ,event_data.value
       ('((/event[1]/data[@name="cpu_time"]/value/text())[1])'
         ,'BIGINT') AS [CPU Time]
    ,event_data.value
       ('((/event[1]/data[@name="duration"]/value/text())[1])'
         ,'BIGINT') AS [Duration]
    ,event_data.value
       ('((/event[1]/data[@name="logical_reads"]/value/text())[1])'
         ,'INT') AS [Logical Reads]
    ,event_data.value
       ('((/event[1]/data[@name="physical_reads"]/value/text())[1])'
         ,'INT') AS [Physical Reads]
    ,event_data.value
       ('((/event[1]/data[@name="writes"]/value/text())[1])'
         ,'INT') AS [Writes]
    ,event_data.value
       ('((/event[1]/data[@name="row_count"]/value/text())[1])'
         ,'INT') AS [Rows]
    ,event_data.value(
       'xs:hexBinary(((/event[1]/action[@name="query_hash"]/value/text())[1]))'
         ,'BINARY(8)') AS [Query Hash]
    ,event_data.value(
      'xs:hexBinary(((/event[1]/action[@name="query_plan_hash"]/value/text())[1]))'
         ,'BINARY(8)') AS [Plan Hash]
    ,event_data.value(
      'xs:hexBinary(((/event[1]/action[@name="plan_handle"]/value/text())[1]))'
         ,'VARBINARY(64)') AS [PlanHandle]
    ,event_data.value
      ('((/event[1]/data[@name="offset"]/value/text())[1])'
         ,'INT') AS [Stmt Offset]    
    ,event_data.value
       ('((/event[1]/data[@name="offset_end"]/value/text())[1])'
         ,'INT') AS [Stmt Offset End]
    ,file_name
    ,file_offset
  FROM 
    #EventData 
)
SELECT 
  ei.*
  ,TRY_CONVERT(XML,qp.Query_Plan) AS [Plan]
INTO #Queries
FROM 
  EventInfo ei 
    OUTER APPLY 
      sys.dm_exec_text_query_plan
      (
        ei.PlanHandle
        ,ei.[Stmt Offset]
        ,ei.[Stmt Offset End]
      ) qp
OPTION (MAXDOP 1, RECOMPILE);

Ahora puedes trabajar con los datos brutos de la tabla #Queries, detectando las consultas más ineficaces para su optimización. En muchos casos, también es beneficioso agregar los datos basándose en una sentencia, un hash de consulta o un hash de plan, analizando el impacto acumulativo de las consultas.

El material complementario de este libro,, incluye un script que puedes utilizar para capturar la carga de trabajo en el objetivo ring_buffer. Sin embargo, hay una limitación importante: la vista sys.dm_xe_session_targets, que proporciona los datos recogidos del objetivo, sólo puede dar salida a 4 MB de XML. Esto puede llevar a una situación en la que no veas algunos eventos recopilados.

De nuevo: ten cuidado con la sobrecarga que introducen xEvents y SQL Traces en los sistemas. No crees y ejecutes esas sesiones permanentemente. En muchos casos, puedes obtener suficientes datos para solucionar problemas habilitando la sesión o la traza sólo durante unos minutos .

Almacén de consultas

Así que hasta ahora, hemos discutido dos enfoques para detectar consultas ineficaces en este capítulo. Ambos tienen limitaciones. Los datos basados en la caché de planes pueden pasar por alto algunas consultas; las Trazas SQL y los xEventos requieren que realices un análisis complejo de la salida y pueden tener una sobrecarga de rendimiento significativa en sistemas con mucha actividad.

El almacén de consultas, introducido en SQL Server 2016, ayuda a resolver esas limitaciones. Puedes pensar en él como en los registradores de datos de vuelo (o "cajas negras") de las cabinas de los aviones, pero para SQL Server. Cuando el Almacén de Consultas está activado, SQL Server captura y persiste las estadísticas en tiempo de ejecución y los planes de ejecución de las consultas en la base de datos. Muestra cómo se comportan los planes de ejecución y cómo evolucionan con el tiempo. Por último, te permite forzar planes de ejecución específicos a las consultas para resolver problemas de rastreo de parámetros, de los que hablaremos en el Capítulo 6.

Nota

El almacén de consultas está desactivado por defecto en la versión local de SQL Server hasta SQL Server 2019. Está habilitado por defecto en las bases de datos SQL de Azure, en las instancias gestionadas de Azure SQL y en las nuevas bases de datos creadas en SQL Server 2022.

El Almacén de Consultas está totalmente integrado en el canal de procesamiento de consultas, como ilustra el diagrama de alto nivel de la Figura 4-4.

Cuando necesita ejecutar una consulta, SQL Server busca el plan de ejecución en la caché de planes. Si encuentra un plan, SQL Server comprueba si es necesario recompilar la consulta (debido a actualizaciones de estadísticas u otros factores), si se ha creado un nuevo plan forzado y si se ha eliminado un plan forzado antiguo del Almacén de Consultas.

Figura 4-4. Proceso de consultas

Durante la compilación, SQL Server comprueba si la consulta tiene un plan forzado disponible. Cuando esto ocurre, la consulta se compila esencialmente con el plan forzado, de forma muy parecida a cuando se utiliza la sugerencia USE PLAN. Si el plan resultante es válido, se almacena en la caché de planes para su reutilización.

Si el plan forzado deja de ser válido (por ejemplo, cuando un usuario elimina un índice al que se hace referencia en el plan forzado), SQL Server no falla la consulta. En lugar de eso, vuelve a compilar la consulta sin el plan forzado y sin almacenarlo en caché después. El Almacén de Consultas, en cambio, persiste ambos planes, marcando el plan forzado como inválido. Todo esto ocurre de forma transparente para las aplicaciones.

En SQL Server 2022 y SQL Azure Database, el Almacén de Consultas te permite añadir sugerencias a nivel de consulta mediante el procedimiento almacenado sp_query_store_set_hints. Con las sugerencias del Almacén de Consultas, SQL Server compilará y ejecutará las consultas como si estuvieras proporcionando las sugerencias mediante la cláusula de consulta OPTION. Esto te proporciona flexibilidad adicional durante el ajuste de consultas sin necesidad de cambiar las aplicaciones.

A pesar de su estrecha integración con el canal de procesamiento de consultas y de varias optimizaciones internas, el Almacén de Consultas sigue añadiendo sobrecarga al sistema. La cantidad de sobrecarga depende de los dos factores principales siguientes:

Número de compilaciones en el sistema

Cuantas más compilaciones realice SQL Server, más carga deberá soportar el Almacén de Consultas. En particular, es posible que el Almacén de Consultas no funcione muy bien en sistemas que tengan una carga de trabajo muy pesada, ad hoc y no parametrizada.

Ajustes de la recogida de datos

Las configuraciones del Almacén de Consultas te permiten especificar si quieres capturar todas las consultas o sólo las caras, junto con los intervalos de agregación y los ajustes de retención de datos. Si recopilas más datos y/o utilizas intervalos de agregación más pequeños, tendrás más sobrecarga.

Presta atención específica a la configuración QUERY_CAPTURE_MODE, que controla qué consultas se capturan. Con QUERY_CAPTURE_MODE=ALL (por defecto en SQL Server 2016 y 2017), el Almacén de Consultas captura todas las consultas del sistema. Esto puede tener un impacto, especialmente en una carga de trabajo ad-hoc.

Con QUERY_CAPTURE_MODE=AUTO (por defecto en SQL Server 2019 y posteriores), el Almacén de Consultas no captura las consultas pequeñas o ejecutadas con poca frecuencia. Esta es la mejor opción en la mayoría de los casos.

Por último, a partir de SQL Server 2019, puedes establecer QUERY_CAPTURE_MODE=CUSTOM y personalizar aún más los criterios cuando se capturan las consultas.

Cuando se configura correctamente, la sobrecarga introducida por el Almacén de Consultas suele ser relativamente pequeña. Sin embargo, puede ser significativa en algunos casos. Por ejemplo, he estado utilizando el Almacén de Consultas para solucionar los problemas de rendimiento de un proceso que consiste en un gran número de pequeñas consultas ad hoc. Capturé todas las consultas del sistema utilizando el modo QUERY_CAPTURE_MODE=ALL, recopilando casi 10 GB de datos en el Almacén de Consultas. El proceso tardó 8 horas en completarse con el Almacén de Consultas activado, frente a 2,5 horas sin él.

No obstante, te sugiero que habilites el Almacén de Consultas si tu sistema puede soportar la sobrecarga. Por ejemplo, algunas funciones de la familia de Procesamiento Inteligente de Consultas (IQP) dependen del Almacén de Consultas y se beneficiarán de él. También simplifica el ajuste de las consultas y puede ahorrarte muchas horas de trabajo cuando está activado.

Nota

Monitorea las esperas QDS* cuando actives el Almacén de Consultas . Las esperas excesivas de QDS* pueden ser un signo de una mayor sobrecarga del Almacén de Consultas en el sistema. Ignora las esperas de QDS_PERSIST_TASK_MAIN_LOOP_SLEEP y QDS_ASYNC_QUEUE; son benignas.

Hay dos banderas de seguimiento importantes que deberías considerar activar si utilizas el Almacén de Consultas:

T7745

Para reducir la sobrecarga de, SQL Server almacena en caché algunos datos del Almacén de Consultas en la memoria periódicamente, y los descarga en la base de datos. El intervalo de vaciado se controla mediante la configuración DATA_FLUSH_INTERVAL_SECONDS, que dicta cuántos datos del Almacén de consultas puedes perder en caso de caída del Servidor SQL. Sin embargo, en circunstancias normales, SQL Server guardaría los datos del Almacén de consultas en memoria durante el apagado o la conmutación por error de SQL Server.

Este comportamiento puede prolongar los tiempos de apagado y conmutación por error en sistemas ocupados. Puedes desactivarlo con la bandera de rastreo T7745, ya que la pérdida de una pequeña cantidad de telemetría suele ser aceptable.

T7752 (SQL Server 2016 y 2017)

SQL Server carga algunos datos del Almacén de Consultas en la memoria al iniciarse la base de datos, manteniendo la base de datos no disponible durante ese tiempo. Con grandes Almacenes de Consultas, esto puede prolongar el tiempo de reinicio o conmutación por error de SQL Server y afectar a la experiencia del usuario.

La bandera de rastreo T7752 obliga a SQL Server a cargar los datos del Almacén de Consultas de forma asíncrona, permitiendo que las consultas se ejecuten en paralelo. La telemetría no se recogerá durante la carga; sin embargo, suele ser un precio aceptable a pagar por un arranque más rápido.

En puedes analizar el impacto de una carga sincrónica del Almacén de Consultas observando el tiempo de espera para el tipo de espera QDS_LOADDB. Esta espera sólo se produce al iniciar la base de datos, por lo que debes consultar la vista sys.dm_os_wait_stats y filtrar la salida por tipo de espera para obtener el número.

Como regla general, no crees un Almacén de Consultas muy grande. Además, considera la posibilidad de monitorear el tamaño del Almacén de Consultas, especialmente en sistemas muy ocupados. En algunos casos, SQL Server puede no ser capaz de limpiar los datos con la suficiente rapidez, sobre todo si utilizas el modo de recogida QUERY_CAPTURE_MODE=ALL.

Por último, aplica las últimas actualizaciones de SQL Server, especialmente si utilizas SQL Server 2016 y 2017. Se han publicado varias mejoras de escalabilidad y correcciones de errores del Almacén de Consultas tras el lanzamiento inicial de la función.

Puedes trabajar con el Almacén de Consultas de dos formas: a través de la interfaz gráfica de SSMS o consultando directamente las vistas dinámicas de gestión. Veamos primero la interfaz de usuario.

Almacenar consultas Informes SSMS

Después de que habilite el Almacén de Consultas en la base de datos, verás una carpeta Almacén de Consultas en el Explorador de Objetos(Figura 4-5). El número de informes de la carpeta dependerá de las versiones de SQL Server y SSMS de tu sistema. El resto de esta sección te guiará a través de los siete informes mostrados en la Figura 4-5.

Figura 4-5. Informes de Query Store en SSMS

Consultas regresivas

Este informe de, que se muestra en la Figura 4-6, muestra las consultas cuyo rendimiento ha sufrido una regresión a lo largo del tiempo. Puedes configurar el marco temporal y los criterios de regresión (como operaciones de disco, consumo de CPU y número de ejecuciones) para el análisis.

Selecciona la consulta en el gráfico de la parte superior izquierda del informe. La parte superior derecha del informe muestra los planes de ejecución recopilados para la consulta seleccionada. Puedes hacer clic en los puntos, que representan diferentes planes de ejecución, y ver los planes en la parte inferior. También puedes comparar diferentes planes de ejecución.

Figura 4-6. Informe de consultas regresivas

El botón Forzar plan te permite forzar un plan seleccionado para la consulta. Llama internamente al procedimiento almacenado sys.sp_query_store_force_plan. Del mismo modo, el botón Desforzar plan elimina un plan forzado llamando al procedimiento almacenado sys.sp_query_store_unforce_plan.

El informe Consultas regresivas es una gran herramienta para solucionar problemas relacionados con el esnifado de parámetros, que trataremos en el Capítulo 6, y arreglarlos rápidamente forzando planes de ejecución específicos.

Consultas que más recursos consumen

Este informe (Figura 4-7) te permite detectar las consultas que consumen más recursos del sistema. Aunque funciona de forma similar a los datos proporcionados por la vista sys.dm​_exec_query_stats, no depende de la caché del plan. Puedes personalizar las métricas utilizadas para la clasificación de los datos y el intervalo de tiempo.

Figura 4-7. Informe Consultas que más recursos consumen

Consumo global de recursos

Este informe de muestra las estadísticas de la carga de trabajo y el uso de recursos en los intervalos de tiempo especificados. Te permite detectar y analizar picos en el uso de recursos y profundizar en las consultas que introducen dichos picos. La Figura 4-8 muestra la salida del informe.

Figura 4-8. Informe global de consumo de recursos

Consultas con alta variación

Este informe de te permite localizar las consultas con alta variación de rendimiento. Puedes utilizarlo para detectar anomalías en la carga de trabajo, junto con posibles regresiones de rendimiento. Para ahorrar espacio en el libro, no incluyo capturas de pantalla de cada informe.

Consultas con Planes Forzados

Este informe de te muestra las consultas que tienen un plan de ejecución forzado en el sistema.

Estadísticas de espera de consulta

Este informe de te permite detectar consultas con altas esperas. Los datos se agrupan por varias categorías (como CPU, disco y bloqueo), en función del tipo de espera. Puedes ver detalles sobre la asignación de esperas en la documentación de Microsoft.

Consultas rastreadas

Por último, el informe Consultas rastreadas te permite monitorizar los planes de ejecución y las estadísticas de consultas individuales. Proporciona información similar a la de los informes Consultas retrocedidas y Consultas que más recursos consumen, en el ámbito de consultas individuales.

Estos informes te proporcionarán una gran cantidad de datos para el análisis. Sin embargo, en algunos casos, querrás utilizar T-SQL y trabajar directamente con los datos del Almacén de Consultas. Veamos cómo puedes conseguirlo.

Trabajar con DMV del almacén de consultas

Las vistas de gestión dinámica del Almacén de Consultas están muy normalizadas, como se muestra en la Figura 4-9. Las estadísticas de ejecución se rastrean para cada plan de ejecución y se agrupan por intervalos de recogida, que se definen mediante el ajuste INTERVAL_LENGTH_MINUTES. El intervalo por defecto de 60 minutos es aceptable en la mayoría de los casos.

Como puedes suponer, cuanto más pequeños sean los intervalos que utilices, más datos se recogerán y persistirán en el Almacén de Consultas. Lo mismo se aplica a la carga de trabajo del sistema: un número excesivo de consultas ad hoc puede disparar el tamaño del Almacén de Consultas. Tenlo en cuenta cuando configures el Almacén de Consultas en tu sistema.

Figura 4-9. DMVs del almacén de consultas

Puedes separar lógicamente las DMV en dos categorías: almacén de planes y estadísticas en tiempo de ejecución. Los DMV del almacén de planes incluyen las siguientes vistas :

sys.query_store_query

La vista sys.query_store_query proporciona información sobre las consultas y sus estadísticas de compilación, así como la hora de la última ejecución.

sys.query_store_query_text

La vista sys.query_store_query_text muestra información sobre el texto de la consulta.

sys.query_context_setting

La vista sys.query_context_setting contiene información sobre los ajustes de contexto asociados a la consulta. Incluye las opciones de SET, el esquema predeterminado para la sesión, el idioma y otros atributos. SQL Server puede generar y almacenar en caché planes de ejecución distintos para la misma consulta cuando esas configuraciones son diferentes.

sys.query_store_plan

La vista sys.query_store_plan proporciona información sobre los planes de ejecución de las consultas. La columna is_forced_plan indica si el plan es forzado. El last_force_failure_reason te indica por qué no se aplicó un plan forzado a la consulta.

Como puedes ver, cada consulta puede tener varias entradas en las vistas sys.query_store_query y sys.query_store_plan. Esto variará en función de tus opciones de contexto de sesión, recompilaciones y otros factores.

Otras tres vistas representan estadísticas de tiempo de ejecución :

sys.query_store_runtime_stats_interval

La vista sys.query_store_runtime_stats_interval contiene información sobre los intervalos de recogida de estadísticas.

sys.query_store_runtime_stats

La vista sys.query_store_runtime_stats hace referencia a la vista sys.query_store_plan y contiene información sobre las estadísticas de ejecución de un plan específico durante un intervalo concreto de sys.query_store_run⁠time​_stats_interval. Proporciona información sobre el recuento de ejecuciones, el tiempo de CPU y la duración de las llamadas, estadísticas de E/S lógica y física, uso del registro de transacciones, grado de paralelismo, tamaño de la concesión de memoria y algunas otras métricas útiles.

sys.query_store_wait_stats

A partir de SQL Server 2017, puedes obtener información sobre las esperas de las consultas con la vista sys.query_store_wait_stats. Los datos se recogen para cada plan e intervalo de tiempo y se agrupan por varias categorías de espera, incluyendo CPU, memoria y bloqueo.

Veamos algunos escenarios para trabajar con datos del Almacén de Consultas.

El Listado 4-8 proporciona código que devuelve información sobre las 50 consultas del sistema que más E/S consumen. Como el Almacén de Consultas persiste las estadísticas de ejecución en intervalos de tiempo, tendrás que agregar los datos de varias filas de sys.query_store_run⁠time​_stats. La salida incluirá datos de todos los intervalos que hayan finalizado en las últimas 24 horas, agrupados por consultas y sus planes de ejecución.

Hay que tener en cuenta que la información de fecha/hora del Almacén de Consultas utiliza el tipo de datos datetimeoffset . Tenlo en cuenta cuando filtres los datos.

Listado 4-8. Obtener información sobre consultas caras del Almacén de Consultas

SELECT TOP 50 
  q.query_id, qt.query_sql_text, qp.plan_id, qp.query_plan
  ,SUM(rs.count_executions) AS [Execution Cnt]
  ,CONVERT(INT,SUM(rs.count_executions * 
    (rs.avg_logical_io_reads + avg_logical_io_writes)) / 
      SUM(rs.count_executions)) AS [Avg IO]
  ,CONVERT(INT,SUM(rs.count_executions * 
    (rs.avg_logical_io_reads + avg_logical_io_writes))) AS [Total IO]
  ,CONVERT(INT,SUM(rs.count_executions * rs.avg_cpu_time) /
    SUM(rs.count_executions)) AS [Avg CPU]
  ,CONVERT(INT,SUM(rs.count_executions * rs.avg_cpu_time)) AS [Total CPU]
  ,CONVERT(INT,SUM(rs.count_executions * rs.avg_duration) / 
    SUM(rs.count_executions)) AS [Avg Duration]
  ,CONVERT(INT,SUM(rs.count_executions * rs.avg_duration)) 
    AS [Total Duration]
  ,CONVERT(INT,SUM(rs.count_executions * rs.avg_physical_io_reads) / 
    SUM(rs.count_executions)) AS [Avg Physical Reads]
  ,CONVERT(INT,SUM(rs.count_executions * rs.avg_physical_io_reads)) 
    AS [Total Physical Reads]
  ,CONVERT(INT,SUM(rs.count_executions * rs.avg_query_max_used_memory) / 
    SUM(rs.count_executions)) AS [Avg Memory Grant Pages]
  ,CONVERT(INT,SUM(rs.count_executions * rs.avg_query_max_used_memory)) 
    AS [Total Memory Grant Pages]
  ,CONVERT(INT,SUM(rs.count_executions * rs.avg_rowcount) /
    SUM(rs.count_executions)) AS [Avg Rows]
  ,CONVERT(INT,SUM(rs.count_executions * rs.avg_rowcount)) AS [Total Rows]
  ,CONVERT(INT,SUM(rs.count_executions * rs.avg_dop) /
    SUM(rs.count_executions)) AS [Avg DOP]
  ,CONVERT(INT,SUM(rs.count_executions * rs.avg_dop)) AS [Total DOP]
FROM 
  sys.query_store_query q WITH (NOLOCK)
    JOIN sys.query_store_plan qp WITH (NOLOCK) ON
      q.query_id = qp.query_id
    JOIN sys.query_store_query_text qt WITH (NOLOCK) ON
      q.query_text_id = qt.query_text_id
    JOIN sys.query_store_runtime_stats rs WITH (NOLOCK) ON
      qp.plan_id = rs.plan_id 
    JOIN sys.query_store_runtime_stats_interval rsi WITH (NOLOCK) ON
      rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE
  rsi.end_time >= DATEADD(DAY,-1,SYSDATETIMEOFFSET())
GROUP BY
  q.query_id, qt.query_sql_text, qp.plan_id, qp.query_plan
ORDER BY 
  [Avg IO] DESC
OPTION (MAXDOP 1, RECOMPILE);

Obviamente, puedes ordenar los datos por criterios distintos a la E/S media. También puedes añadir predicados a la cláusula WHERE y/o HAVING de la consulta para acotar los resultados. Por ejemplo, puedes filtrar por columnas DOP si quieres detectar consultas que utilizan el paralelismo en un entorno OLTP y afinar la configuración del Umbral de Coste por Paralelismo.

Otro ejemplo es para detectar las consultas que utilizan la caché de planes como globo. El código del Listado 4-9 proporciona información sobre las consultas que generan múltiples planes de ejecución debido a diferentes configuraciones de contexto. Las dos razones más comunes para ello son las sesiones que utilizan diferentes opciones de SET y las consultas que hacen referencia a objetos sin nombre de esquema.

Listado 4-9. Consultas con diferentes configuraciones de contexto

SELECT 
    q.query_id, qt.query_sql_text
    ,COUNT(DISTINCT q.context_settings_id) AS [Context Setting Cnt]
    ,COUNT(DISTINCT qp.plan_id) AS [Plan Count]
FROM 
    sys.query_store_query q WITH (NOLOCK)
        JOIN sys.query_store_query_text qt WITH (NOLOCK) ON
            q.query_text_id = qt.query_text_id
        JOIN sys.query_store_plan qp WITH (NOLOCK) ON
            q.query_id = qp.query_id
GROUP BY
    q.query_id, qt.query_sql_text
HAVING
    COUNT(DISTINCT q.context_settings_id) > 1
ORDER BY 
    COUNT(DISTINCT q.context_settings_id)
OPTION (MAXDOP 1, RECOMPILE);

El listado 4-10 muestra cómo encontrar consultas similares basándose en el valor query_hash (SQL en la salida representa una consulta seleccionada aleatoriamente del grupo). Normalmente, esas consultas pertenecen a una carga de trabajo ad-hoc no parametrizada en el sistema. Puedes parametrizar esas consultas en el código. Si eso no es posible, plantéate utilizar la parametrización forzada, de la que hablaré en el Capítulo 6.

Listado 4-10. Detectar consultas con valores query_hash duplicados

;WITH Queries(query_hash, [Query Count], [Exec Count], qtid)
AS
(
    SELECT TOP 100
        q.query_hash
        ,COUNT(DISTINCT q.query_id)
        ,SUM(rs.count_executions)
        ,MIN(q.query_text_id)
    FROM
        sys.query_store_query q WITH (NOLOCK)
            JOIN sys.query_store_plan qp WITH (NOLOCK) ON
                q.query_id = qp.query_id
            JOIN sys.query_store_runtime_stats rs WITH (NOLOCK) ON
                qp.plan_id = rs.plan_id
    GROUP BY
        q.query_hash
    HAVING
        COUNT(DISTINCT q.query_id) > 1
)
SELECT
    q.query_hash
    ,qt.query_sql_text AS [Sample SQL]
    ,q.[Query Count]
    ,q.[Exec Count]
FROM
    Queries q CROSS APPLY
    (
        SELECT TOP 1 qt.query_sql_text
        FROM sys.query_store_query_text qt WITH (NOLOCK)
        WHERE qt.query_text_id = q.qtid
    ) qt
ORDER BY 
    [Query Count] DESC, [Exec Count] DESC
OPTION(MAXDOP 1, RECOMPILE);

Como puedes ver, las posibilidades son infinitas. Utiliza el Almacén de Consultas si puedes permitirte su sobrecarga en tu sistema.

Por último,, el comando DBCC CLONEDATABASE te permite generar un clon sólo de esquema de la base de datos y utilizarlo para investigar problemas de rendimiento. Por defecto, un clon incluirá los datos del Almacén de Consultas. Puedes restaurarlo y realizar un análisis en otro servidor para reducir la sobrecarga en producción .

Herramientas de terceros

Como ya has visto en, SQL Server proporciona un conjunto muy rico y amplio de herramientas para localizar consultas ineficaces. No obstante, también puedes beneficiarte de las herramientas de monitoreo desarrolladas por otros proveedores. La mayoría te proporcionará una lista de las consultas que consumen más recursos para su análisis y optimización. Muchas también te darán la línea de base, que puedes utilizar para analizar tendencias y detectar consultas regresivas.

No voy a hablar de herramientas concretas; en su lugar, quiero ofrecerte algunos consejos para elegir y utilizar las herramientas.

La clave para utilizar cualquier herramienta es comprenderla. Investiga cómo funciona y analiza sus limitaciones y qué datos puede pasar por alto. Por ejemplo, si una herramienta obtiene los datos sondeando la vista sys.dm_exec_requests según un calendario, puede perderse una gran parte de las consultas pequeñas pero ejecutadas con frecuencia que se ejecutan entre sondeo y sondeo. Por otra parte, si una herramienta determina las consultas ineficaces mediante esperas de sesión, los resultados dependerán en gran medida de la carga de trabajo de tu sistema, de la cantidad de datos almacenados en caché en el buffer pool y de muchos otros factores.

Dependiendo de tus necesidades específicas, estas limitaciones pueden ser aceptables. Recuerda el Principio de Pareto: no es necesario que optimices todas las consultas ineficaces del sistema para conseguir un rendimiento de la inversión deseado (o aceptable). No obstante, puedes beneficiarte de una visión holística y desde múltiples perspectivas. Por ejemplo, es muy fácil cotejar la lista de consultas ineficaces de una herramienta con las estadísticas de ejecución basadas en la caché de planes para obtener una lista más completa.

Pero hay otra razón importante para entender tu herramienta: estimar la cantidad de gastos generales que podría introducir. Algunas DMV son muy caras de ejecutar. Por ejemplo, si una herramienta llama a la función sys.dm_exec_query_plan durante cada sondeo de sys.dm_exec_requests, puede suponer un aumento apreciable de la sobrecarga en sistemas ocupados. Tampoco es infrecuente que las herramientas creen trazas y sesiones xEvent sin tu conocimiento.

No confíes ciegamente en los libros blancos y en los vendedores cuando afirman que una herramienta es inofensiva. Su impacto puede variar en distintos sistemas. Siempre es mejor probar la sobrecarga con tu carga de trabajo, tomando como base el sistema con y sin la herramienta. Ten en cuenta que la sobrecarga no siempre es estática y puede aumentar a medida que cambia la carga de trabajo.

Por último, considera las implicaciones de seguridad de tu elección de herramientas. Muchas herramientas te permiten crear monitores personalizados que ejecutan consultas en el servidor, lo que abre la puerta a actividades maliciosas. No concedas permisos innecesarios al inicio de sesión de la herramienta, y controla quién tiene acceso a gestionarla.

Al final, elige el enfoque que mejor te permita localizar las consultas ineficaces y que mejor funcione con tu sistema. Recuerda que la optimización de las consultas ayudará en cualquier sistema.

Resumen

Las consultas ineficaces afectan al rendimiento de SQL Server y pueden sobrecargar el subsistema de disco. Incluso en los sistemas que tienen memoria suficiente para almacenar en caché los datos en el buffer pool, esas consultas queman CPU, aumentan los bloqueos y afectan a la experiencia del cliente.

SQL Server realiza un seguimiento de las métricas de ejecución de cada plan almacenado en caché y las expone a través de la vista sys.dm_exec_query_stats. También puedes obtener estadísticas de ejecución de procedimientos almacenados, desencadenadores y funciones escalares definidas por el usuario con las vistas sys.dm_exec_procedure_stats, sys.dm_exec_trigger_stats, y sys.dm​_exec_function_stats, respectivamente.

Tus estadísticas de ejecución basadas en la caché de planes no seguirán las métricas de ejecución en tiempo real de los planes de ejecución, ni incluirán las consultas que no tengan planes almacenados en caché. Asegúrate de tener esto en cuenta en tu proceso de análisis y ajuste de consultas.

Puedes capturar consultas ineficaces en tiempo real con Eventos Extendidos y Trazas SQL. Ambos enfoques introducen sobrecarga, especialmente en sistemas con mucho tráfico. También proporcionan datos en bruto, que tendrás que procesar y agregar para su posterior análisis.

En SQL Server 2016 y posteriores, puedes utilizar el Almacén de Consultas. Se trata de una gran herramienta que no depende de la caché del plan y te permite localizar rápidamente las regresiones del plan. El Almacén de Consultas añade cierta sobrecarga; esto puede ser aceptable en muchos casos, pero vigílalo cuando actives la función.

Por último, puedes utilizar herramientas de monitoreo de terceros para encontrar consultas ineficientes. Recuerda investigar cómo funciona una herramienta y comprender sus limitaciones y gastos generales.

En el próximo capítulo, hablaré de algunas técnicas habituales que puedes utilizar para optimizar las consultas ineficaces.

Lista de comprobación para la resolución de problemas

• Obtén la lista de consultas ineficaces de la vista sys.dm_exec_query_stats. Ordena los datos según tu estrategia de resolución de problemas (CPU, E/S, etc.).

• Detecta los procedimientos almacenados más caros con la vista sys.dm_exec​_proce⁠dure_stats.

• Considera la posibilidad de activar el Almacén de Consultas en tu sistema y analizar los datos que recopiles. (Esto puede ser factible o no si ya utilizas herramientas de monitoreo externas).

• Activa los indicadores de rastreo T7745 y 7752 para mejorar el rendimiento del apagado e inicio de SQL Server cuando utilices el Almacén de Consultas.

• Analiza los datos de herramientas de monitoreo de terceros y compáralos con los datos de SQL Server.

• Analizar la sobrecarga que las consultas ineficaces introducen en el sistema. Correlaciona el consumo de recursos de las consultas con las estadísticas de espera y la carga del servidor.

• Optimiza las consultas si determinas que es necesario.