Indices en SQL Server – Parte 1 – Introducción
1 10 2008
Etiquetas de Technorati: sqlserver,index
Sin lugar a dudas, el desempeño de una aplicación está directamente relacionado al buen
o mal diseño de los índices de la base de datos.
Para demostrar esto tomemos como ejemplo la tabla de usuarios de un sistema
cualquiera, la cual tiene la siguiente estructura:
Esta tabla no tiene ningún índice creado, por lo cual SQL Server tratará la tabla como
un HEAP. Un heap es una estructura de datos que almacena la posición física en la que
se almacenó cada nueva fila dentro de las páginas asignadas a la tabla.
Puesto que esta tabla no tiene ningún tipo de índice, es bastante eficiente para agregar
nuevas filas a la tabla pero muy ineficiente para encontrar una fila específica, esto se
debe a que es necesario leer toda la tabla para obtener el resultado deseado.
Para ilustrar esto, realicemos el siguiente experimento: utilizando el procedimiento
almacenado CrearUsuarios, crearemos 100.000 usuarios de los cuales el 10.000 no están
activos y su fecha de creación esta en los últimos 600 días.
exec CrearUsuarios 100000, 10000, 600
Ahora realicemos una consulta para validar el usuario al inicio de sesión del sistema.
SELECT Password, Activo FROM Usuario
WHERE Username = ‘Usuario123′
La respuesta probablemente funcione bastante rápido, ya que al recién haber creado los
datos, todas estas filas están en memoria, pero veamos el plan de ejecución de esta
consulta presionando el botón de la barra de herramientas del SQL Server
Management Studio y luego ejecutando la misma consulta.
Al pasar el mouse sobre el primer elemento de la izquierda se puede observar el costo
de la consulta.
El costo estimado de la consulta es de 0.685675 y la forma de resolverlo fue un Table
Scan, lo que implica leer toda la tabla.
Para obtener más información sobre el acceso a los datos requeridos para resolver la
consulta ejecutamos la siguiente consulta:
CHECKPOINT
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
SET STATISTICS IO ON
SELECT Password, Activo FROM Usuario
WHERE Username = ‘Usuario123′
SET STATISTICS IO OFF
Lo primero que pasa al ejecutar esta consulta es que SQL Server escribe todos los
cambios pendientes al disco, la segunda instrucción elimina todos los datos que tiene en
memoria. La combinación de estas dos instrucciones obliga a SQL Server a leer todo
desde disco nuevamente, es importante tener en cuenta que al ejecutar DBCC
DropCleanBuffers se produce un fuerte impacto en el desempeño de todos los usuarios,
por lo que no se debe utilizar en servidores de producción.
La tercera instrucción nos permite obtener información sobre las lecturas de datos
requeridas para contestar una consulta. El resultado que se obtiene en el área de
mensajes es:
(1 row(s) affected)
Table ‘Usuario’. Scan count 1, logical reads 774, physical reads 3, read-ahead reads
773, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Esto nos dice que se accedió a la tabla “Usuario”, se hizo una lectura completa de la
tabla 1 vez, lo que equivale a 774 páginas leídas, como cada página pesa 8Kb, 774 *
8kb es igual a 6 Mb, precisamente lo mismo que pesa la tabla completa.
Las lecturas físicas son las páginas que no se encontraban en memoria cuando SQL
Server las necesitaba, en este caso fueron 3, algo extraño ya que vaciamos el cache
antes de ejecutar la consulta por lo cual no debería existir ninguna página en memoria,
pero SQL Server es más inteligente y cada vez que tiene que leer páginas desde el disco,
lo cual es una operación muy costosa, lee también otras páginas cercanas que puedan
ser útiles para responder la consulta que solicitó la lectura. En este caso SQL Server fue
capaz de Leer 773 de las 774 páginas utilizando este mecanismo de lecturas anticipadas
(read-ahead).
Las “lob” o Large Object Block, son operaciones relacionadas a datos que no se
guardan junto con la fila como los campos de tipo TEXT, IMAGE y los tipos de datos
que sobrepasen los 8000 bytes de la página. En este caso no hay operaciones de este
tipo.
En el próximo post veremos los tipos de índices que están disponibles en SQL Server
2000, 2005 y 2008.
Índices en SQL Server – Parte 2 – Tipos de índices
7 10 2008
Etiquetas de Technorati: sqlserver,Index
Para mejorar el desempeño de las consultas se utilizan índices, los más utilizados son
los Clustered y Non-Clustered.
Existen otros 3 tipos de índices que se utilizan para mejorar los tiempos de acceso a
datos XML, a búsquedas de texto y de datos espaciales. A continuación se muestra la
disponibilidad según la versión de SQL Server:
Clustered Non- Full Text Xml Index Spatial
Index Clustered Index Index
Index
SQL Server si si – – –
2000
SQL Server si si (1) si si –
2005
SQL Server si si (1 y 2) si si si
2008
(1) Permite definir Columnas Incluidas (2) Permite definir Índices Filtrados
Los Clustered Indexes son índices que controlan el orden físico de las filas en la tabla,
por lo cual solo puede existir uno para cada tabla.
Los Non-Clustered indexes son índices que mantienen un sub conjunto de las columnas
de la tabla en orden. Estos indices no modifican el orden de las filas de la tabla, en
lugar de esto mantienen una lista ordenada de referencias a filas de la tabla original.
Para ilustrar la diferencia entre estos 2 tipos de índices podemos decir que las páginas
blancas de la guía telefónica tienen un clustered index por Apellido(s) y Nombres, con
lo cual puedo buscar de forma muy eficiente el número de teléfono de una persona si
conozco sus apellidos y su nombre, una vez que lo encuentro obtendré su número de
teléfono en forma inmediata pues el numero está al lado del nombre.
En el caso de las páginas amarillas de la guía telefónica la forma de buscar es un poco
distinta, en este caso busco por rubro. Primero busco en un índice, el cual me indica en
qué página se encuentra la lista de empresas que satisfacen la condición que busco. Esto
mismo es lo que pasa cuando utilizo un índice Non-Clustered index una vez que
encuentro lo que quiero en el índice debo ir a leer la fila específica para obtener el resto
de los datos.
Veamos qué pasa cuando agregamos un índice a la columna Username de la tabla
usuario que creamos en la parte 1.
El índice será Non-Clustered y Único puesto que no podemos tener más de un usuario
con el mismo nombre.
Al volver a ejecutar la misma consulta, obtenemos lo siguiente:
El plan de ejecución es un poco más complicado pero esta consulta es 100 veces menos
costosa que la anterior. Ahora la consulta utiliza el índice para encontrar el RID, que es
el identificador de la fila, con este RID hace una búsqueda en la tabla de tipo heap (RID
Lookup).
Esta reducción de costo se explica por la cantidad de accesos a datos que esta consulta
requiere, puesto que estos bajan desde 774 a solo 4 páginas lo que equivale 32 kb de
datos contra 6192 kb, que eran necesarios antes de la creación del índice.
(1 row(s) affected)
Table ‘Usuario’. Scan count 0, logical reads 4, physical reads 1, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Veamos otra alternativa, si utilizamos un clustered index en lugar de un non-cluster
index no será necesario el lookup, por lo que el acceso será más eficiente.
Ahora el plan de ejecución se ve así:
El
acceso se ve así:
Table ‘Usuario’. Scan count 0, logical reads 3, physical reads 2, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Como se puede ver el costo de esta consulta ahora es la mitad que con el índice non-
clustered y en lugar de 4 lecturas lógicas tenemos sólo 2.
Es importante destacar que según Comparing Tables Organized with Clustered Indexes
versus Heaps, es siempre recomendable utilizar tablas que utilicen un Cluster index en
lugar de tablas que solo utilicen índices non-cluster.
En los próximos post veremos como diseñar los índices y llaves primarias de las tablas,
de qué tipo deben ser y cuales son sus costos asociados.
Índices en SQL Server – Parte 3 – Clustered Index
13 10 2008
Etiquetas de Technorati: sqlserver,index
Como mencioné en los post anteriores (parte 1, parte 2) el clustered index controla el
orden físico de las filas en la tabla, a diferencia de los índices Non-Clustered que
funcionan como una lista ordenada de identificadores de fila.
La siguiente ilustración muestra la estructura que tiene un clustered index.
Todas las tablas que tienen un clustered index tienen un nodo raíz y muchos nodos en
los niveles intermedios, estos a su vez pueden apuntar a nodos hojas o a otros nodos
intermedios. Esta estructura forma un árbol (B-Tree) que permite encontrar cualquier
fila en forma eficiente.
La búsqueda parte desde el nodo raíz, este nodo tiene una lista de llaves, se comparan
estas llaves para encontrar el nodo de nivel intermedio que contenga un rango de llaves
que cubra la llave que se está buscando. Luego se repite el proceso en los nodos
intermedios hasta que se encuentre la página de datos que contenga el la fila específica.
Para ilustrar este proceso, realicemos una búsqueda sobre un clustered index de la llave
123.
Contenido del Nodo Raíz (ID 0)
Llave ID Nodo
Intermedio
1 1
100 2
160 3
300 4
500 5
1000 6
Contenido del Nodo Intermedio (ID 2)
Llave ID Nodo
Intermedio
100 10
110 11
120 12
130 13
140 14
150 15
Contenido del Nodo Hoja (ID 12)
Llav Columna 1 Columna 2 … Columna N
e
120 AX06 10000 XXX
121 AX02 10004 XXX
122 AX07 10000 XXX
123 AX04 20000 XXX
124 AX08 9000 XXX
125 AX01 1 XXX
Para obtener el resultado se lee el nodo raíz, se busca el nodo intermedio que contiene a
la llave 123, en este caso el nodo 2 contiene filas con llave entre 100 y 160. Luego se
repite el proceso, el nodo 12 contiene filas con llaves entre 120 y 130. El nodo 12 es un
página de datos por lo cual no necesitamos seguir buscando.
Cada vez que se agrega una fila a la tabla, SQL Server debe insertar la nueva fila en la
posición correcta dentro del índice, esto puede ser una operación simple y eficiente si es
que la página es la última del índice o si la página tiene espacio disponible (ver Fill
Factor). Si la página no tiene espacio es necesario dividir la página en 2, algo conocido
como Page Split, que deja 2 páginas con un 50% de utilización.
Cada vez que se elimina una fila de la tabla, SQL Server eliminará la fila de la página,
sin modificar ninguna otra página, lo cual limita el impacto de la operación a 1 sola
página pero causa que se desperdicie mayor porcentaje de las página, proceso conocido
como fragmentación.
Algo aun más costoso ocurre cuando se actualiza el valor de la llave del índice
clustered, en este caso SQL Server debe copiar la fila desde la pagina original, aplicar
los cambios indicados en el update, insertar la fila en la nueva página y finalmente
eliminar la fila desde la página original.
Índices en SQL Server – Parte 4 – Non Clustered Index
15 10 2008
Etiquetas de Technorati: sqlserver,index
En los post anterior (parte 3) vimos como los clustered index controlan el orden físico
de las filas en la tabla. Ahora veremos como operan los índices Non-Clustered.
La siguiente ilustración muestra la estructura que tiene un non-clustered index.
Al igual que en el caso de los clustered index, los non-clustered index tienen un nodo
raíz y muchos nodos en los niveles intermedios, estos a su vez pueden apuntar a nodos
hojas o a otros nodos intermedios. La diferencia se presenta en los nodos hoja, estos
tienen almacenados solo el Id del registro y no todo el registro, por lo que se hacer
necesario hacer una búsqueda sobre el índice cluster o sobre el heap para obtener el
resto de las columnas de la fila.
La búsqueda parte desde el nodo raíz, este nodo tiene una lista de llaves, se comparan
estas llaves para encontrar el nodo de nivel intermedio que contenga un rango de llaves
que cubra la llave que se está buscando. Luego se repite el proceso en los nodos
intermedios hasta que se encuentre la llave que identifica a la fila correspondiente a la
llave del índice.
Para ilustrar este proceso, realicemos una búsqueda de la llave AX04 sobre un non-
clustered index.
Paso 1.- Contenido del Nodo Raíz (ID 0)
Llave ID Nodo Intermedio
Índice
AA01 1
AG01 2
BA01 3
DB02 4
RF04 5
KJ01 6
Paso 2.- Contenido del Nodo Intermedio (ID 2)
Llave ID Nodo Intermedio
Índice
AG01 10
AJ10 11
AP20 12
AZ30 13
BA20 14
BH50 15
Paso 3.- Contenido del Nodo Hoja (ID 12)
Llave Llave
Índice
AP20 101
AQ10 50
AR12 160
AX04 123
AX24 145
AY25 12
Paso 4.- Hemos encontrado la fila (123), ahora debemos realizar una búsqueda
utilizando índice cluster, conocido como bookmark lookup en SQL Server 2000/2005 y
como key lookup en SQL Server 2008.
Llave ID Nodo Intermedio
1 1
100 2
160 3
300 4
500 5
1000 6
Paso 5.- Contenido del Nodo Intermedio (ID 2)
Llave ID Nodo Intermedio
100 10
110 11
120 12
130 13
140 14
150 15
Paso 6.- Contenido del Nodo Hoja (ID 12)
Llave Columna 1 Columna 2 … Columna N
120 AX06 10000 XXX
121 AX02 10004 XXX
122 AX07 10000 XXX
123 AX04 20000 XXX
124 AX08 9000 XXX
125 AX01 1 XXX
En SQL Server la Llave Índice puede ser una o varias columnas, siempre y cuando el
largo combinado de estas columnas no supere los 900 bytes.
Columnas Incluidas
SQL Server 2005 agregó una nueva funcionalidad a los índices non-clustered llamada
“Columnas Incluidas” estas columnas no son parte de la llave índice, por lo que no se
mantienen ordenadas dentro del índice y como consecuencia de esto solo es necesario
almacenar su valor en el nodo hoja del índice.
En el ejemplo anterior, el paso 3 quedaría así al agregar la Columna 2 como una
columna incluida del índice:
Llave Llave Columna
Índice Incluida
(Columna 2)
AP20 101 12000
AQ10 50 12332
AR12 160 12344
AX04 123 20000
124 145 12233
125 12 12221
Las columnas incluidas tienen varias ventajas:
Al utilizar columnas incluidas en el índice es posible superar la limitación de los 900
bytes para la llave del índice, manteniendo un índice eficiente.
Las modificaciones al valor de una columna incluida es más eficiente que la
modificación de una columna que es parte de la llave del índice pues estas no
requieren ser mantenidas en orden.
Es posible crear Covering Indexes, que son índices que incluyen todas las columnas
requeridas para contestar una consulta específica, que no requieren la búsqueda sobre
el clustered index. Este tipo de índice es igual o más eficiente que u índice cluster.
Índices Filtrados
SQL Server 2008 agregó otra mejora a los índices non-clustered llamada “Índices
Filtrados”. Estos índices mantienen ordenados un sub conjunto de las filas de la tabla.
El uso más común de este tipo de índices se da cuando queremos crear un índice sobre
una columna que permite valores nulos. Al crear un índice normal podemos
desperdiciar una gran parte del espacio de índice ordenando filas que tienen un valor
nulo.
A continuación se describen algunos ejemplos de casos de uso para estos índices
filtrados:
La columna “fecha de término de contrato” de la tabla empleado contiene una gran
cantidad de filas con el valor nulo.
Los estados intermedios son buenos candidatos para ser índices filtrados. Es posible
crear un índice que solo contenga las ordenes de compra recibidas y en proceso, pero
no las despachadas.
Definir un índice CREATE INDEX
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX
nombre_indice
ON <objeto> (columna [ ASC | DESC ] [ ,...n ] )
[ ; ]
<objeto> ::=
[nbBaseDatos.[nbEsquema].| nbEsquema.]nbTablaVista
Esta es la sintaxis simplicada de la instrucción CREATE INDEX que permite crear un
índice en una tabla sobre una o varias columnas.
nbBaseDatos Es el nombre de la base de datos.
nbEsquema Es el nombre del esquema al que pertenece la tabla/vista.
nbTablaVista Es el nombre de la tabla o vista sobre la que se quiere crear el índice.
nombre_indice Es el nombre del índice que estamos creando.
Columna Es el nombre de la columna que forma parte del índice. Se pueden definir
índices compuestos escribiendo entre paréntesis los nombres de las columnas separados
por comas.
ASC los valores de la columna se ordenarán de forma ASCendente o DESCendente. Por
defecto se asume ASC.
UNIQUE permite definir un índice único (no admite valores repetidos).
CLUSTERED el índice será agrupado.
NONCLUSTERED (valor por defecto) el índice será no agrupado.
Ejemplos:
CREATE INDEX I_clientes_nombre ON Clientes (nombre)
Crea un índice no agrupado sobre la columna nombre de la tabla Clientes en la base de
datos actual, las filas se ordenarán de forma ascendente.
CREATE INDEX I_clientes_ApeNom ON Clientes (apellidos, nombre)
Crea un índice no agrupado sobre las columnas apellidos y nombre de la tabla Clientes
en la base de datos actual, las filas se ordenarán de forma ascendente por apellido y
dentro del mismo apellido por nombre.
CREATE INDEX I_clientes_EdadApe ON Clientes (edad DESC,apellidos)
Crea un índice no agrupado sobre las columnas edad y apellidos de la tabla Clientes en
la base de datos actual, las filas se ordenarán de forma descendente por edad y
ascendente por apellido. Aparecerán los clientes de mayor a menor edad y los clientes
de la misma edad se ordenarán por apellido (por orden alfabético).
CREATE CLUSTERED INDEX I_clientes_cod ON Clientes (codigo)
Crea un índice agrupado sobre la columna codigo de la tabla Clientes en la base de
datos actual, las filas se ordenarán y almacenarán por orden de código.
CREATE UNIQUE INDEX U_clientes_col ON Clientes (col)
Crea un índice único sobre la columna col de la tabla Clientes en la base de datos actual,
la columna col no podrá contener valores duplicados.
8.14. Eliminar un índice DROP INDEX
Para eliminar un índice tenemos la sentencia DROP INDEX.
La instrucción DROP INDEX no es aplicable a los índices creados mediante la
definición de restricciones PRIMARY KEY y UNIQUE. Para quitar la restricción y el
índice correspondiente, se tiene que ejecutar un ALTER TABLE con la cláusula DROP
CONSTRAINT.
Sintaxis simplificada:
DROP INDEX <indice> [ ,...n ] [ ; ]
<indice>::=
nbindice ON [nbBaseDatos.[nbEsquema].|nbEsquema.]nbTablaVista
nbBaseDatos Es el nombre de la base de datos.
nbEsquema Es el nombre del esquema al que pertenece la tabla/vista.
nbTablaVista Es el nombre de la tabla o vista de la que se quiere eliminar el índice.
nbindice Es el nombre del índice a eliminar.
Ejemplo:
DROP INDEX U_clientes_col ON Clientes;
Elimina el índice U_clientes_col definido sobre la tabla Clientes.
