Cuando un motor de bases de datos recibe la petición de ejecutar una instrucción SQL, lo que debe de realizar es, además de verificar que no haya errores de sintáxis, es determinar la manera en la que debe ejecutar la instrucción SQL de tal manera que se haga de la manera más eficientes en términos de uso de recursos y tiempo estimado de ejecución, y a esto se le llama plan de ejecución.
Un plan de ejecución es, por tanto, la forma en la que el motor de base de datos considera que es la mejor manera de correr la instrucción. Si se trata de una instrucción SELECT -el caso más común- entonces la base de datos hará uso de la información sobre índices, estadísticas, particiones y toda la configuración del motor para realizar varios planes de ejecución y luego escogerá la que, de acuerdo a sus criterios, ocupe la menor cantidad de recursos y se ejecute en el menor tiempo posible.
Planes de ejecución en SQL Server
En SQL Server (específicamente en el SSMS) es posible ver el plan de ejecución que determinó SQL Server para ejecutar determinado query o conjunto de instrucciones SQL. De esta manera es posible que el DBA o el desarrollador conozca -incluso antes de que se ejecute la instrucción- cuál es la manera -paso a paso- en la que SQL Server resolverá cada una de las instrucciones a ejecutar.
Un plan de ejecución muestra el flujo que tendrán los datos -desde la lectura en la tablas origen- las transformaciones que se realizarán, agrupamientos, ordenamientos, uso de índices y cantidad de datos entre cada uno de los pasos hasta mostrar el resultado al usuario. Esto permite analizar si se está siguiendo una buena estrategia de índices, encontrar cuellos de botella o encontrar fallas en la ejecución de la instrucción SQL que pueda ser mejorado para que el uso de recursos sea el adecuado. El SSMS incluso proporciona asistencia sobre mejoras propuestas que pueden ser consideradas por el DBA para mejorar la estructura de la base de datos.
Tipos de planes de ejecución en SQL Server
En el SSMS se pueden obtener dos tipos de planes de ejecución
- Plan de ejecución estimado. Es un plan que se calcula antes de ejecutar la instrucción y se determina con la información que guarda la base de datos (cantidad de registros, particiones, estadísticas e índices existentes) para hacer una estimación del mejor plan de ejecución. Es bastante útil cuando la ejecución del query se llevará mucho tiempo o se está en una fase de construcción de una aplicación y solo se quiere conocer la mejor alternativa para un query que se ejecutará en el futuro.
- Plan de ejecución real. Este plan se obtiene después de ejecutarse el query y muestra el plan de ejecución que en la realidad se ejecutó. Puede diferir del estimado.
Obtención del plan de ejecución con el SSMS
Para obtenerlo es tan fácil como escribir el query que se quiere analizar en el SSMS y hacer uso de los dos botones que para el efecto, se activan y a continuación es posible ver el plan de ejecución estimado o se ejecuta el query y luego se puede ver la pestaña con el plan de ejecución real
La salida del plan de ejecución, en el SSMS, es un gráfico que se lee de izquierda a derecha y de arriba a abajo para ver cada subproceso que se debe realizar para ejecutar el query. Es básicamente un árbol con nodos (pasos) unidos por flechas (flujo de datos). El ancho de la flecha es proporcional al número de filas (Puede ser el real o uno estimado).
Todas las consultas del lote se analizan y se presentan, incluido el costo de cada consulta como porcentaje del costo total del lote, es decir, si se trata de un conjunto de 3 instrucciones SELECT, por poner un ejemplo, se determina el plan de ejecución de las tres instrucciones y se calcula el costo de cada una de ellas de tal manera que la suma de 100%. En la imagen previa, el costo de cada uno de los dos querys está encerrado en un círculo rojo.
Si se tiene la duda sobre dos o más instrucciones para saber cuál es la que usa menos recursos o se tarda menos, se pueden analizar en conjunto y la que tenga un porcentaje menor es la que muy porbablemente sea la mejor (como se muestra en la imagen anterior).
Cada nodo de la estructura en árbol se representa como un icono que especifica el operador lógico y físico utilizado para ejecutar esa parte de la consulta o instrucción. Además, cada nodo está relacionado con un nodo principal. Los nodos secundarios que tienen el mismo nodo principal se dibujan en la misma columna.. Cada nodo se conecta a su nodo principal mediante reglas con puntas de flecha.
Para las consultas en paralelo, en las que intervienen varias CPU, las Propiedades de cada nodo en el plan de ejecución gráfico muestran información acerca de los subprocesos del sistema operativo utilizados.
Información de cada nodo en el Plan de ejecución
Existe mucha información en cada nodo, alguna de las cuales se muestra a continuación
Operación física
Operador físico utilizado, como Hash Join o Nested Loops. Los operadores físicos presentados en color rojo indican que el optimizador de consultas ha emitido una advertencia, por ejemplo, la falta de estadísticas de columna o de predicados de combinación.
Operación lógica
Operador lógico que coincide con el operador físico, como el operador Inner Join. El operador lógico se enumera después del operador físico en la parte superior de la información sobre herramientas.
Tamaño estimado del registro
Tamaño en Bytes de la fila que regresará ese nodo (es un estimado).
Estimación de costos
- De Entrada/Salida (Costo estimado de toda la actividad de E/S para la operación. Este valor debe ser lo más pequeño posible).
- De CPU (Costo estimado de toda la actividad de la CPU para la operación).
Estimación de costos
- De operador (Costo del optimizador de consultas para esta operación. El costo de esta operación como % del costo total de la consulta se muestra entre paréntesis. Este valor debe ser el menor posible).
- Del subárbol (Costo total del optimizador de consultas para ejecutar esta operación y todas las operaciones del mismo subárbol anteriores a ésta).
Número estimado de filas
Cantidad de filas involucrada en la operación. Es una estimación.
Estos son solo algunos de los datos disponibles en un plan de ejecución, será necesario explicar cada uno de estos pasos y datos a profundidad