Tal vez has escuchado hablar sobre un lugar mágico en la memoria RAM donde SQL Server almacena algunas cosas para acceder rápidamente a ellas cuando tus usuarios las necesitan. Una parte de este espacio se utiliza para guardar la ruta que recorrerá el motor durante la ejecución de un proceso, por lo que es una gran estrategia entender cómo funciona para ayudarnos a analizar la forma en la que SQL Server se comporta y que tan bien o mal se ejecutan nuestras transacciones.
Image by slidesgo / Freepik
Plan Cache
Cada vez que lanzas una transacción, SQL Server compila y almacena un plan que dicta la forma en la que se ejecutará dicho proceso, esta secuencia es almacenada para que posteriores ejecuciones sean mas rápidas. Este lugar es conocido como el cache de planes de ejecución, donde también tenemos acceso a información relacionada con el comportamiento de nuestras transacciones (este post tiene información detallada de este componente).
Blitz Cache
Por ahora me gustaría centrarme en detectar las consultas más costosas de una instancia, para eso normalmente utilizo un SP desarrollado por Brent Ozar llamado BlitzCache, el cual puedes instalar en tu instancia y te ayudará a entender que ha pasado recientemente en ella.
Para empezar instalalo y ejecutalo con este comando:
exec sp_BlitzCache @Top = 50
En principio, traerá 50 transacciones costosas, medida que está basada en el tiempo consumido en CPU. Aquí podrás ver información como:
El costo asignado por SQL Server
Conteos y promedios de lecturas, escrituras, ejecuciones, registros y mucho más (agregando el parámetro @ExpertMode = 1)
El tiempo total en CPU y su tiempo promedio por ejecución
El texto de la consulta y el plan diseñado por SQL
Un resumen de advertencias relacionadas a la transacción
Puedes detectar diferentes costos cambiando la forma en la que el SP ordena el rango utilizando el parámetro @SortOrder y así podrás encontrar cuales son las consultas con mayor número de ejecuciones, más lecturas/escrituras o mayor cantidad de memoria asignada. Adicionalmente, si quieres enviar un informe a tus desarrolladores puedes utilizar el comando @ExportToExcel = 1, con lo que es más sencillo pegar y analizar los datos en Excel; explora otras opciones que trae el SP.
Te animo a revisar la documentación disponible para BlitzCache y tambien que instales y pruebes los otros SPs que están incluidos en el repositorio de github del proyecto.
Espero tengas un feliz día