Planes de ejecución con SQL Server I

Introducción

A la hora de gestionar las bases de datos el DBA  siempre se plantea el mismo tipo de preguntas:

• ¿Por qué es esta consulta está funcionando tan lentamente?

• Está SQL Server utilizando el índice que he creado?

• ¿Por qué SQL Server no utiliza el índice que he creado?

• ¿Por qué esta consulta se ejecuta más rápido que esta otra?

• Y así sucesivamente.

La respuesta correcta probablemente sea diferente en cada caso, pero siempre hay que hacerse la misma pregunta: ¿He consultado el plan de ejecución?
Los planes de ejecución muestran lo que está pasando detrás de cada actuación de SQL Server. Esto proporciona una gran cantidad de información acerca de cómo SQL Server está ejecutando las consultas, incluyendo los siguientes puntos.

• ¿Qué índices se están utilizando, y donde hay índices se no se utilizan en absoluto?

• ¿Cómo se están recuperando y uniendo los datos a partir de las tablas definidas en la consulta?

• ¿Cómo se están gestionando las agregaciones GROUP BY en las consultas?

• La carga prevista del sistema, y el costo estimado en tiempo, de todas estas operaciones.

Toda esta información convierte el plan de ejecución en una herramienta bastante importante entre las herramientas que debe manejar el administrador de base de datos, los desarrolladores de bases de datos, los generadores de informes,  y cualquier persona que escriba sentencias de SQL para acceder a los datos en una base de datos SQL Server.
En esta serie de entradas se tratarán los siguientes puntos:

• Cómo capturar los planes de ejecución de forma gráfica, así como en formato texto y XML.

• Un método para la interpretación de los planes de ejecución, y la forma de crear estos planes por código.

• Saber cómo SQL Server representa e interpreta los objetos comunes de SQL Server como índices, vistas, tablas etc, en los planes de ejecución.

• Cómo detectar algunos problemas de rendimiento comunes, tales como las búsquedas en los índices no utilizados o saber dónde faltan.

•  Cómo controlar los planes de ejecución con consejos y guías del plan.

En definitiva, se abordará cómo optimizar las consultas SQL, o construir índices eficientes.

Conceptos básicos de plan de ejecución

Un plan de ejecución es el resultado del optimizador de consultas de SQL Server al calcular la forma más eficiente para implementar la solicitud representada por una consulta  SQL.
Los planes de ejecución indican cómo SQL Server ejecutará una consulta, o la forma en que ha ejecutado una consulta. Son, por lo tanto, el principal medio para detectar un bajo rendimiento de una consulta. En vez de tratar de adivinar por qué una determinada consulta se ejecuta realizando de miles de escaneos, del sistema de entradas y salidas o del disco duro, utilizando el plan de ejecución es posible identificar la línea exacta de código SQL que está causando el problema. Por ejemplo, la consulta puede estar leyendo una tabla entera  cuando con la cláusula WHERE, simplemente se recuperarán las filas que se necesitan. El plan de ejecución muestra todo esto y mucho más.

¿Qué ocurre cuando se ejecuta  una consulta?

Cuando se ejecuta una consulta con SQL Server, se ejecuta una serie de procesos en el servidor para trabajar sobre esa consulta. El objetivo de todos estos procesos es gestionar el sistema según se haya pedido un SELECT, INSERT, UPDATE o un DELETE.
Estos procesos ejecutan una acción cada vez que envía una consulta al sistema. Aunque hay muchas acciones diferentes que ocurren simultáneamente en SQL Server, nos centraremos en los procesos en torno a las consultas. Los procedimientos para el cumplimiento de los requisitos de consultas se descomponen más o menos en dos etapas:

1. Los procesos que se producen en el motor relacional.

2. Los procesos que se producen en el motor de almacenamiento.

En el motor relacional, la consulta se analiza y luego la procesa el optimizador de consultas, el cual genera un plan de ejecución. El plan se envía (en formato binario) para el almacenamiento, luego se utiliza ese plan como base para recuperar o modificar los datos subyacentes. Los motores de almacenamiento son los que ejecutan procesos tales como el bloqueo, el mantenimiento de índices, o las transacciones.  Como los planes de ejecución se crean en el motor relacional, es donde centráremos nuestra atención.

Análisis de la consulta

Cuando se pasa una consulta SQL al sistema de SQL Server, lo primero que hace el motor de SQL server es comprobar si la sentencia  está correctamente escrita y bien formada. Este proceso se llama análisis de la consulta. Si la consulta no se puede analizar correctamente, por ejemplo, si escribe SELETC en lugar de SELECT, devuelve un error.
El proceso de salida del analizador de consultas SQL es un análisis en árbol, o árbol de consulta (o árbol de secuencia). El análisis sintáctico en árbol representa los pasos lógicos necesarios para ejecutar la consulta solicitada.

Analizador o Algebrizador

Si la cadena de SQL es una instrucción de Lenguaje de Manipulación de Datos (DML) y se ha analizado correctamente, se pasa el árbol de análisis sintáctico a un proceso llamado analizador. El analizador resuelve todos los nombres de los distintos objetos, tablas y columnas, a los que se hace referencia dentro de la cadena de consulta. Identifica los tipos de datos varchar frente a los de fecha, hora, etc, a nivel de columna individual para los objetos a los que tiene acceso. También determina la ubicación de los agregados (tales como GROUP BY, y MAX) dentro de la consulta, un proceso llamado aggregate binding agregado de unión.

El análisis del proceso es importante porque la consulta puede tener alias o sinónimos, nombres que no existen en la base de datos, que deben resolverse, o la consulta puede referirse a objetos que no están en la base de datos. Cuando los objetos no existen en la base de datos, SQL Server devuelve un error de este paso, definiendo el nombre de objeto no válido.

El analizador genera como salida un árbol binario llamado árbol procesador de consultas, que se pasa al optimizador de consultas. El analizador incluye un hash, un valor codificado que representa la consulta. El optimizador utiliza el hash para determinar si hay ya un plan igual generado y almacenado en la caché. Si existe un plan, el proceso se detiene aquí y utiliza ese plan. Esto reduce la sobrecarga del optimizador de consultas al no tener que generar un nuevo plan.

El optimizador de consultas

El optimizador de consultas es un bloque de software que modeliza el funcionamiento del motor de base de datos relacional. La parte más importante de los datos utilizados por el optimizador son las estadísticas generadas por SQL Server y mantiene la relación de índices y columnas, de forma explícita para su uso por el optimizador. Utilizando el árbol de procesador de consultas y las estadísticas sobre los datos, el optimizador aplica un modelo con el fin de hacer  lo que cree que será la mejor manera de ejecutar la consulta. Es decir, genera un plan de ejecución.

El optimizador implementa la vía más rápida para ejecutar una consulta de SQL. Decide si se puede acceder a los datos a través de índices teniendo en cuenta los tipos de uniones a utilizar. Las decisiones tomadas por el optimizador se basan en costo calculado del plan de ejecución definido, en términos de las necesidades de procesamiento de la CPU y del sistema de entradas y salidas E / S. Por tanto, es un plan basado en costos.

El optimizador genera y evalúa muchos planes diferentes (a menos que ya exista una plan optimizado en la caché) y, en general, elige el plan de costo más bajo, es decir ejecutará la consulta tan rápido como sea posible y utilizará la menor cantidad de recursos, de CPU y E/S. El cálculo del coste de ejecución es el cálculo más importante, el optimizador utiliza un proceso muy intensivo en CPU y devuelve como resultado el plan más rápido. A veces, el optimizador elige uno  menos eficiente, pues le llevaría más tiempo de CPU evaluar muchos planes de ejecución lo cual en sí mismo sería un plan menos eficiente. Por tanto el optimizador no ejecuta el mejor plan posible. El optimizador considera el plan con el menor costo en el menor número de iteraciones, es decir busca un plan que le lleve a consumir la menor cantidad de tiempo de procesador.

Si se envía una consulta muy simple, por ejemplo, una instrucción SELECT en una sola tabla sin agregados o cálculos en la consulta, en lugar de perder el tiempo tratando de calcular el plan óptimo, el optimizador simplemente aplica un plan trivial para ese tipo de consulta. Por ejemplo, una consulta del tipo.

SELECT NombreCampo
FROM dbo.Tb_Nombre_Tabla
WHERE CampoIdentificador = 55

Si se añade una tabla más con un JOIN, el optimizador realizará un cálculo basado en los costes para seleccionar un nuevo plan. Para ello, se basará en las estadísticas de  SQL Server.
Las estadísticas se recogen en columnas e índices dentro de la base de datos, y describen la distribución de datos y su singularidad, o selectividad. No deseamos que el optimizador lea todos los datos de todas las tablas de referencia en cada consulta cada vez que intenta generar un plan, por lo que este se basa en estadísticas, para ello toma una muestra de los datos y genera una construcción matemática de los datos utilizados por el optimizador para representar a toda la colección de datos. La dependencia que el optimizador tiene de las estadísticas significa que estas deben ser lo más precisas posible o el optimizador podría hacer malas elecciones de los planes de ejecución que crea.

La información que compone las estadísticas se representa mediante un histograma, una tabulación de ocurrencias de un determinado valor, tomadas de 200 puntos de datos de manera uniformemente  distribuida a través de los datos. Estos "datos sobre los datos" proporcionan información necesaria para que el optimizador haga sus cálculos.
Si existen estadísticas para una columna o índice el optimizador las utilizará en sus cálculos. El optimizador examinará las estadísticas para determinar si el índice posee el nivel suficiente de selectividad para actuar como ayuda para la consulta en cuestión. La selectividad es la forma única y sin ambigüedad que describe una fila de datos a través de todo el conjunto. El nivel de selectividad requerida para que sea de ayuda para un índice es bastante alto, por lo general con un x% de valores únicos requeridos en la mayoría de los casos.

Las estadísticas se crean de forma predeterminada y se actualizan automáticamente para todos índices o para cualquier columna utilizada como  predicado, como parte de una cláusula WHERE o JOIN ON. Las variables de tabla no siempre tienen estadísticas generadas, por lo que el optimizador asumirá que contienen una sola fila, independientemente de su tamaño real.

Las tablas temporales tienen estadísticas generadas y almacenadas en el mismo tipo de histograma que las tablas permanentes, y el optimizador también puede  utilizar estas estadísticas. El optimizador toma estas estadísticas, junto con el árbol del procesador de consultas, y heurísticamente determina el mejor plan. Esto significa que funciona a través de una serie de planes, y  prueba diferentes métodos para acceder a los datos, intenta diferentes tipos de JOIN. Reordena y  prueba diferentes índices, y así sucesivamente, hasta que llega a lo que cree que será el plan de menor costo. Durante estos cálculos, el optimizador asigna un número a cada uno de los pasos dentro del plan, lo que representa su estimación de la cantidad combinada de CPU, disco  y recursos de E/S estimará el tiempo que le llevará cada acción y asignará este tiempo junto a este número es el costo estimado para ese paso. La acumulación de costos para cada paso es el costo estimado para el plan de ejecución en sí.
Es importante tener en cuenta que el costo estimado es sólo eso, una estimación. El optimizador debe encontrar el plan más adecuado y planificar cómo ejecutar la consulta. Sin embargo, intenta calcular el mejor plan que puede en la menor cantidad de tiempo posible, y está limitado por la calidad de las estadísticas de que dispone. Por tanto, estas estimaciones de costes son muy útiles como medidas, pero es improbable que reflejen realidad con precisión.

Una vez que el optimizador llega a un plan de ejecución, crea y almacena el plan estimado en la caché.

Ejecución de la consulta

Una vez que el optimizador ha generado un plan de ejecución, o recupera uno de la memoria caché, la acción pasa al motor de almacenamiento, que por lo general es quien ejecuta la consulta según el plan. El plan de ejecución  generado puede estar sujeto a cambios durante el proceso de ejecución real. Por ejemplo, si sucede esto:

• SQL Server determina que el plan excede el umbral para una ejecución en paralelo (Una ejecución que se aprovecha de múltiples procesadores en la máquina).

• Las estadísticas utilizadas para generar el plan están desactualizadas o han cambiado desde que fue creado el último plan de ejecución.

• Procesos u objetos dentro de la consulta, tales como inserciones de datos a una tabla temporal, resultan en una recompilación del plan de ejecución.

Cualquiera de ellos puede cambiar el plan de ejecución estimado.
SQL Server devuelve los resultados de la consulta y después el motor relacional cambia el formato para que coincida con la solicitada en la cuenta de SQL presentada, suponiendo que fuera una SELECT.