Un plan de ejecución SQL Server es el mapa más eficiente y menos costoso que es generado por los cálculos de los algoritmos de Query Optimizer para ejecutar la consulta T-SQL enviada. Los planes de ejecución son usados por los administradores de bases de datos para solucionar los problemas de consultas de desempeño pobre para aislar la parte de la consulta que es la raíz del problema de desempeño.
Cuando una consulta T-SQL es enviada al motor de base de datos de SQL Server, un número específico de procesos será asignado para trabajar en esa consulta, concurrentemente, para ejecutarla en el mínimo tiempo con la menor cantidad de recursos.
En el primer paso, el Motor Relacional de SQL Server revisará que la consulta T-SQL esté escrita correctamente en un proceso llamado Parsing. Un árbol de análisis gramatical será generado desde el analizador que contiene los pasos que son requeridos para ejecutar la consulta enviada. Para consultas DML, el árbol de análisis gramatical será procesado por Algebrizer que es responsable de resolver el nombre de los objetos de base de datos que son usados en la consulta. El árbol del procesador de consultas que es generado por Algebrizer será pasado al Query Optimizerde SQL Server.
Lo que el Optimizador de Consultas hace es especificar la mejor manera de ejecutar la consulta enviada eligiendo el plan más eficiente con el menor costo en CPU, memoria e I/O entre los diferentes planes de ejecución. El Optimizador de Consultas usa el árbol de procesador de consultas y SQL Server Statistics, los cuales son los metadatos que describen la distribución de datos y singularidad dentro de las tablas e índices de la base de datos para crear el mejor plan, tomando en consideración que el Nivel de Optimización que es usado es el nivel de optimización Completo. El Optimizador de Consultas se mantiene probando e intentado diferentes índices y órdenes para los pasos de ejecución de la consulta, calculando el costo estimado para cada paso y acumulando el costo del plan general hasta que alcanza el plan de ejecución más rápido. Cuando el plan es generado por el Optimizador de Consultas, se almacenará en un almacenaje de memoria especial llamado Plan Cache para volver a usarse en el futuro. Finalmente, el plan será usado por el Motor de Almacenamiento de SQL Server para ejecutar la consulta e insertar, recuperar o actualizar los datos solicitados.
El mínimo tiempo de ejecución con el mínimo consumo de recursos describen el plan de ejecución más eficiente esperado desde el Optimizador de Consultas. Pero es un proceso pesado que el Optimizador de Consultas evita si puede reutilizar los planes grabados en la caché de planes. Otra situación, cuando la consulta enviada es una consulta simple de un SELECT sin agregaciones o cálculos complejos, el Optimizador de Consultas prefiere crear un plan básico para ejecutar esa consulta a gastar tiempo en crear el plan más óptimo.
Para tener cálculos precisos, el Optimizador de Consultas depende de las Estadísticas de SQL Server para formular la mejor decisión respecto del plan. Estas estadísticas describen la distribución de datos y la ocurrencia dentro de las tablas e índices de la base de datos. La calidad de estas estadísticas es muy importante en crear el plan de ejecución óptimo. Si las estadísticas expiran o cambian después de crear el plan, el plan de ejecución será eliminado desde la caché de planes y reemplazado por uno más eficiente. Por defecto, las estadísticas son creadas y actualizadas automáticamente para todas las tablas e índices que pueden ser configurados desde la ventana Database Properties como sigue:
Los planes de ejecución no son almacenados en la caché de planes para siempre, un proceso interno que es responsable de limpiar las cachés, lazywriter, removerá el plan de la caché de planes si el sistema requiere más memoria o si la edad del plan, una medida especial para el uso del plan, es igual a cero. La caché de planes puede ser limpiada manualmente usando el siguiente comando DBCC:
|
1 |
DBCC FREEPROCCACHE |
También, el contenido de la caché del plan puede ser vista usando la DMV sys.dm_exec_cached_plans combinada con otras DMVs para mostrar el texto de la consulta y el plan de ejecución gráfica como sigue:
|
1 2 3 4 5 6 7 |
SELECT [EXST].[text] , [EXQP].[query_plan] ,[EXCP].[usecounts] , [EXST].[dbid] FROM sys.dm_exec_cached_plans EXCP CROSS APPLY sys.dm_exec_sql_text ( EXCP.plan_handle ) EXST CROSS APPLY sys.dm_exec_query_plan ( EXCP.plan_handle ) EXQP ; |
El resultado será así:
Hay principalmente dos tipos de planes de ejecución que pueden ser mostrados desde SQL Server Management Studio: el Plan de Ejecución Real que muestra los cálculos reales y los pasos seguidos por el Motor de Almacenamiento de SQL Server mientras se ejecutan la consulta enviada, lo cual requiere ejecutar la consulta enviada para generar el plan real. El Plan de Ejecución Estimado muestra los cálculos aproximados y los pasos estimados que pueden ser seguidos por el Motor de Almacenamiento de SQL Server para ejecutar la consulta enviada después de analizar gramaticalmente la consulta sin la necesidad de ejecutarla para generar ese tipo de planes de ejecución lógica. Y este tipo de planes que son generados por el Optimizador de Consultas y almacenados en la caché de planes.
Los planes de ejecución son usualmente usados para solucionar problemas de desempeño de consultas, ya que nos da información precisa acerca de la ejecución de la consulta. Pero el plan de ejecución real para consultas complejas puede tomar un tiempo largo para ser generados, así que es mejor usar los planes de ejecución estimados para esas consultas complejas.
Los planes de ejecución pueden ser vistos como texto, XML o gráficamente. Use SET SHOWPLAN_TEXT ON para ver el plan de ejecución como texto, SET SHOWPLAN_XML ON para ver el plan de ejecución como XML. El plan de ejecución estimado gráfico puede ser generado directamente cuando usted hace clic en el botón de abajo en SQL Server Management Studio sin ejecutar la consulta:
Para incluir el plan de ejecución real, haga clic en el botón de abajo en SQL Server Management Studio antes de ejecutar su consulta.
Una manera alternativa de hacer eso es hacer clic derecho en la consulta y elegir incluir el plan real o mostrar el estimado como sigue:
Como puede ver en la imagen previa, usted puede simplemente usar el atajo de teclado Ctrl+L para mostrar el plan estimado y el atajo de teclado Ctrl+M para incluir el real. Usted debería obtener el permiso ShowPlan en la base de datos en que la consulta será probada para ver el plan de ejecución.
Usualmente, los planes de ejecución reales y estimados son similares, pero en algunos casos puede que note una diferencia entre ellos. La razón principal para esta diferencia es la diferencia entre las estadísticas de la base de datos y los datos reales. Este es un comportamiento normal que puede ocurrir cuando usted inserta y elimina de sus tablas e índices o cambia la tabla o el esquema del índice.
Puede que usted pregunte entonces, en tales casos, si la actualización automática de estadísticas está habilitada y funcionando. La respuesta para eso es que la manera en que la actualización automática de estadísticas funciona, y cómo toma un subconjunto de los datos como muestras para actualizar las antiguas estadísticas para reducir el costo del proceso de actualización. También, las estadísticas sólo serán actualizadas automáticamente si el 20% de las filas es cambiado, lo cual puede ser un problema para tablas muy grandes en que esto puede resultar en problemas de desempeño. Lo cual significa que las estadísticas diferirán de los datos reales con el tiempo, generando malos planes de ejecución que se apoyan en estas estadísticas imprecisas. Actualizar las estadísticas manualmente en momentos pico para evitar los problemas de desempeño mencionados, reducirá la diferencia entre los datos reales y las estadísticas.
Otras circunstancias en las cuales el plan de ejecución estimado difiere del plan de ejecución real es cuando el plan de ejecución estimado es inválido, donde el plan de ejecución no puede ser generado debido a un error. Un buen ejemplo de esto cuando la consulta contiene una sentencia SELECT … INTO de tablas temporales cuando la taba temporal o tabla variable no está creada aún, como en el siguiente ejemplo:
|
1 2 3 4 5 |
USE SQLShackDemo GO SELECT EmpID INTO #TempTable FROM [dbo].[Employees] SELECT * FROM #TempTable DROP TABLE #TempTable |
Un mensaje de error será mostrado indicando que no hay una tabla con ese nombre:
Y el resultado nos mostrará la diferencia entre los planes estimado y real como sigue:
Puede que también note una diferencia entre los planes estimado y real cuando el paralelismo es requerido o no para esa consulta. Dos planes de ejecución serán creados para la consulta enviada si el costo del plan llega al umbral de paralelismo, luego el Motor de SQL Server, dependiendo de la situación actual, elegirá usar o no el plan paralelo. Así que puede ocurrir que el plan real es el plan paralelo creado y el real usa e plan serial o viceversa.
Veamos el ejemplo de abajo y revisemos el plan estimado y real para esa consulta. Asumamos que necesitamos solucionar un problema en la consulta de abajo primero con el plan de ejecución estimado:
|
1 2 3 4 5 6 7 8 |
USE SQLShackDemo GO SELECT EM.EmpName , EM.EmpAddress ,EMD.DepName ,EMF.Emp_Salary from [dbo].[Employee_Financial] EMF inner join [dbo].[Employees] EM ON EM.EmpID =EMF.Emp_ID inner join [dbo].[Employee_Department] EMD ON EMD.DepID =EM.EmpDEPID |
Mostrando el plan de ejecución estimado:
Si incluimos en plan de ejecución real y corremos la consulta:
Los dos resultados idénticos, los cuales encontrará la mayor parte del tiempo, indican que las estadísticas en estas tablas consultadas están actualizadas. Estamos en seguridad esta vez y usted puede usar el plan de ejecución estimado o real para hacer su trabajo de solución de problemas. Pero para mí, prefiero usar el plan de ejecución real para solucionar problemas en las consultas, ya que siempre nos da un resultado preciso.
Conclusión
En este artículo mencionamos dos tipos de planes de ejecución: el plan de ejecución estimado que es generado por el Optimizador de Consultas y almacenado en la memoria de la caché de planes después de analizar gramáticamente la consulta sin la necesidad de ejecutar la consulta, y el plan de ejecución real que es generado después de ejecutar la consulta. Los planes de ejecución estimado y real deberían ser lo mismo, excepto en algunos casos donde las estadísticas estás desactualizadas, hay algunos cambios en el esquema realizados en la tabla subyacente, el plan de ejecución no puede ser generado debido a tablas temporales o variables que no han sido creadas aún o debido a paralelismo que puede o no ser usado cuando se ejecuta esa consulta.
Enlaces útiles
- • Mostrando Planes de Ejecución Gráficos (SQL Server Management Studio)
- Forzar un plan de ejecución de consultas usando SQL Server 2016 Query store
- Solucionar problemas de desempeño de consultas SQL usando Estadísticas en Vivo de Ejecución de SQL Server 2016
- Planes de ejecución de consultas SQL Server – Entendiendo y leyendo los planes
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Restricciones en SQL Server: SQL NOT NULL, UNIQUE y SQL PRIMARY KEY - December 16, 2019
- Operaciones de copia de seguridad, truncamiento y reducción de registros de transacciones de SQL Server - November 4, 2019
- Qué elegir al asignar valores a las variables de SQL Server: sentencias SET vs SELECT T-SQL - November 4, 2019