Hola a todos, hace algún tiempo hicimos un post con algunos tips para optimizar consultas SQL. Todos estos consejos son ayudan al optimizador de consultas de SQL para que no se produzcan cambios en los planes de ejecución de las consultas que se lanzan contra la BBDD. Pero, ¿cómo podemos comprobar que dicho plan de ejecución no cambia con el tiempo?. Vamos a ver cómo se puede ver en el Management Studio el plan de ejecución de SQL Server.
Planes de ejecución
Un plan de ejecución es el resultado del intento del optimizador de consultas para calcular el camino más eficiente a la hora de gestionar una petición realizada por una query T-SQL.
El plan de ejecución muestra como SQL Server puede ejecutar la query paso a paso, con lo que se puede utilizar para identificar exactamente en qué paso del código de la query se está causando el problema para un óptimo rendimiento a la hora de devolver las filas pedidas.
En SQL Server encontramos dos tipos de plan de ejecución:
- Plan de ejecución estimado. Los pasos del plan de ejecución representan el punto de vista del optimizador sobre lo que puede ocurrir pero no representa lo que ocurre físicamente cuando se ejecuta la query.
- Plan de ejecución actual. En este tipo de plan se evalúan los pasos del plan de ejecución en el momento de ejecutar la query, con lo que si que representa lo que está ocurriendo en el momento de ejecución de la query.
Estos dos tipos de planes pueden representar distintos sets de datos, pero en la mayoría de ocasiones se evalúan los mismos operadores y cantidades de datos, con lo que normalmente pueden devolver el mismo plan de ejecución óptimo para cada una de las querys ejecutadas.
Reutilización del plan de ejecución
Es muy costoso para el servidor tener que calcular el plan de ejecución para todas las querys que se ejecutan en un momento determinado. Por este motivo, se mantienen y reutilizan los planes de ejecución siempre que sea posible, ahorrando tiempo de computo. Los planes de ejecución se almacenan en una sección de la memoria llamada plan cache (procedure cache antes de SQL 2005)
Existe un proceso interno del motor de SQL, llamado Lazywriter, cuyo cometido es liberar los recursos utilizados por todos los procesos de caché, incluido el plan cache. Realiza un escaneo periódico de los objetos en la cache, y decrece el contador de tiempo del objeto en la cache.
El plan de ejecución se quita del plan cache si ocurre uno de los siguientes eventos:
- Se necesita más memoria en el sistema.
- El contador de tiempo del plan llega a 0.
- El plan no está siendo referenciado por ninguna conexión existente.
Además existen algunas acciones que fuerzan también a volver a evaluar el plan de ejecución. Algunas de ellas las comentamos en un post con algunos tips para optimizar consultas SQL, pero hay alguna acción más:
- Cambiar la estructura de una tabla referenciada por la query
- Cambiar o borrar un índice usado por la query.
- Actualizar las estadísticas usadas por la query.
Formato de los planes de ejecución
En SQL Server se pueden visualizar los planes de ejecución de tres formas distintas:
- Entorno gráfico
- En formato texto
- En formato XML
El entorno gráfico es el más sencillo e intuitivo para leer el plan de ejecución de la query. Para poder ver los planes de ejecución hay que tener asigando un permiso especial.
GRANT SHOWPLAN TO <username>;Plan de ejecución modo gráfico
Para ver el plan de ejecución en el entorno gráfico hay que pulsar en el botón de la imagen en el Management Studio
Pulsando en este botón aparece el plan de ejecución en la parte inferior de la pantalla donde se muestra el resultado
También se puede ver la query completa que se está evaluando. Poniendo el ratón sobre cada uno de los iconos del plan de ejecución resultante se activan unos popups llamados ToolTips, con información añadida del objeto evaluado.
Primero se ve la parte del operador SELECT, con alguna información interesante:
- Cached Plan Size. Memoria ocupada por el plan de ejecución
- Estimated Operator Cost. Coste de la operación en porcentaje
- Estimated Subtree Cost. Coste de todos los pasos hasta el momento, incluido el actual
- Estimated Number of Rows. Cálculo de las filas según las estadísticas de la tabla o índice.
También se genera para el siguiente operador, en este caso es un «Table Scan», y ofrece la misma información que la vista anteriormente y también ofrece información nueva:
- Physical y Logical Operation. Operación que se evalúa. Normalmente suelen coincidir.
- I/O y CPU Cost. Coste de CPU y Entrada/Salida de la operación.
- Estimated Number Of Executions. Número de veces que se ejecuta la operación
Esperamos que esta aproximación a los planes de ejecución os haya resultado de utilidad. No dudéis en poneros en contacto con nosotros en caso de necesitar ayuda en la gestión de vuestras bases de datos. Échale un vistazo a nuestros servicios de soporte y mantenimiento SQL Server y Oracle.
¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.
Sígue a GPS en LinkedIn