En ocasiones recibimos quejas acerca de procesos que en un primer momento tardaban poco en ejecutarse y pasado un tiempo se vuelven mucho más pesados. Para solucionar este tipo de problemas tendremos que buscar por diferentes caminos el origen del error:
Errores en las personalizaciones, recursos insuficientes en el servidor, bloqueos, falta de índices…
En esta ocasión vamos a hablar de los planes de ejecución de SQL Server y cómo estos nos pueden ayudar a resolver algunos de estos problemas.
¿Qué es un plan de ejecución de SQL Server?
Un plan de ejecución de SQL Server es el conjunto de pasos ejecutados en base de datos para obtener el resultado de una consulta. Nos aportan información de las tablas afectadas, los índices utilizados, estadísticas, tipos de combinaciones, número de filas afectadas, clasificación de datos y recuperación de los mismos.
Tenemos varios tipos de visualización de planes de ejecución: Gráficos, XML y de texto. En este artículo nos centraremos en los de tipo gráfico. En ellos, las tareas realizadas por la base de datos están representadas por operadores que pueden ser:
Lógicos: Describen conceptualmente la operación que se debe realizar.
Físicos: Realizan las operaciones descritas por los operadores lógicos.
Antes de explicar cómo hacer uso de los planes de ejecución, es necesario saber cuáles son los operadores que podemos encontrar en ellos.
Algunos de los operadores más comunes son:
¿Cómo podemos ver los planes de ejecución en SQL Server?
Iniciamos SQL Server Management Studio. En la barra de herramientas encontraremos los siguientes iconos.
De izquierda a derecha son:
- Mostrar plan de ejecución estimado.
- Mostrar plan de ejecución actual.
- Mostrar estadísticas de la consulta en tiempo real.
- Nos aporta información de cómo va evolucionando la consulta en tiempo real: Tiempos, número de filas recorridas…
- Mostrar estadísticas del cliente.
- Nos muestra una vista de las ejecuciones realizadas de una consulta desde SQL, en la que podemos comparar algunas métricas.
¿Cómo leer un plan de ejecución?
Generalmente los planes de ejecución se leen de derecha a izquierda y de la parte superior a la inferior. Empezamos con el operador del plan situado en la parte superior derecha y a partir de ahí nos movemos hacia abajo y hacia la izquierda. De esta forma podemos seguir el flujo de datos en la consulta.
Si lo que queremos es hacer un seguimiento de las órdenes físicas en SQL, debemos leer el plan de ejecución de izquierda a derecha, donde cada operador solicitará los datos del siguiente.
Debajo de cada operador nos aparecerá el coste relativo al resto de operadores. Al tratarse de una herramienta de optimización de consultas, generalmente nos centraremos en el operador más costoso y trataremos de mejorar la consulta en torno a él.
Ejemplo práctico
Una vez explicados conceptos básicos de los planes de ejecución, seguiremos con un ejemplo práctico donde veremos cómo afectan las modificaciones sobre la consulta a los planes de ejecución seguidos y al rendimiento de los procesos.
Empezamos ejecutando el siguiente job en la aplicación:
Iniciamos SQL Server Management Studio y abrimos el “Monitor de actividad” de nuestra instancia de base de datos.
Podemos localizar el proceso ejecutado entre las consultas pesadas que se han ejecutado recientemente.
Llegados a este punto, haciendo clic derecho sobre nuestra consulta tendremos dos opciones:
- Editar texto de la consulta.
- Mostrar plan de ejecución.
Aunque es importante revisar la consulta que se ha ejecutado, en este caso iremos directamente al plan de ejecución.
Si nos posicionamos sobre cualquiera de los operadores, se abrirá un cuadro de información relacionada: Coste del operador, índice utilizado, número de registros recorridos, número de ejecuciones…
Nuestro proceso ejecuta una búsqueda por los “DataAreaId”, “TransDate”, “Voucher” y “DocumentNum”, por lo que, en caso de haber algún problema de rendimiento, podríamos pensar que ese índice no es del todo óptimo.
En este ejemplo probaremos con la ejecución del mismo proceso después de crear un nuevo índice en base de datos, para ver si esto afecta (para bien o para mal) al plan de ejecución seguido por la consulta. Para ello ejecutamos el siguiente comando:
Si vemos la tabla “CustTrans” en SQL server veremos que contiene nuestro índice:
Ejecutamos nuevamente el job y comprobamos si el plan de ejecución seguido en SQL tiene alguna variación.
Se puede observar que el plan de ejecución difiere del plan inicial, tanto en forma como en los operadores y costes relativos.
Mientras en el primero empieza filtrando toda la información necesaria de la CustTrans, en el segundo hace la selección de los datos relativos a la CustTrans con la GeneralJournalEntry utilizando el índice que acabamos de crear. Esto no quiere decir que uno sea mejor que el otro, para saberlo, deberemos analizar algunos indicadores como por ejemplo los tiempos de ejecución o el número de llamadas realizadas.
Si copiamos la consulta y la ejecutamos varias veces antes y después de añadir el índice, mostrando las estadísticas del cliente, vemos lo siguiente:
Antes de añadir el índice
Después de añadir el índice
Se aprecia que incluyendo el índice disminuye un poco el tiempo de procesado, pero no es nada significativo, por lo que no podemos afirmar que este cambio de plan no mejoraría el rendimiento del proceso.
En resumen, cuando realizamos consultas pesadas podemos revisar los planes de ejecución para ver cómo se lleva a cabo la consulta en SQL Server y a partir de ahí intentar optimizarla.
