jump to navigation

msSQL – ¿Como SQL Server determina un plan de Ejecución usando los Indices disponibles y las Estadísticas?

El objetivo de este trabajo práctico es aplicar los conocimientos volcados en el artículo “How SQL Server Determines an Execution Plan Using Available Indexes and Statistics” por Nils Bevaart.

Es decir que veremos Cómo SQL Server determina un plan de la ejecución usando índices y estadística disponibles. Trabajaremos sobre la base Northwind que viene por default con la instalación de SQL Server 2000.

Primero veamos, ¿Qué es el plan de ejecución de SQL Server?

Cada vez que se ejecuta una consulta en un motor de bases de datos cualquiera, internamente se ejecutan una serie de operaciones, que varían según la consulta, los datos y obviamente, el motor de base de datos. El conjunto de pasos que tiene que realizar el motor para ejecutar la consulta, se llama Plan de Ejecución.

En el plan de ejecución de SQL Server 2000 podemos encontrar las siguientes operaciones:

Operación

 

Descripción

 

Solución

 

Table Scan

 

El motor tiene que leer toda la tabla. No se esta usando ningún tipo de índice.

En algunos casos, cuando es una tabla chica, un Table Scan es la mejor opción, ya que produce poco overhead. De hecho la tabla puede tener índices y sin embargo el SQL elige usar un table scan porque seria más rápido.

Ver si la tabla tiene índices y si se están usando correctamente.

Clustered Index Scan

 

El motor recorre toda la tabla.

Se realiza en una tabla que tiene un índice Clustered.

 

Clustered Index Seek

 

Significa que el motor esta usando efectivamente el índice Clustered de la tabla.

 

Index Seek

 

Es similar que el Clustered Index Seek, pero con la diferencia de que se usa un indice Non Clustered.

 

Bookmark Lookup

 

Indica que SQL Server necesitaba ejecutar un salto del puntero desde la página de índice a la página de datos de la tabla para recuperar los datos. Esto sucede siempre que tenemos un índice Non Clustered.

Hay que limitar los campos que queremos traer en la consulta. Si el campo que vamos a extraer, esta fuera del índice, entonces se va a ejecutar esta operación.

Index Scan

 

Se lee el índice completo de una tabla. Es preferible a un Table Scan. Esta operación puede ser síntoma de un mal uso del índice. Aunque también puede ser que el motor haya seleccionado que esta es la mejor operación. Es muy común un Index Scan en un join o en un ORDER BY o GROUP BY.

 

Neested Loop Join

 

Esta operación ocurre cuando tenemos un join y la cantidad de registros es relativamente baja, o los campos a relacionar están indexados.

 

Merge Join

 

Se usa cuando la cantidad de registros a comparar son grandes y están ordenados. Aun si no están ordenadas, el motor puede predecir que es mas rápido ordenar la tabla.

 

Hash Join

 

Se usa generalmente cuando las tablas relacionadas no tienen índice en ninguna de los campos a comparar.

 

Sort 

 

Esta operación ordena. Solo se hace cuando el campo o los campos que se desean ordenar, no están indexados.

 

 Cuando el SQL Server ejecuta una Query, utilizará un plan de la ejecución si ya existe uno. Si aun no hay un plan, el SQL Server sigue una serie de pasos para crear un plan. El objetivo de esto es encontrar un plan aceptable, no necesariamente el plan óptimo.

Las siguientes medidas son tomadas por el optimizador de consulta. Cada paso puede producir unos o más planes. Cada plan asigna un coste total para la ejecución, y el SQL Server utiliza el plan con los costos más bajos

Estadística 

Este proceso se basa solamente en estadística. La mala estadística conduce a los malos planes de la ejecución. Si un índice se pone en una tabla pequeña, el plan óptimo pudo ser no hacer uso del índice y utilizar una exploración de la tabla. Si la estadística no es actualizada mientras que la tabla crece perceptiblemente, el SQL Server inmóvil asumirá que la tabla es pequeña y utilizar la exploración de la tabla, aunque esto no sea el plan más óptimo.

En la mayoría de los casos es recomendable dejar que el SQL Server automáticamente ponga al día la estadística.

Se ejecutará la siguiente sentencia para establecer las actualizaciones como automáticas:

USE master

EXEC sp_dboption ‘Northwind’, ‘auto update statistics’, ‘true’

Para setear las actualizaciones automáticas de estadística sobre una tabla específica, tal como Products, ejecutaremos:

USE Northwind

EXEC sp_autostats products, ‘ON’

Para actualizar manualmente la estadística sobre la tabla específica, tal como Products, ejecutaremos:

USE Northwind

UPDATE STATISTICS products

Cómo trabaja la estadística
Mantener estadística actualizada es crucial para el funcionamiento óptimo de una base de datos. En algunos casos, sin embargo, un plan de ejecución disponible no es ideal para la situación dada. Para entender cuando y porqué sucede esto, es importante entender cómo trabaja la estadística.

El comando siguiente exhibe la estadística para un índice dado.

USE Northwind

DBCC SHOW_STATISTICS (Products, SupplierID)

Esto da toda la información estadística sobre este índice. Esta muestra se toma al azar de la base de datos y da la información siguiente:

Haga Click para AMPLIAR la imagen

Imagen 1: Salida de DBCC SHOW_STATISTICS

Descripción de las columnas resultantes:

Haga Click para AMPLIAR la imagen

Imagen 2: Plan estimado de la ejecución, de la selección de los productos cuyo proveedor (Supplier) es 4.

En base de esta estimación, los pasos siguientes en un plan de la ejecución serán determinados.

Si un valor de la búsqueda se empareja en la tabla de la estadística, el valor para el EQ_ROWS será la cuenta estimada de la fila.

Haga Click para AMPLIAR la imagen

Cuadro 3: Plan de la ejecución – búsqueda para productor 7 y categoría 1

Trabajo

¿Cómo solucionamos este problema? Las estadísticas no se pueden ampliar. Están diseñas para ser pequeñas, entonces el proceso para determinar un plan de ejecución se mantiene pequeño. Aunque la estadística pudiese tener el doble de tamaño, este problema seguirá.

La opción de usar un índice solo será provechosa si se usa en todos los Querys con un parámetro de búsqueda para ese campo. La inclusión de un índice es un trabajo manual, requiere conocimiento del set de datos. Un humano encontrará las mismas dificultades que el SQL Server para determinar el mejor uso de índices. También, la automatización del uso de índices en un ambiente de producción consume mucho tiempo y está lejos de ser eficiente.

En estas situaciones, la realización de un trabajo adicional es la mejor opción. Cambiando el índice específico en un índice cluster, no se necesita más una búsqueda de bookmark. Esto ahorra un tiempo considerable. Si las estadísticas están apagadas y el numero de aciertos excede el numero esperado todos los registros relevantes se agrupan en un cluster y este requiere un poco mas de tiempo.

En los ejemplos mencionados anteriormente, si el índice en Productid es un índice cluster, el plan de la ejecución está como sigue:

Haga Click para AMPLIAR la imagen

Imagen 4: Plan de la ejecución – búsqueda para productor 7 y categoría 1 con índice cluster.

Planeamiento de índices

El efecto que causa cambiar los índices cluster que existen ahora en un índice no-cluster se debe examinar. Solo puede haber un índice cluster en una tabla. La diferencia en funcionamiento entre un índice cluster y uno no cluster para un campo que contiene valores únicos es generalmente mínima. Si, sin embargo, este campo se utiliza en relaciones de uno a n, el funcionamiento general las Querys de toda la base puede declinar.

Es importante tener en cuenta todas estas consideraciones cuando se diseña una estructura de índices para la base de datos. Por lo general es mayor el esfuerzo de probar diferentes configuraciones y dar con la más acertada. Por supuesto el Tuning de índices ayudará en este proceso. 

A %d blogueros les gusta esto: