Rendimiento

Ene

2024

Planes de Ejecución en SQL Server

Hoy vamos a hablar de los planes de ejecución en SQL Server, uno de los temas más amplios y a la vez imprescindibles para entender el rendimiento de nuestras consultas. Entender los planes de ejecución es básico para analizar el rendimiento de las consultas y optimizarlas. Empecemos por el principio.

¿Qué son los planes de ejecución?

Los planes de ejecución son una representación gráfica o textual de cómo SQL Server procesa una consulta. Cada consulta se divide en una serie de operadores lógicos y físicos que realizan diferentes tareas, como leer datos de una tabla, filtrar filas, ordenar resultados, etc. Además, los planes de ejecución nos muestran el orden y el coste de cada operador, así como las estadísticas de las filas procesadas y el uso de memoria y CPU.

¿Para qué sirven los planes de ejecución?

Los planes de ejecución nos ayudan a entender cómo SQL Server ejecuta una consulta y a identificar posibles problemas de rendimiento. Por ejemplo, podemos ver si una consulta usa índices adecuados, si hay operadores costosos o ineficientes, si hay estimaciones erróneas de cardinalidad, etc. Con esta información, podemos aplicar técnicas de optimización, como crear o modificar índices, reescribir la consulta, actualizar las estadísticas, etc.

¿Cómo se obtienen los planes de ejecución?

Hay varias formas de obtener los planes de ejecución en SQL Server. Una de las más sencillas es usar el Management Studio y activar la opción «Incluir plan de ejecución actual» o «Incluir plan de ejecución estimado» en el menú «Consulta». Esto nos mostrará el plan de ejecución junto con los resultados de la consulta. Otra forma es usar la instrucción SET SHOWPLAN_ON o SET SHOWPLAN_TEXT para obtener el plan de ejecución en formato textual. También podemos usar la función sys.dm_exec_query_plan para obtener el plan de ejecución en formato XML a partir del identificador de una consulta. Después, podemos usar esta web para ver gráficamente nuestros planes en XML.

¿Qué tipos de planes de ejecución hay?

Hay dos tipos principales de planes de ejecución: estimados y actuales. Los planes estimados se generan antes de ejecutar la consulta y se basan en las estadísticas disponibles y en el optimizador de consultas. Los planes actuales se generan después de ejecutar la consulta y reflejan lo que realmente ha ocurrido durante la ejecución. Nuestros planes estimados y actuales pueden ser diferentes si hay cambios en los datos, en las estadísticas o en el entorno. Por eso, es recomendable comparar ambos tipos de planes para detectar discrepancias y ajustar la consulta o las estadísticas si es necesario.

¿Qué elementos componen un plan de ejecución?

Un plan de ejecución se compone de varios elementos que representan los operadores lógicos y físicos que intervienen en la consulta. Cada elemento tiene un icono, un nombre y unas propiedades que describen su función y su coste.

Algunos elementos comunes son:
– Scan: lee todas las filas o páginas de una tabla o un índice.
– Seek: busca una fila o un rango de filas en un índice usando una clave.
– Join: combina filas de dos orígenes usando un criterio de unión. Hay varios tipos de join, como nested loops, merge o hash.
– Aggregate: agrupa filas por una o más columnas y calcula funciones agregadas, como sum, count o avg.
– Sort: ordena filas por una o más columnas.
– Filter: elimina filas que no cumplen una condición.
– Compute Scalar: calcula una expresión escalar, como una columna calculada o una función.
– Stream Aggregate: agrupa filas por una o más columnas y calcula funciones agregadas usando un algoritmo eficiente que requiere que las filas estén ordenadas previamente.
– Hash Match: realiza operaciones como join, aggregate o union usando una estructura hash para almacenar y buscar las filas.
– Spool: almacena temporalmente las filas en el disco para reutilizarlas más adelante.
– Top: devuelve un número fijo o porcentual de filas ordenadas por una o más columnas.

Estos son solo algunos ejemplos, sin embargo, hay muchos más elementos que pueden aparecer en un plan de ejecución. Lo importante es entender qué hace cada elemento y cómo afecta al rendimiento de la consulta. Puedes encontrar la lista completa de operadores en la documentación oficial.

¿Cómo se interpreta un plan de ejecución?

Para interpretar un plan de ejecución hay que tener en cuenta varios aspectos, como el orden, el coste, las estadísticas y las advertencias.
El orden indica la secuencia en la que se ejecutan los operadores. En un plan de ejecución gráfico, el orden se lee de derecha a izquierda y de arriba a abajo.
Por otro lado, el coste indica el porcentaje de recursos que consume cada operador respecto al total de la consulta y se puede ver en la barra de colores que acompaña a cada elemento. El coste es una estimación basada en las estadísticas y puede no reflejar el coste real.
Las estadísticas muestran información sobre las filas procesadas, el uso de memoria y CPU, el tiempo de ejecución, etc y se pueden ver en las propiedades de cada elemento o en el panel de detalles. Las estadísticas son datos reales que se obtienen al ejecutar la consulta.
Por último, las advertencias son mensajes que indican posibles problemas de rendimiento o de calidad de los datos.

Advertencias

Las advertencias se pueden ver como símbolos de exclamación o de información en los elementos del plan. Como hemos visto, es importante que les prestemos atención ya que nos indican problemas en nuestras consultas.

Algunas advertencias comunes son:
– Missing Index: indica que la consulta podría beneficiarse de un índice que no existe.
– Sort Warnings: indica que la operación de ordenación ha usado más memoria de la disponible y ha tenido que escribir datos en el disco.
– Hash Warnings: indica que la operación hash ha usado más memoria de la disponible y ha tenido que escribir datos en el disco.
– Plan Affecting Convert: indica que hay una conversión implícita o explícita de tipos de datos que puede afectar al rendimiento o a la precisión de la consulta.
– SpillToTempDb: indica que una operación ha usado más memoria de la estimada y ha tenido que escribir datos en el disco.

Estos son solo algunos ejemplos pero, hay muchas más advertencias que pueden aparecer en un plan de ejecución. Lo importante es prestar atención a las advertencias y corregir los problemas si es posible.

Conclusión

Los planes de ejecución son una herramienta imprescindible para cualquier DBA que quiera optimizar el rendimiento de las consultas en SQL Server. Los planes de ejecución nos permiten ver cómo SQL Server procesa una consulta y qué recursos consume cada operador. Con esta información, podemos identificar y resolver los problemas más comunes, como el uso inadecuado o la falta de índices, las estimaciones erróneas de cardinalidad, los operadores costosos o ineficientes, etc. Espero que este artículo os haya servido para introduciros en el mundo de los planes de ejecución y os anime a usarlos en vuestro trabajo diario.

Si queréis profundizar más en este tema, os recomiendo los siguientes recursos:
– Los libros SQL Server Execution Plans y SQL Server Query Performance Tuning de Grant Fritchey
– Los post de Paul White en SQLPerformance.com
– Los cursos de Brent Ozar

Nov

2023

VISTAS INDEXADAS DE SQL SERVER

Las vistas son tablas virtuales que nos devuelven datos de una consulta sobre una o varias tablas. Igual que una tabla tiene sus columnas y sus filas pero al contrario que estas ese conjunto de datos como tal no está almacenado en ningún sitio. Cada tabla tiene sus datos y la vista los lee de ahí. Entonces, ¿los resultados de datos de las vistas no se guardan en ningún sitio? En realidad hay algunos tipos de vistas que sí guardan los datos finales como es el caso de las vistas materializadas que implementan algunos gestores de bases de datos (Oracle, por ejemplo). En el caso de SQL Server este tipo de vista no está disponible, aunque no podemos descartar que se implemente en un futuro (en Azure Synapse Analytics han existido desde siempre).

¿Qué alternativa tenemos en SQL Server?

Como hemos visto antes, las vistas las vemos como si fueran una tabla pero en realidad no tienen datos, los recuperan dinámicamente de las tablas a las que se referencia. Esto es muy útil en la mayoría de los casos ya sea por simplicidad de las consultas, para abstraernos de la estructura original y simplificar la estructura saliente o para gestionar permisos. Sin embargo el uso de una vista no afecta en nada al rendimiento pues ejecuta la consulta tal cual como si la escribiéramos a mano. Si una vista lee sobre varias tablas grandes generará mucho consumo de E/S y si la definición de la vista incluye procesamientos complejos y muchas uniones entre tablas tendremos una notoria degradación de rendimiento.

Como alternativa, en SQL Server podemos hacer uso de un tipo de vista especial que son las Vistas Indexadas (o indizadas), son vistas que tienen un índice clustered por lo que si van a almacenar el conjunto de resultados en la base de datos para poder hacer una lectura plana.

Dónde usar las vistas indexadas

Las vistas indexadas ofrecen un rendimiento de lecturas mejorado al hacer uso del índice para leer los datos y no tener que ejecutar la consulta subyacente. Sin embargo, nos pueden llegar a penalizar considerablemente las operaciones de escritura al añadir no sólo otro índice donde escribir los datos sino complejidad a ese índice. Adicionalmente podremos crear tantos índices nonclustered sobre la vista como necesitemos. Al igual que ocurre con los índices de las tablas en nuestra mano queda medir el coste/beneficio y valorar su idoneidad.

Con las vistas indexadas notaremos una mejora sustancial en el rendimiento de nuestras consultas si las aplicamos sobre entornos con una gran diferencia de lecturas frente a escrituras, se me viene a la cabeza sobre todo entornos Data Warehouse, bases de datos OLAP o entornos de minería de datos. Rara vez serán recomendables en entornos con alta carga de transacciones IUD (insert, update y delete) en bases de datos OLTP.

Limitaciones de las vistas indexadas

Como ya hemos visto uno de los inconvenientes de las vistas indexadas es que la degradación en rendimiento de las escrituras es mayor que la mejora en las lecturas, esto nos limita en gran medida su uso. Sin embargo no es su única limitación, la verdad es que tienen una amplia lista de incompatibilidades y requisitos. Uno de los principales requisitos de las vistas indexadas es que hay que crearlas con la opción SCHEMABINDING esto implica que no se podrán modificar las tablas referenciadas.

Consideraciones para crear vistas indexadas

Además de tener que crear la vista como SCHEMABINDING tenemos que tener en cuenta otros aspectos importantes:

Las vistas indexadas no admiten expresiones no deterministas. Es decir, las expresiones de la vista siempre deben devolver el mismo resultado no como GETDATE() que nos devolvería un valor distinto en cada ejecución.
Las tablas y funciones dentro de la vista deben declararse con el nombre completo (esquema.tabla).
No se admiten subconsultas.
No se admiten OUTER JOINS, esto deja fuera RIGHT JOIN y LEFT JOIN.
El índice clustered de nuestra vista ocupará espacio en disco.
Solo se puede hacer referencia a tablas de la misma base de datos.
Si tenemos GROUP BY, la definición de la vista debe contener COUNT_BIG(*), pero no HAVING.
No se puede usar EXISTS, NOT EXISTS, COUNT(*), MIN, MAX, hints de tablas, TOP ni UNION.
No puede utilizar los tipos de datos text, ntext , image o XML. El tipo de datos float se puede utilizar en la vista, pero no en el índice agrupado

Como crear vistas indexadas

Para crear una vista indexada lo primero que haremos será crear la vista con la opción SCHEMABINDING

CREATE VIEW VI_Facturas_Ventas
WITH SCHEMABINDING  AS
SELECT FC.cliente, FD.Articulo, COUNT_BIG(*)
FROM Ventas.FacturasCabeceras FC
INNER JOIN Ventas.FacturasDetalle FD
ON FC.NumFactura = FD.NumFactura
GROUP BY FC.cliente, FD.Articulo

Una vez que tenemos creada la vista tendremos que crear el índice clustered.

CREATE UNIQUE CLUSTERED INDEX CI_Facturas_Ventas ON VI_Facturas_Ventas (Cliente, Articulo);

En este punto el optimizador de consultas podrá usar nuestro índice para cualquier consulta sobre la tabla, incluso para consultas sobre las tablas sin que se use la vista aunque, esto último, sólo en ediciones Enterprise de SQL Server.

Conclusiones

Un gran poder conlleva una gran responsabilidad y las vistas indexadas son una increíble herramienta en entornos o tablas con gran cantidad de lectura y pocas modificaciones pero si no es así nos pueden hacer mucho daño al servidor. El uso de vistas indexadas de SQL Server puede ser una buena técnica para mejorar el rendimiento de las consultas al reducir el costo de E/S y la duración de las consultas, pero requiere pruebas, planificación y un estudio pormenorizado de donde usar vistas indexadas. Se debe realizar un análisis completo del impacto en el rendimiento, midiendo las mejoras en el rendimiento de lecturas frente al coste en las escrituras.