Descripción de los planes de consulta

Comprender cómo funcionan los optimizadores de bases de datos es esencial antes de profundizar en los detalles del plan de ejecución. SQL Server usa un optimizador de consultas basado en costos, que calcula el costo de varios planes posibles en función de las estadísticas que tiene en las columnas que se usan y los posibles índices para cada operación del plan de consulta. Esta información ayuda al optimizador a determinar el costo total de cada plan. Las consultas complejas pueden tener miles de planes de ejecución posibles, pero el optimizador no evalúa cada uno de ellos. En su lugar, usa heurística para identificar planes que probablemente funcionen bien y, a continuación, selecciona el plan de costo más bajo de los evaluados.

Dado que el optimizador de consultas está basado en costos, es fundamental proporcionarle entradas precisas para la toma de decisiones. SQL Server se basa en estadísticas para realizar un seguimiento de la distribución de datos en columnas e índices, y estas estadísticas deben mantenerse actualizadas para evitar generar planes de ejecución poco óptimos. Aunque SQL Server actualiza automáticamente sus estadísticas a medida que cambian los datos en una tabla, es posible que se necesiten actualizaciones más frecuentes para cambiar rápidamente los datos. El optimizador tiene en cuenta muchos factores al crear un plan, incluido el nivel de compatibilidad de la base de datos, las estimaciones de filas en función de las estadísticas y los índices disponibles.

Cuando un usuario envía una consulta al motor de base de datos, ocurre el proceso siguiente:

1. La consulta se analiza para obtener una sintaxis adecuada y, si es correcta, se genera un árbol de análisis de objetos de base de datos.
2. A continuación, el árbol de análisis se introduce en un componente del motor de base de datos denominado Algebrizer para el enlace. Este paso valida que existen columnas y objetos en la consulta e identifica los tipos de datos que se procesan. La salida es un árbol de procesador de consultas, que actúa como entrada para el paso siguiente.
3. La optimización de consultas consume mucha CPU, por lo que el motor de base de datos almacena en caché los planes de ejecución en un área de memoria especial denominada caché del plan. Si ya existe un plan para la consulta, se recupera de la memoria caché. Cada consulta de la memoria caché tiene un valor hash generado en función del T-SQL de la consulta, conocido como el query_hash. El motor genera un query_hash para la consulta actual y comprueba si hay coincidencias en la memoria caché del plan.
4. Si no existe ningún plan, el optimizador de consultas usa su optimizador basado en costos para generar varias opciones de plan de ejecución basadas en estadísticas sobre las columnas, tablas e índices usados en la consulta. La salida es un plan de ejecución de consultas.
5. La consulta se ejecuta mediante un plan de ejecución de la memoria caché del plan o un nuevo plan generado en el paso anterior. La salida es los resultados de la consulta.

Veamos un ejemplo. Considere la consulta siguiente:

SELECT orderdate,
        AVG(salesAmount)
FROM FactResellerSales
WHERE ShipDate = '2013-07-07'
GROUP BY orderdate;

En este ejemplo, SQL Server comprueba la existencia de las columnas OrderDate, ShipDate y SalesAmount en la tabla FactResellerSales . Si existen estas columnas, SQL Server genera un valor hash para la consulta y examina la memoria caché del plan para un valor hash coincidente. Si se encuentra un valor hash coincidente, el motor intenta reutilizar el plan. Si no se encuentra ningún valor hash coincidente, SQL Server examina las estadísticas disponibles en las columnas OrderDate y ShipDate .

La WHERE cláusula que hace referencia a la columna ShipDate se conoce como predicado en esta consulta. Si hay un índice no clúster que incluye la columna ShipDate , ES probable que SQL Server lo incluya en el plan, siempre que los costos sean menores que recuperar datos del índice agrupado. Después, el optimizador elige el plan de costo más bajo de las opciones disponibles y ejecuta la consulta.

Los planes de consulta combinan una serie de operadores relacionales para recuperar datos y capturar información como recuentos estimados de filas. Otro elemento del plan de ejecución es la memoria necesaria para las operaciones como combinar o ordenar datos, conocidos como concesión de memoria. La concesión de memoria resalta la importancia de las estadísticas. Si SQL Server calcula que un operador devuelve 10 000 000 filas cuando devuelve realmente 100, se asigna una concesión de memoria mayor a la consulta. Una concesión de memoria excesivamente grande puede causar dos problemas. En primer lugar, la consulta puede encontrar una RESOURCE_SEMAPHORE espera, lo que indica que está esperando que SQL Server asigne una gran cantidad de memoria. De manera predeterminada, SQL Server espera 25 veces el costo de la consulta (en segundos) antes de ejecutarse, hasta un máximo de 24 horas. En segundo lugar, si no hay suficiente memoria disponible cuando se ejecuta la consulta, se derrama en tempdb, lo que es más lento que el funcionamiento en la memoria.

El plan de ejecución también almacena otros metadatos sobre la consulta, como el nivel de compatibilidad de la base de datos, el grado de paralelismo y los parámetros proporcionados si la consulta está parametrizada.

Los planes de consulta se pueden ver en una representación gráfica o en un formato basado en texto. Las opciones basadas en texto se invocan con comandos SET y solo se aplican a la conexión actual. Estos planes se pueden ver en cualquier lugar donde pueda ejecutar consultas de T-SQL.

La mayoría de los DBA prefieren planes gráficos porque permiten ver el plan en su conjunto, incluida la forma del plan. Hay varias maneras de ver y guardar planes de consulta gráficos. La herramienta más común para este propósito es SQL Server Management Studio. Además, hay herramientas de terceros que admiten la visualización de planes de ejecución gráficos.

Hay tres tipos diferentes de planes de ejecución.

Plan de ejecución estimado

El optimizador de consultas genera este tipo de plan de ejecución. Los metadatos y el tamaño de la concesión de memoria de consulta se basan en estimaciones de las estadísticas presentes en la base de datos en el momento de la compilación de consultas. Para ver un plan estimado basado en texto, ejecute el comando SET SHOWPLAN_ALL ON antes de ejecutar la consulta. Al ejecutar la consulta, verá los pasos del plan de ejecución, pero la consulta no se ejecutará y no verá ningún resultado. La opción SET permanece en vigor hasta que la desactive.

Plan de ejecución real

Este tipo de plan es el mismo que el plan estimado; sin embargo, también incluye el contexto de ejecución de la consulta. Este contexto contiene los recuentos de filas estimados y reales, las advertencias de ejecución, el grado real de paralelismo (número de procesadores usados) y los tiempos transcurridos y de CPU usados durante la ejecución. Para ver un plan real basado en texto, ejecute el comando SET STATISTICS PROFILE ON antes de ejecutar la consulta. La consulta se ejecuta y obtiene el plan y los resultados.

Estadísticas de consulta activa

Esta opción de visualización de planes combina los planes estimados y reales en un plan animado que muestra el progreso de la ejecución a través de los operadores. Se actualiza cada segundo y muestra el número real de filas que fluyen a través de los operadores. Otra ventaja de Live Query Statistics es que muestra la entrega del operador al operador, lo que puede resultar útil para solucionar problemas de rendimiento. Dado que este tipo de plan está animado, solo está disponible como un plan gráfico.