Por Leandro Sarmiento - Data Engineer en RocBird
-
Planes de ejecución:
Un plan de ejecución muestra las operaciones que debe realizar SQL Server para obtener los resultados de una consulta junto con el costo de cada operación. También nos permite ver si SQL Server está usando o no los índices que se han creado para las tablas.
Ejemplo
Para obtener el plan de ejecución se debe hacer clic en la opción “Include Actual Execution Plan”, y luego ejecutar la consulta:
El plan de ejecución correspondiente se muestra en la solapa “Execution plan”
Los planes de ejecución se leen de derecha a izquierda y de arriba abajo, en este caso SQL Server comienza la ejecución con un Clustered Index Seek. Se puede ver el costo relativo de cada operador expresado como un porcentaje del costo total de la consulta lo que permite centrarse rápidamente en el operador más costoso. Las flechas muestran el flujo de datos de operador a operador y su anchura indica la cantidad de filas que se mueven entre operadores. Si nos paramos en una flecha o un operador, obtenemos más detalle:
Antes de ejecutar una consulta también podemos ver su “Plan de Ejecución Estimado” lo que es especialmente útil para conocer de antemano que hará SQL Server sin necesidad de ejecutar una consulta que pueda resultar muy pesada para el motor de base de datos, sin embargo el plan de ejecución final puede ser distinto al estimado.
Con estas herramientas podemos realizar una optimización básica de nuestras consultas y entender qué está haciendo SQL Server.
Pasos a seguir y ejemplos:
Cuando reconocemos que una consulta esta demorando en ejecutarse o queremos conocer su performance podemos seguir los siguiente pasos:
Ejecutar el comando SET STATISTICS IO ON
Incluir el Plan de Ejecución Actual o Estimado.
Con el plan de ejecución reconocemos las operaciones más costosas de la consulta.
Creamos índices que puedan soportar las operaciones identificadas, también podemos consultar la tabla sys.indexes para saber qué índices ya tiene creada la tabla y poder usarlos.
La siguiente consulta agrupa por la columna “TerritoryID” y cuenta su cantidad:
Como no existe un índice en la mencionada columna SQL Server realiza las operaciones Clustered Index Scan, es decir que está usando el Clustered Index creado a partir de la clave primaria (en este caso) y HashMatch Aggregate, esta última operación se escoge porque la columna utilizada para el GROUP BY no cuenta con un índice.
Además, si vemos la salida de SET STATISTICS IO vemos que se tuvieron que realizar muchos Logical Reads:
Si creamos un NonClustered Index en la columna TerritoryID:
Ahora SQL Server hace un Index Scan en el índice no agrupado que es un poco más costoso, pero realiza un Stream Aggregate que es menos costoso (esta es la naturaleza de la optimización de consulta, si se logra disminuir el costo de un operador aumentará el costo de otro).
Los valores devueltos por STATISTICS IO cambiaron significativamente:
En la siguiente consulta la columna “ShipToAddressID” no tiene un índice:
Entonces, para devolver los datos ordenados SQL Server debe utilizar el operador Sort. Ordenar grandes conjuntos de datos puede ser un gran golpe a la performance en SQL Server. Los datos tienen que ser ordenados en memoria o si no caben en memoria volcarse en tempdb.
STATISTIC IO nos devuelve lo siguiente:
Luego de crear un NonClustered Index en la columna ShipToAddressID vemos que SQL Server ahora no realiza la operación de ordenamiento, sino que solo escanea el índice:
Los valores SET STATISTICS IO son:
Además de los casos anteriores se recomienda evaluar la creación de NonClustered Index en:
-Columnas que se utilizan como filtros en la cláusula WHERE. La consulta debe ser lo bastante selectiva para que SQL Server haga uso del índice.
-Columnas que forman claves foráneas.
-En las columnas utilizadas para realizar JOINs.
-Consultas que agreguen datos y utilicen la cláusula GROUP BY, la columna o columnas incluidas en esta última deberían estar en un índice.
-Consultas que utilicen funciones agregadas y no incluyan GROUP BY, por ejemplo si se usa la función de agregación MIN() y contamos con un índice apropiado, SQL Server puede buscar solamente por el primer valor del índice y no tiene que escanear la tabla entera.
-Las columnas usadas en la cláusula ORDER BY.
-La cláusula SELECT, creando un índice para las columnas devueltas por el SELECT.
La optimización de consultas involucra muchos más temas y conocimientos que escapan al alcance de este artículo pero con los conceptos y ejemplos brindados se puede comenzar a realizar un análisis y optimización preliminares además de servir como una breve introducción al tema.