Localizando advertencias en los planes de ejecución

Hace aproximadamente un año, Jose Manuel Jurado publicó este artículo en el blog de Microsoft. La verdad es que cuando lo vi me resultó curioso pero no le di mayor importancia, será porque no estaba yo tan enamorado como ahora de las bondades de query store. El caso es que parece que el artículo se me quedó grabado aunque nunca había vuelto a él ni lo había puesto en práctica. Y digo esto porque esta semana, hablando con mis compañeros de trabajo sobre los eventos extendidos y explicándoles cómo xEvents ampliaba con creces las características de SQL Server Profiler, les comenté que podrías crear una sesión que solo capture eventos que desencadenen cierto error.

Esto no es nuevo para vosotros, ya lo vimos aquí. Entonces se me encendió la bombilla y volvió a mi el artículo de José Manuel, ¿y si capturamos con eventos extendidos las consultas con advertencias en sus planes de ejecución?

Por qué xEvents para localizar advertencias

Como os he dicho, la idea de esta solución es una mezcla de curiosidad y mini-reto personal. Sin embargo, se me ocurre que puede ser una solución ideal para varios escenarios gracias a la capacidad de eventos extendidos de activar y desactivar la captura de datos a nuestra voluntad. Esto lo hace ideal para capturar eventos contenidos en el tiempo que nosotros hayamos localizado un problema. Tampoco vamos a necesitar en estos casos el historial de planes de ejecución como nos ofrece Query Store. 

Capturando advertencias de conversión con eventos extendidos

Como algunos ya sabéis, un error de conversión de datos puede generar dos tipos de problema: errores de cardinalidad o errores en el plan de búsqueda. Es común que sean ambos pero, vamos a partir de la misma premisa del escenario original y vamos a capturar solo los warning de conversión del tipo “seek plan” o errores en el plan de búsqueda. Además vamos a añadir un par de filtros extra como limitar la captura a sesiones con un session ID mayor de 50 para evitar procesos de usuario y a limitarlo a las bases de datos con un ID mayor que 4 para evitar las bases de datos internas de SQL.

CREATE EVENT SESSION [Convert Warnings] ON SERVER 
ADD EVENT sqlserver.plan_affecting_convert(
ACTION(sqlserver.database_name,sqlserver.is_system,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[session_id],(50)) AND [package0].[equal_uint64]([convert_issue],'Seek Plan') AND [sqlserver].[database_id]>(4)))
ADD TARGET package0.event_file(SET filename=N'Convert Warnings')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Y aquí lo podéis ver en funcionamiento:

¿Y el resto de advertencias?

Ahora que hemos visto que mi idea original es viable, ¿por qué quedarnos aquí? Ya vimos en el post sobre planes de ejecución que existen más tipos de advertencias en los planes de ejecución. ¿Y si hacemos una sesión que capture todos esos tipos de alertas? Pues sí mis queridos lectores, claro que os voy a enseñar esto también, ya sabéis que no soy yo de conformarme con poco. Tras indagar un poco en todas las posibles advertencias de planes de ejecución que podemos capturar con xEvents he creado esta sesión que vamos a repasar ahora juntos:

CREATE EVENT SESSION [WARNINGS] ON SERVER 
ADD EVENT sqlserver.hash_warning(
    ACTION(sqlserver.sql_text,sqlserver.username)
    WHERE (([sqlserver].[session_id]>(50)) AND ([sqlserver].[database_id]>(4)))),
ADD EVENT sqlserver.missing_column_statistics(
    ACTION(sqlserver.sql_text,sqlserver.username)
    WHERE (([sqlserver].[session_id]>(50)) AND ([sqlserver].[database_id]>(4)))),
ADD EVENT sqlserver.missing_join_predicate(
    ACTION(sqlserver.sql_text,sqlserver.username)
    WHERE (([sqlserver].[session_id]>(50)) AND ([sqlserver].[database_id]>(4)))),
ADD EVENT sqlserver.plan_affecting_convert(
    ACTION(sqlserver.sql_text,sqlserver.username)
    WHERE (([sqlserver].[session_id]>(50)) AND ([sqlserver].[database_id]>(4)))),
ADD EVENT sqlserver.sort_warning(
    ACTION(sqlserver.sql_text,sqlserver.username)
    WHERE (([sqlserver].[session_id]>(50)) AND ([sqlserver].[database_id]>(4)))),
ADD EVENT sqlserver.unmatched_filtered_indexes(
    ACTION(sqlserver.sql_text,sqlserver.username)
    WHERE (([sqlserver].[session_id]>(50)) AND ([sqlserver].[database_id]>(4))))
ADD TARGET package0.event_file(SET filename=N'WARNINGS')
WITH (STARTUP_STATE=ON)
GO

Aquí podemos ver varios eventos todos ellos relacionados con advertencias en los planes de ejecución entre los que encontramos:

• Hash_warning: Este tipo de advertencia nos  indica que la operación hash ha usado más memoria de la disponible y ha tenido que escribir datos en el disco. 
• Missing_column_statistics: Veremos esta advertencia cuando una consulta accede a una columna que no tiene estadísticas disponibles que podrían haber sido útiles para la optimización de consultas. 
• Missing_join_predicate: Se produce cuando una consulta ejecutada no tiene un predicado de combinación.
• Plan_affecting_convert: Este es el caso que hemos visto en el escenario anterior, hay un error de conversión afectando al plan de ejecución.
• Sort_Warning: Indica que la operación de ordenación ha usado más memoria de la disponible y ha tenido que escribir datos en el disco. 
• Unmatched_filtered_indexes: Este error es causado cuando SQL Server no puede hacer uso de un índice filtrado debido a que la consulta está parametrizada. Y si, esta es una de las limitaciones de SQL Server que más quebraderos de cabeza nos pueden dar.

Además, para cada uno de los eventos se han aplicado los mismos filtros de session ID y Database ID del escenario anterior.

Conclusión

Ya sea con query store como hizo Jose Manuel o con eventos extendidos como hemos visto aquí localizar las consultas con advertencias en sus planes de ejecución para luego arreglar el problema nos ayudará a mejorar el rendimiento de nuestro servidor y a no malgastar recursos. No cometas el error que cometí yo y no esperes un año para ponerte a buscar proactivamente este tipo de problemas. Y si se te ocurre otra forma de conseguir lo mismo dejalo en los comentarios o ponte en contacto conmigo para que te publique un artículo sobre el tema, yo estaré encantado de hacerlo. Somos una comunidad y el objetivo es compartir el conocimiento.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!