SQL Server, ideas y experiencias

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

Materiales - Arquitectura, rendimiento y performance tuning del SQL Server

por Jose Mariano Alvarez 8. mayo 2009

El objetivo de este seminario fue aprender acerca de la mejor forma de utilización del SQL Server, partiendo del estudio de la arquitectura interna para poder entender como usa los recursos hasta llegar al análisis del rendimiento de aplicaciones y las alternativas para solucionar los problemas que se presenten. Esto apuntó a solucionar tanto los problemas de performance de aplicaciones que se han desarrollado y se tiene su código como de las que no se conoce detalladamente su diseño. El objetivo final fue tener el conocimiento necesario para optimizar el uso que hace una aplicación de los recursos disponibles en el SQL Server.  En ocasiones se hizo comparaciones para ver diferencias y similitudes con el SQL Server 2000/05/08.

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

Arquitectura rendimiento y performance Tuning del motor relacional del SQL Server Parte 1.pdf (650,83 kb)

Arquitectura rendimiento y performance Tuning del motor relacional del SQL Server Parte 2.pdf (968,57 kb)

Demos.zip (44,44 kb)

 

DETALLES DE CONTENIDOS

1)      Performance

a)      Introducción teórica

b)      El concepto de performance (rendimiento)

c)       Indicadores de performance

2)      Arquitectura y funcionamiento interno

a)      Componentes internos

b)      Gestión de la memoria

c)       Páginas y Extents

d)      Buffers y cache

e)      Discos y RAID

f)       Configuración

g)      Hardware

h)      Software

3)      Bases de datos
a)      Archivos físicos

b)      Asignación de recursos y filegroups

c)       Opciones de bases de datos

d)      Collation

e)      Shrink

f)       Bases del sistema

g)      Cambios en la tempdb

4)      Logging y recuperación

a)      Transaction Log

b)      Backup y Restore

c)       Fallos y recovery

5)      Tablas

a)      Arquitectura interna

b)      Tipos de datos

c)       Novedades y cambios en SQL Server 2005

d)      Novedades en SQL Server 2008

6)      Índices

a)      Árboles B  y Heaps

b)      Índices clustered y non Clustered

c)       Unicidad

d)      Fillfactor y padd index

e)      Estadísticas y densidades

f)       Page splits y fragmentación

g)      Reindexado

h)      Particionamiento de tablas e índices

i)        Gestión de índices

j)        Intersección de índices

k)      Cover index y variantes según la versión

l)        Sparse index en SQL Server 2008

m)    Índices parciales en SQL Server 2008

7)      Planes de ejecución y procesamiento.

a)      Operadores lógicos y físicos

i)        Conceptos

ii)       Entendiendo el plan

iii)     Conceptos de Hash, Merge y Nested loops

b)      Procesamiento de queries

i)        Select, insert, delete, update

ii)       Truncate

iii)     Join

(1)    Hash

(2)    Merge

(3)    Nested loops

iv)     Where y SARGS

v)      Subqueries

vi)     Constraints

vii)   Vistas simples

viii)  Vistas indexadas

ix)     Cursores

x)      Triggers

c)       Store procedures

i)        El cache de procedimientos

ii)       Compilación y recompilación

d)      Funciones

e)      Resultados intermedios

i)        Tablas temporales

ii)       tablas derivadas

iii)     Tipo de dato tabla

f)       Control del plan de ejecución

i)        Heurísticas

ii)       Hints

iii)     Plan Guides

g)      Novedades

h)      XML e Indices XML en SQL Server 2005/8

8)      Recursion y Common Table Expresions en SQL Server 2005

9)       Operadores de conjunto en SQL Server 2005

10)   Funciones de numeración en SQL Server 2005

11)   Agregación y pivoteo en SQL Server 2005

12)    Datos Jerárquicos en SQL Server 2008

13)    Filestream en SQL Server 2008

14)    Merge en SQL Server 2008

15)   Transacciones y concurrencia

a)      Modelos y propiedades ACID

b)      Modos de aislamiento.

c)       Snapshot Isolation level y row versioning.

d)      Tipos de locks y escalamiento

e)      Deadlocks

16)   9) Midiendo el rendimiento (monitoring)

a)      Performance monitor

b)      SQL Server Profiler

c)       Dynamic management Views

d)      Statistics time y Statistics IO

e)      El debug en cada version

f)       Index Tuning Wizard

g)      Database Tuning Adviser

h)      Query analizar

i)        Management studio

j)        Built-in Traces

k)      Otras herrramientas

17)   10) CLR

a)      Stored procedures y funciones

b)      Performance

18)   11) Temas especiales

a)      Manejo de grandes volúmenes

b)      Normalización versus desnormalización

c)       Database snapshots

d)      NUMA y segregación de la carga

e)      Control de la carga (2008)

Tags: , ,

Eventos y conferencias

Powered by SQL Total Consulting


View Jose Mariano Alvarez's profile on LinkedIn

 Add to Technorati Favorites 

Calendar

<<  junio 2017  >>
lumamijuvido
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789

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!