SQL Server, ideas y experiencias

Materiales-Índices y rendimiento (Performance) en el SQL Server

por Jose Mariano Alvarez 3. noviembre 2009

El objetivo fue aprender como el SQL Server almacena los datos y como utiliza los índices para poder diseñarlos y usarlos eficientemente. El objetivo final fue que los asistentes puedan tener el conocimiento necesario para optimizar mediante índices el uso que hace una aplicación de los recursos disponibles en el SQL Server.

Los siguientes enlaces tienen el contenido de los materiales mostrados en el curso dictado en el MUG y que no están incluidos en el CD con materiales que han recibido.

Índices y rendimiento (Performance) en el SQL Server.pdf (1,76 mb)

DemosIndicesOct2009.zip (25,81 kb)

Tags: , , , , ,

Eventos y conferencias

Buenas prácticas en el uso de índices - La selectividad

por Jose Mariano Alvarez 16. mayo 2009

Introducción

Los índices mejoran el rendimiento de las consultas que seleccionan un pequeño porcentaje de las filas de una tabla. La selectividad de los índices es uno de los conceptos más importantes a la hora de entender si un índice puede ser útil en esos casos. Para comprender el concepto, vamos a analizar las consecuencias de la selectividad de un índice no agrupado (NON CLUSTERED) sobre una columna de una tabla de la base de datos de ejemplo AdventureWorks que tiene el SQL Server 2008. Los accesos puntuales, que es lo que vamos a analizar en este caso, no son la única utilidad que nos ofrecen los índices para mejorar los costos, pero es importante comprender por qué en ocasiones no se utilizan índices existentes.

La motivación

Uno de las características fundamentales del SQL Server 2008 (y versiones anteriores también) es que la selección de las estrategias de ejecución del motor relacional se encuentran basadas en los costos. De esta manera cobra vital importancia la creación de índices para reducir estos costos.

Cuando creamos índices una de las posibles utilidades que ofrecen es reducir la carga impuesta a la base de datos permitiendo accesos puntuales a los registros a partir del índice lo que habitualmente se conoce como INDEX SEEK, para luego acceder en un segundo paso a los registros de la tabla. Si no existe el índice, en lugar de que el motor de la base de datos pueda acceder puntualmente, tiene que explorar todos los registros de la tabla o índice agrupado, lo que se conoce como TABLE SCAN o CLUSTER INDEX SCAN.

Ambas estrategias de acceso tienen sus costos. Por lo tanto la selección que hace el motor de la base de datos deberá estar de acuerdo a los costos. Además, cada una de las estrategias tiene sus consecuencias.

Si tenemos índices que no se utilizan simplemente estaríamos aumentando el trabajo que tiene que realizar el SQL Server para mantener actualizados los índices cuando se realizan modificaciones y estaríamos aumentando el espacio de almacenamiento usado por guardar las estructuras de los índices tanto en memoria como en disco. Por lo tanto es importante entender algunos conceptos importantes para crear buenos índices.

La selectividad

Vamos a definir la selectividad de un índice, como el porcentaje promedio de filas en una tabla que tienen el mismo valor en las columnas involucradas en el índice, esto equivale al cociente (ratio) entre el número de valores distintos en las columnas que definen el índice y el número de registros en la tabla. La selectividad ideal es 1 y representa el caso en que todas las entradas del índice son únicas. Esta selectividad puede ser garantizada sólo por índices únicos. SQL Server crea automáticamente índices en las columnas de todas las restricciones de integridad de clave primaria (PRIMARY KEY) y únicas (UNIQUE) que de definen. Esto ocurre porque generalmente estos índices son los más selectivos y más eficientes para optimizar el rendimiento ya que permiten seleccionar al motor de la base de datos estrategias particulares.

Ecuacion densidad

Quizá sea más simple comprender el inverso multiplicativo de la selectividad que representa el promedio de filas de igual valor que se van a obtener en el caso de que exista un condición por igualdad en la clausula WHERE de la sentencia.

ecuacion promedio de valores iguales

Veamos un simple ejemplo en la base de datos AdventureWorks de nuestro SQL Server 2008.

USE AdventureWorks

GO

SELECT COUNT (DISTINCT ProductID) FROM Sales.SalesOrderDetail

266

SELECT COUNT(*) FROM Sales.SalesOrderDetail

121317

Por lo tanto vemos que la selectividad de nuestro índice por la columna ProductID es 266/121317 0.002192602 lo que representa una selectividad regular ya que nuestro ideal es 1. Si vemos el inverso multiplicativo, lo que equivale a hacer121317/266 obtenemos 456.07 que representa el número promedio de registros cuando buscamos las filas de un producto en nuestra tabla de detalle de órdenes.

Veamos las consecuencias a la hora de realizar consultas.

Ejemplo 1

SELECT * FROM Sales.SalesOrderDetail WHERE productID = 870

El plan resultante es el siguiente

Selectividad1

Como podemos ver el plan refleja el hecho de que existen muchos registros con el productID = 870 y por lo tanto le resulta más eficiente realizar en SCAN. La selectividad de la condición no es adecuada para un acceso puntual.

Ejemplo 2

SELECT * FROM Sales.SalesOrderDetail WHERE productID = 719

El plan resultante es el siguiente

Selectividad2

En este caso el SQL Server elige realizar un SEEK del índice NON CLUSTERED porque determina que es más eficiente. Luego debe realizar un KEY LOOKUP para obtener el resto de las columnas que no se encuentran en el índice. Si comparamos estos dos casos podemos ver claramente que a diferentes valores de productID, el SQL Server determina en cual es el mejor método de acceso analizando la selectividad de cada valor de productID en particular.

Ejemplo 3

DECLARE @ProdId INT

SET @ProdId = 719

SELECT * FROM Sales.SalesOrderDetail WHERE productID = @ProdId

El plan resultante es el siguiente

Selectividad3

En este ejemplo vemos que a pesar de que el valor es el mismo del ejemplo anterior, debido al uso de la variable no puede conocer el valor específico al momento de compilar y optimizar, y por lo tanto utiliza el caso promedio que corresponde a la selectividad promedio y por lo tanto realiza un SCAN independientemente del valor de la variable.

Vamos a dejar para otro artículo el tema de los parámetros de los procedimientos almacenados ya que requiere un análisis particular.

La densidad

Si buscamos en la documentación del SQL Server encontramos que el comando DBCC SHOW_STATISTICS nos permite ver la información estadística usada para seleccionar el plan de ejecución. Dentro de esta información se encuentran las densidades y un histograma disponible para la primera columna del índice. Esta fue la información que permitió determinar los planes de ejecución en los ejemplos anteriores.

La densidad se define como:

Ecuacion Densidad

Por lo tanto si multiplicamos la densidad por la cantidad de registros de la tabla obtenemos el promedio de valores iguales o el inverso multiplicativo de la selectividad.

DBCC SHOW_STATISTICS ( 'Sales.SalesOrderDetail' , 'IX_SalesOrderDetail_ProductID')

Dentro de la información que nos presenta está:

 

All density

Average Length

Columns

0,003759399

4

ProductID

8,242868E-06

8

ProductID, SalesOrderID

8,242868E-06

12

ProductID, SalesOrderID, SalesOrderDetailID

Si multiplicamos por la cantidad de filas de la tabla obtenemos 0, 003759399 * 121317 = 456,07 que es el mismo valor que antes.

Conclusión

Un índice puede ser selectivo de acuerdo al valor utilizado. Si el índice no es selectivo, el SQL Server no lo va a utilizar para acceder a los registros mediante un SEEK y va a preferir realizar un TABLE SCAN. No tiene sentido crear índices para acceder puntualmente a un subconjunto de registros de la tabla en el caso de no ser selectivos. Existen otros motivos para crear índices como cover index que justifican en algunos casos índices no tan selectivos.

Tags: , , ,

Artículos

Los índices y las condiciones de búsqueda

por Jose Mariano Alvarez 24. febrero 2009

Para poder entender lo que hace de SQL Server antes de aplicar cualquier tipo de índice en una consulta tenemos que entender que el SQL Server tiene que determinar si un determinado índice es útil o no. Este articulo trata de mostrar como un predicado mal escrito impide el uso de accesos puntuales mediante INDEX SEEK.

Para entender esto vamos a simplificar las cosas y comenzar con una consulta trivial.

SELECT <Columnas devueltas>
FROM   <Tabla>
WHERE  <Condición>

Está simple consulta, que utiliza una sola tabla, precisa de las “columnas devueltas” y de las columnas utilizadas en la condición para verificar si la cumplen. Por el momento consideraremos condiciones simples sin subconsultas.

El punto crítico aquí es la condición, ya que es esta la que limita la cantidad de filas devueltas y por lo tanto la posibilidad de hacer un acceso puntual (SEEK) mediante índices o una exploración (SCAN) ya sea de índices o de tabla dependiendo de las columnas involucradas. El problema radica en que el SQL Server debe determinar conociendo información estadística de los datos de las columnas involucradas en la condición, la estructura de los índices disponibles y la forma en que está escrita la condición si el uso de un índice mediante acceso puntual (SEEK) es adecuado o es preferible una exploración (SCAN).

Vamos a ignorar por el momento las columnas devueltas y el tipo de índice (que dejaremos para otros artículos ) a efectos de simplificar el análisis y entender la idea de los argumentos de búsqueda,. Simplemente nos vamos a concentrar en como un predicado de búsqueda mal expresado puede impedir el uso de los índices mediante un SEEK.

Veamos un ejemplo usando AdventureWorks:

USE AdventureWorks
GO

SELECT * 
FROM Sales.SalesOrderHeader 
WHERE customerid = 117

 

IndexSeek

Aquí vemos como el predicado de búsqueda permite determinar facilmente que el acceso puntual por índice es útil. Puede verse el Index Seek. Esto ocurre porque el predicado customerid = 117 es un buen argumento de búsqueda y por lo tanto dado que tiene información estadística puede determinar que le conviene usar el índice porque determina que devuelve un porcentaje muy bajo de filas en proporción a las que tiene la tabla.

Qué ocurre si cambiamos el argumento de búsqueda:

SELECT * 
FROM Sales.SalesOrderHeader 
WHERE customerid + 1 = 118

 

IndexScan

En este caso el resultado es el mismo, en cuanto a filas devueltas, ya que ambos predicados son equivalentes, pero la condición customerid + 1 = 118 no permite el acceso puntual mediante SEEK y debe hacer un INDEX SCAN.

Si analizamos un poco ambas condiciones vemos que la segunda condición por igualdad esta aplicada al resultado de una operación suma y no a una columna por lo que no le es posible al motor del SQL Server hacer una búsqueda puntual porque lo que está en el índice no es el resultado de la suma sino el customerid. Por lo tanto no le queda más remedio que revisar fila por fila para determinar si se cumple la condición de igualdad.

Para verlo más claramente es muy parecido a si buscamos a todas las Daniela de la guía telefónica. Dado que esta ordenada por apellido y luego por nombre no es posible hacer un acceso puntual a las páginas donde se encuentran y es necesario revisar todas las páginas de la guía verificando abonado telefónico por abonado telefónico si se cumple la condición. Esto mismo ocurriría aunque hubiera solo 10 personas con nombre Daniela.

Conclusión

Si no escribimos las condiciones de nuestras consultas de manera adecuada nunca podrá usar los índices para realizar accesos puntuales (INDEX SEEK). En líneas generales las condiciones requieren de un predicado del tipo columna <operador> <expresión>, donde no todos los operadores cumplen con esta condición.

Tags: , ,

Artículos

Cómo saber si una consulta usa índices

por Jose Mariano Alvarez 17. febrero 2009

Esta es la primera pregunta que me hacen cuando llego a un cliente que me requiere porque tiene problemas de performance o rendimiento en el SQL Server.

Una de las cosas más importantes, con respecto a esta frase, es entender que quieren decir cuando hacen esta pregunta. El primer diagnóstico que suele hacerse es ver que la consulta esta lenta a pesar de que nos dicen que la tabla tiene índices y los está usando.

Entonces veamos que es un error habitual pensar que cuando en el plan de ejecución aparece la palabra Index esto significa que se está usando la estructura del índice para acceder puntualmente a los registros.

Tomando como ejemplo la base de datos AdventureWorks

Ejecutamos la siguiente consulta:

SET NOCOUNT on
Set STATISTICS PROFILE OFF
Set STATISTICS IO ON

SELECT  TotalDue 
FROM    Sales.SalesOrderHeader
WHERE   ContactID = 100

 

Nos devuelve el siguiente resultado

TotalDue
---------------------
1980.8809
265.6878
481.5265
1107.7067

Table 'SalesOrderHeader'. Scan count 1, logical reads 703, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

El plan de ejecución es el siguiente:

Plan1

Como vemos en el plan de ejecución está utilizando un operador Cluster Index Scan que es casi lo mismo que un Table Scan. La diferencia es que en este caso la tabla posee un índice Clustered y por lo tanto los registros están ordenados por dicho índice. La consulta, por lo tanto está revisando uno por uno todos los registros de la tabla lo cual se refleja en las 703 lecturas lógicas.

Si cambiamos la consulta por :

SET NOCOUNT on
Set STATISTICS PROFILE OFF
Set STATISTICS IO ON

SELECT  TotalDue 
FROM    Sales.SalesOrderHeader
WHERE    salesorderid in(    
    51702,    
    57021,    
    63139,    
    69398
);

Obtenemos el mismo resultado que antes pero ahora el plan de ejecución es:

Plan2

En este nuevo plan podemos ver que utiliza un Clustered Index Seek lo que significa que esta accediendo puntualmente a la página de datos lo cual se refleja en los 4 Scan count y las 12 lecturas lógicas.

Conclusión

Si comparamos ambas consultas vemos que no son idénticas pero nos permiten ver que la relación entre una y otra es de 1% a 99% y que el Index seek significa un acceso puntual mientras que un index scan significa recorrer todos los registros del índice. Por lo tanto en general, es preferible el que nuestra consulta se resuelva con un Seek.

Tags: , ,

Artículos

Powered by SQL Total Consulting


View Jose Mariano Alvarez's profile on LinkedIn

 Add to Technorati Favorites 

Calendar

<<  mayo 2017  >>
lumamijuvido
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234

View posts in large calendar

Locations of visitors to this page

Widget Twitter not found.

Root element is missing.X


Valid XHTML 1.0 Transitional

Valid CSS!