jump to navigation

msSQL – No todos los índices de SQL Server son creados iguales

Fuente: http://www.sql-server-performance.com/articles/per/index_not_equal_p1.aspx

Autor: Brad McGehee

Si usted tiene mucha experiencia con los índices, probablemente ya esté familiarizado con la diferencia entre índices agrupados y no agrupados. Pero este artículo no es acerca de ellos. Este artículo es acerca de si el optimizador de consultas de SQL Server utilizará sus índices tan cuidadosamente hechos. Puede que no sea consciente de ello, pero sólo porque una columna tiene un índice no significa que el optimizador de consultas lo va a usar. Como puede imaginar, la creación de un índice que nunca se utilizará es una pérdida de tiempo, y en el peor de los casos, incluso puede reducir el rendimiento de su aplicación. Vamos a aprender por qué.

Para comenzar, veamos un ejemplo sencillo. Supongamos que tenemos una base de datos. En esa base de datos es una tabla llamada “pedidos”. Entre una serie de columnas en esta tabla, estamos interesados en dos columnas: “OrderID” e “IdEmpleado”. Esta tabla tiene 150000 filas y hay un índice no-agrupado en “IdEmpleado”. Ahora digamos que queremos ejecutar la siguiente consulta:

SELECT OrderID FROM Orders WHERE IdEmpleado = 5

La primera cosa a notar acerca de la consulta es que la columna “IdEmpleado” utilizada en la cláusula WHERE de la consulta tiene un índice no-agrupado. Debido a esto, lo más probable es suponer que cuando se ejecuta esta consulta a través del optimizador de consultas,  usará el índice para producir resultados.

 Lamentablemente, no se puede suponer esto automáticamente. Sólo porque hay un índice no significa necesariamente que el optimizador de consultas lo va a usar. Esto se debe a que el Analizador de consultas siempre evalúa si un índice es útil o no antes de que se utilice. Si el Analizador de consultas examina un índice y considera que no es útil, lo ignora, y si es necesario, se realizará una exploración de la tabla para producir los resultados solicitados.

Entonces, ¿qué es un índice útil? Con el fin de responder a esta pregunta, tenemos que entender que uno de los principales objetivos del optimizador de consultas es el de reducir la cantidad de I / O, y la correspondiente cantidad de tiempo que se tarda en ejecutar una consulta. En otras palabras, los optimizadores de consultas evalúan muchas maneras diferentes de ejecutar una consulta, y encuentra la que piensa que producirá la menor cantidad de I / O. Pero lo que puede ser sorprendente es que la utilización de un índice disponible no siempre significa que se tendrá como resultado la menor cantidad de E / S. En muchos casos, especialmente con los índices no agrupados, una exploración de la tabla  puede producir menos de E / S que un índice.

Antes de que el optimizador de consultas use un índice, evalúa el índice para ver si es lo suficientemente selectivo. ¿Qué significa esto? La selectividad se refiere al porcentaje de filas de una tabla que son devueltos por una consulta. Una consulta se considera altamente selectiva si devuelve un número muy limitado de filas. Una consulta se considera que tiene poca selectividad si devuelve un alto porcentaje de filas. En términos generales, si una consulta devuelve menos del 5% del número de filas en una tabla, se le considera de alta selectividad, y lo más probable es que el índice se utilice. Si la consulta devuelve entre el 5% – 10% de las filas, el índice puede ser o no utilizado. Si la consulta devuelve más de un 10% de las filas, lo más probable es que el índice no se utilice. Y suponiendo que no hay otros índices útiles para la consulta, una exploración de tabla se llevará a cabo.

Vamos a volver a nuestro ejemplo de consulta:

SELECT OrderID FROM Orders WHERE IdEmpleado = 5

Sólo mirando a la consulta no sabemos si el índice de IdEmpleado se utilizará o no. Supongamos que sabemos que de los 150.000 registros en la tabla, “IdEmpleado = 5” es cierto para 5000 de los registros. Si dividimos 5.000 por 150.000 obtenemos 3,3%. Dado que el 3,3% es inferior al 5%, lo más probable es que el optimizador de consultas utilice el índice. ¿Pero qué pasa si “IdEmpleado = 5” es verdad para 25.000? En este caso, se divide 25.000 por 150.000, y se obtiene el 16,6%. Dado que el 16,6% es superior al 5%, o incluso el 10%, lo más probable es que el optimizador de consultas no utilice el índice y en su lugar de realice una exploración de tabla.

Entonces, ¿cómo puede una exploración de tabla utilizar menos E / S que un índice, como el índice no agrupado en nuestro ejemplo? Los Índices no-agrupados son grandiosos si es muy selectivo, especialmente si va a devolver un registro. Pero si muchos registros serán devueltos, y el índice no es muy selectivo, es muy caro en E / S recuperar los datos. La razón de esto es que el optimizador de consultas tiene que ir primero al índice para localizar los datos (utilizando E / S) y luego tiene que ir a la tabla para recuperarlo (más de E / S). En algún momento, el optimizador de consultas determina que cuestan menos E / S examinar toda una tabla de lo que lo hace para ir y venir entre el índice y la tabla para recuperar los registros solicitados.

El ejemplo anterior se aplica principalmente a los índices no agrupados. Si se dispone de un índice agrupado, el índice puede ser utilizado, incluso si hay baja selectividad, porque el índice es la tabla y las operaciones de E / S pueden ser muy eficaces.

Entonces, ¿cómo hace el optimizador de consultas para saber si un índice es suficientemente selectivo para ser útil? Lo hace mediante el mantenimiento de estadísticas de índice sobre cada índice en cada tabla. Las estadísticas de índice son un histograma de valores que se guardan en la tabla sysindexes. Estas estadísticas son una muestra de los registros disponibles que le dice al optimizador de consultas aproximadamente que tan selectivo es un índice.

Las estadísticas de índice se crean cada vez que se crea un índice, reconstruidas, cuando el comando UPDATE STATISTICS se ejecuta, y automáticamente usado por el optimizador de consultas en caso de necesidad. Las estadísticas no se mantienen en tiempo real, ya que sería demasiado trabajo en el servidor. Pero porque las estadísticas no son en tiempo real, pueden salir de la fecha, y, a veces, el optimizador de consultas puede hacer una mala elección, porque las estadísticas no son actuales.

Pero sólo porque las estadísticas sean actuales no significa que el optimizador de consultas utilice un índice. Recuerde, el optimizador de consultas basa su decisión en la selectividad de un índice, y el utiliza las estadísticas para determinar la selectividad.

Por lo tanto, si el optimizador de consultas puede comprobar si un determinado índice es útil o no, ¿cómo podemos hacer lo mismo? Afortunadamente, hay un comando que nos permite examinar un índice y averiguar si un determinado índice es suficientemente selectivo como para ser utilizado.

La razón por la cual queremos saber si un índice es lo suficientemente selectivo o no es porque si no lo es entonces no será utilizado si un índice no será utilizado, entonces no tiene ningún sentido tenerlo .Lo mas probable es que la eliminación de los índices innecesarios pueda aumentar el rendimiento de su aplicación porque los índices, como usted probablemente sabe,  desaceleran los INSERT, UPDATE y DELETE en una tabla por la sobrecarga de mantener esos índices.  Y si  la tabla en cuestión esta  sujeta a altos niveles de  cambios de la base de datos el mantenimiento de los índices puede causar cuellos de botella. Entonces nuestro objetivo es asegurar que si tenemos índices que estos sean lo suficientemente selectivos para ser útiles. Nosotros no queremos mantener índices que no serán utilizados.

El comando que usaremos para averiguar la selectividad de un índice es:

 DBCC SHOW_STATISTICS (table_name, index_name)

Cuando este comando corre genera una salida similar a la siguiente. Ese es un resultado real basado en una de las bases de datos que yo mantengo.

Estadísticas para  INDEX ‘in_tran_idx’.

Updated              Rows    Rows Sampled  Steps  Density       Average key length 
——————– ——- ————  —— ————  ——————
Feb 24  2001 3:36AM  7380901 7163688       300    2.2528611E-5  0.0

(1 row(s) affected)

All density Columns 
————————
2.2528611E-5 in_tran_key

Steps 
———– 

283 
301 
340 
371 
403 
456 

44510 

(300 row(s) affected)

DBCC ejecución Completada .Si DBCC presento mensajes de error contáctese con su administrador de sistemas .

Este resultado incluye un montón de información, mucha de la cual esta mas allá del alcance de este libro .En lo que nos queremos enfocar es en el valor de densidad  “2.2528611E-5” bajo la columna “All density”.
Densidad se refiere al porcentaje promedio de filas duplicadas en un índice   si una columna indexada , similar a employeeid, tiene demasiada información duplicada entonces se dice que ese índice tiene una alta densidad .Pero si una columna indexada tiene mayormente información única entonces se dice que el índice tiene una baja densidad.

La densidad esta  inversamente relacionada a la selectividad .Si la densidad es un numero alto, entonces la selectividad es baja, lo cual significa que un índice posiblemente no será utilizado. Si la densidad es un numero bajo, entonces la selectividad es alta, y el  índice será mas probablemente utilizado.

En el ejemplo de arriba, la densidad para el índice es menor al 1%. Por lo que esto significa que la selectividad de la tabla esta sobre el 99%, lo cual significa que el índice es probablemente muy útil para el optimizados de consultas.
Si usted es un DBA avanzado probablemente Habrá notado que he simplificado demasiado esta discusión, aun así, el punto que quiero marcar en este articulo es aun muy valido, y mi punto es  que no todos los índices son iguales .Justamente porque que un índice este disponible para el optimizador de consultas no significa que será siempre utilizado.

Para los DBA esto significa que usted necesita ser cauto con los índices de sus tablas. A medida que el tiempo lo permita usted podrá querer correr el comando DBOC SHOW_STATISTICS y ver cuan selectivos son realmente sus índices. Podrá encontrar que algunos de sus índices no serán utilizados, si este es el caso podría querer  considerar removerlos lo que a su vez aumentara la velocidad de su aplicación.

 Para nuevos DBAs eliminar en vez de agregar nuevos índices parece  ser  un camino inverso para  retocar el rendimiento de la base de datos .Pero cuanto mas usted sabe sobre como funciona internamente el SQL Server mejor entenderá las limitaciones de de utilizar índices para aumentar el rendimiento de sus aplicaciones.


Conclusiones:

Abril 2009

 Flores Germán  1327-0476
Cabaña Leonardo 3901-1351
Sarcansky Verónica 3901-2786

Si bien el uso de índices puede aumentar la velocidad de las aplicaciones no todos los índices le serán útiles al optimizador de consultas, si una tabla indexada  tiene un alto índice de selectividad entonces ese índice le será útil al optimizador de consultas, sino no y probablemente sea mas eficiente eliminarlo ya que el mantenimiento de los índices genera un overhead muchas veces excesivo cuando se mantienen índices que no serán utilizados.

 

Alvarez Alejandro Maximiliano 3501-2672
Debole Leonardo 3601-1757
Gonzalez Fernando 3301-2657
Liffourrena Leandro Alejo 3201-0304
Marrone Ignacio 3801-1532
Trotta Franco 3801-0233

Al prestar detenida atención a este artículo, podemos decir que no siempre es óptima la utilización de los índices.

Nos resulta muy útil el concepto de selectividad para darnos cuenta cuando es factible utilizar los índices en una base de datos, también podemos contar con los histogramas estadísticos para valernos de ello.

Parte de los administradores de base de datos piensan que la utilización de índices es siempre óptima, pero en realidad, como bien este artículo nos hizo entender, puede que un índice resulte menos óptimo que la exploración de una tabla. Si se utilizara este índice provocaríamos un incremento considerable en el tiempo de ejecución de una consulta y hoy en día, el tiempo es el factor más preciado de cualquier empresa.

A %d blogueros les gusta esto: