Descubre SQL Server 2022 Query Store Hints

¡Hola! Bienvenidos a un nuevo post en el mejor blog de SQL Server. En la entrada de hoy hablaremos sobre algunas de las funcionalidades más destacadas de SQL Server 2022.

Como ya sabemos, SQL Server 2022 es actualmente una realidad. Ya hemos podido comprobar algunas de sus novedades y hoy vamos a dedicarnos a estudiar más en profundidad algunas de ellas.

Caso práctico de Query Store Hints

En la demostración que vamos a realizar hoy, veremos cómo actúa Query Store en SQL Server 2022 cuando aplicamos Query Store Hints a un plan de ejecución.

Para realizar la prueba, vamos a utilizar una base de datos pública como es “BabbyNames” que la obtenemos desde el siguiente enlace:

https://github.com/MaMotos/SQLData

Se trata de una base de datos pública que contiene los nombres de los niños y niñas nacidos entre 1910 y la actualidad en cada Estado de los EE.UU.

Para comenzar con el ejercicio, vamos a realizar una query con la que obtendremos qué nombre será el más repetido cada año en cada Estado de EE.UU. obteniendo así 2 filas por año, una para el nombre más frecuente masculino y otra para el nombre más repetido femenino.

Para ello, utilizaremos la siguiente query:

WITH NameRank AS (
    SELECT
        RANK () OVER (PARTITION BY ReportYear, StateCode, Gender ORDER BY NameCount DESC) as RankByGenderAndRow,
        Id
    FROM agg.FirstNameByYearStateWide AS fnby
)
SELECT
    fnby.ReportYear, fnby.StateCode, fnby.Gender, fn.FirstName,  fnby.NameCount,
    fnby.ReportColumn1, fnby.ReportColumn2, fnby.ReportColumn3, fnby.ReportColumn4, fnby.ReportColumn5, fnby.ReportColumn6,
    fnby.ReportColumn7, fnby.ReportColumn8, fnby.ReportColumn9, fnby.ReportColumn10, fnby.ReportColumn11, fnby.ReportColumn12,
    fnby.ReportColumn13, fnby.ReportColumn14, fnby.ReportColumn15, fnby.ReportColumn16, fnby.ReportColumn17, fnby.ReportColumn18,
    fnby.ReportColumn19, fnby.ReportColumn20
FROM NameRank
JOIN agg.FirstNameByYearStateWide AS fnby ON
    fnby.Id = NameRank.Id
JOIN ref.FirstName AS fn ON fnby.FirstNameId=fn.FirstNameId
WHERE RankByGenderAndRow = 1

Al ejecutar la Query, obtendremos el resultado que os muestro a continuación:

Como podemos observar, la ejecución ha tardado 44 segundos.

Si accedemos al plan de ejecución, podemos ver que tenemos un WARNING! en el SELECT:

Al hacer clic sobre la cajita del «SELECT» y pulsar F4, podemos ver el cuadro de propiedades para acceder al mensaje de WARNING!

Detalle del warning de la instrucción Select.

La Query ejecutada se trata de una CTE (Common Table Expression), lo cual no se lleva del todo bien con el estimador de cardinalidad nuevo (140). De modo que está interpretando que le van a llegar muchas más filas de las que realmente le llegan. Esto hace que se asigne mucha más memoria a la ejecución de la que en realidad necesita.

Esto es un problema tanto para esta query como para el entorno en general, ya que estamos malgastando recursos.

Aplicando Query Store Hint

Para este caso práctico, tendremos Query Store configurado de la siguiente manera:

Con el siguiente script podemos ver qué query_id tiene asignado el plan de ejecución:

-- Identify the Query Store Query_id of the query statement you wish to modify
SELECT qt.query_sql_text, q.query_id
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
WHERE query_sql_text LIKE N'%NameRank%'
AND query_sql_text NOT LIKE N'%query_store%';
GO

Como vemos, se trata del query_id 1. Con esta información, podemos comprobar si dicho plan de ejecución tiene activo algún Query Store Hint con el siguiente script:

-- Get Query Store Hints active on the query_id plan
SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc 
FROM sys.query_store_query_hints 
WHERE query_id = 1;

Query Store Hints activos en query_id = 1.

Observamos que no hay ningún Query Store Hint activo para el plan de ejecución con query_id = 1.

El próximo paso será activar el Query Store Hint necesario para una correcta ejecución de la query.

Para ello, vamos a obligarle a utilizar el estimador de cardinaliadad legacy (70) que es el de las versiones de SQL Server hasta 2012 incuida.

Para poder utilizar el estimador, emplearemos el traceflag 9481. Aplicaremos el siguiente script para añadir la traza al plan de ejecución:

-- Add Query Store Hint to plan_id
EXEC sp_query_store_set_hints 1, N'OPTION(QUERYTRACEON 9481)'

¡Perfecto! ya tenemos la traza añadida al plan de ejecución. Comprobémoslo:

A continuación, aplicamos la ejecución de la query para ver si hay mejoras:

Ejecución una vez aplicado Query Store Hint.

Como resultado, la ejecución ha pasado de 44 segundos a 5 segundos. Por lo tanto, lo consideramos un gran avance y un éxito gracias a Query Store Hint.

Conclusión

Query Store Hint es una buena herramienta a la hora de poder aplicar hints directamente a los planes de ejecución guardados en Query Store. Se pueden comprobar los diferentes Query Store Hints que se pueden aplicar con el siguiente script:

SELECT * FROM sys.dm_exec_valid_use_hints

No obstante, recomendamos realizar un estudio de indexación y mantener las estadísticas actualizadas constantemente antes de aplicar ningún Query Store Hint.

Y hasta aquí el post de hoy. Si te interesa la tecnología y estas especializándote en sistemas de BBDD SQL Server te recomendamos nuestros últimos posts:

Change Data Capture en SQL Server

Evita problemas en tu migración SQL Server con Data Migration Assistant

Conoce las estadísticas de espera o Waits para SQL Server

Si necesitas optimizar y mejorar el rendimiento de tu SQL Server, en Aleson ITC podemos ayudarte.

Saber más