jump to navigation

msSQL – Como Crear Columnas Sin Clave en Índices No agrupados en SQL Server 2005

Fuente: http://www.sql-server-performance.com/articles/clustering/create_nonkey_columns_p1.aspx

Por: Brad McGehee 

Digamos por ejemplo, que tenemos una tabla que contiene estas columnas (Este ejemplo ha sido simplificado para hacerlo más fácil de seguir):

 Clave_Primaria      Numero_Cliente      Nombre_Cliente      Direccion_Cliente

Supongamos que la columna Clave_Primaria es un número entero autoincremental y que las tres últimas columnas son columnas de tipo VARCHAR. Demos por sentado también, que hay un índice agrupado sobre la columna Clave_Primaria, y que no hay ningún índice no agrupado sobre la tabla.

Ahora, vamos a asumir que ejecutamos la consulta siguiente:

SELECT Numero_Cliente, Nombre_Cliente, Direccion_Cliente

FROM Nombre_Tabla

WHERE Clave_Primaria = 1001

 Cuando el optimizador de consultas analice esta consulta, sabrá que la columna Clave_Primaria es la clave a un índice agrupado. Entonces, el índice agrupado se usará (probablemente) para localizar muy rápidamente la Clave_Primaria de 1001, empleando una consulta de índice agrupado. Por añadidura, ya que otras tres columnas son parte del índice agrupado, los valores para estas tres columnas estarán inmediatamente disponibles y puedrán mostrarse inmediatamente después de que la consulta termina de ejecutarse. Tengamos presente que todas las columnas en un índice agrupado son almacenadas en el nivel final (leaf level) del índice agrupado, así SQL Server, en este ejemplo, no tedrá que buscar en otra parte para encontrar los datos a ser devueltos. Ahora, miremos la siguiente consulta:

 SELECT Numero_Cliente, Nombre_Cliente, Direccion_Cliente

FROM Nombre_Tabla

WHERE Numero_Cliente = ‘ABC123’

 En este caso, cuando esta consulta es analizada por el optimizador de consultas, no hay ningún índice sobre la columna Numero_Cliente. A causa de esto, SQL Server realizará una exploración de índice agrupado para buscar el registro designado para devolver. Ya que esta columna no indexada o única, cada fila en la tabla tendrá que ser explorada hasta que el registro sea encontrado. Una vez que es encontrado, el resto del registro (dado que es un índice agrupado y los datos de cada fila son la parte del nivel final de índice), estará inmediatamente disponible y sus datos serán devueltos.

Si nosotros necesitamos realizar la referida consulta a menudo, se debería para nuestro provecho, añadir un índice no agrupado a la columna Numero_Cliente.

De esta manera, cuando ejecutamos una consulta como la anterior, en vez de realizar una exploración de índice agrupado, que lleva mucho tiempo, puede usarse el índice no agrupado de Numero_Cliente para realizar una consulta de índice, que es mucho más rápida que una exploración de tabla. ¿Pero es esto todo que necesitamos debe hacer para optimizar la consulta anterior? En realidad, hemos olvidado algo. Si ejecutamos la consulta, vamos a observar, nuevamente debajo, que algo más tiene que pasar antes de que nuestros datos sean devueltos.

 SELECT Numero_Cliente, Nombre_Cliente, Direccion_Cliente

FROM Nombre_Tabla

WHERE Numero_Cliente = ‘ABC123’

 Cuando esta consulta se ejecuta, usará el índice no agrupado sobre la columna Numero_Cliente para rápidamente identificar el registro empleando una consulta de índice. Pero a diferencia de un índice agrupado, un índice no agrupado sólo contiene los datos almacenados en el índice, que es, en este caso, sólo la columna Numero_Cliente. Por otra parte, nuestra consulta busca devolver tres columnas: Numero_Cliente, Nombre_Cliente, y Direccion_Cliente, no solamente la clave Numero_Cliente. A causa de ello, SQL Server ahora tendrá que realizar otro paso antes de que pueda devolver nuestros datos. Una vez que ha localizado la fila correcta en el índice no agrupado, SQL Server debe buscar los valores de las otras dos columnas del índice agrupado, donde estos datos son almacenados. Esto se llama consulta de marcador de libros (bookmark lookup).

Como usted puede imaginarse, realizar una consulta de marcador de libros toma recursos adicionales de SQL Server y tiempo para desarrollarse, disminuyendo el rendimiento. ¿Debe evitarse de todo modo posible una consulta de marcador de libros? Si usted ha sido un administrador de base de datos durante mucho tiempo, probablemente esté familiarizado con el concepto de índice de cubierta. Si usted no lo es, un índice de cubierta es un índice no agrupado que incluye no sólo el valor empleado para realizar la consulta (el Numero_Cliente en nuestro ejemplo), sino también otras columnas de datos necesarias para abarcar la consulta. En este caso, las columnas Nombre_Cliente y  Direccion_Cliente.

En vez de crear un índice no agrupado con Numero_Cliente como única columna en el índice (que es lo que hicimos antes), nosotros crearemos un índice no agrupado que contiene tres columnas, incluyendo Numero_Cliente, Nombre_Cliente, y Direccion_Cliente. Se llama a esto índice de cubierta porque cuando la consulta se ejecuta y se usa Numero_Cliente para localizar la fila, los datos restantes necesarios para completar la consulta existen en el índice e inmediatamente pueden devolverse y ninguna consulta de marcador de libros tiene que ser realizada, como nosotros vimos en el ejemplo anterior.

Los índices de cubierta, de ser empleados con criterio, pueden acelerar muchas de las consultas comúnmente ejecutadas. Pero los índices de cubierta tienen algunas limitaciones. Por ejemplo, son limitados a un máximo de 16 columnas; tienen un ancho máximo de 900 caracteres; ciertos tipos de datos no pueden ser incluidos en ellos; y la adición de columnas adicionales a un índice hacen al índice más extenso, lo que a su turno requiere que más operaciones de entrada – salida de disco se efectúen para leer y escribir las filas, disminuyendo potencialmente el rendimiento, sobre todo si la tabla es sujeto a muchas inserciones (INSERT), actualizaciones (UPDATE) y eliminaciones (DELETE).

 En el pasado, los administradores de bases de datos han aprendido a vivir con estas desventajas. Como la mayor parte de las sugerencias de ajuste de rendimiento, los índices de cubierta tiene sus pros y sus contras, y el administrador de base de datos debe emplear su experiencia para determinar como ellos deberían de implementarse.

El comportamiento que acabamos de describir es verdadero para todas las versiones de  SQL Server, pero en SQL Server 2005, ahora tenemos un nueva funcionalidad llamada ” columna sin clave de índices no agrupado”. Ésta, es una variación de las índices de cubierta estándar de las que hemos estado hablando hasta este punto, pero con algunas ventajas sobre las mismas.

Vamos a volver a nuestra consulta de ejemplo.

SELECT Numero_Cliente, Nombre_Cliente, Direccion_Cliente

FROM Nombre_Tabla

WHERE Numero_Cliente = ‘ABC123’

 Supongamos, como antes, que hay un índice agrupado sobre Clave_Primaria. Ahora, en vez de crear un índice de cubierta como describimos anteriormente, lo que podemos hacer es crean un índice no agrupado sobre Numero_Cliente, pero en vez de añadir Nombre_Cliente y Direccion_Cliente como columnas claves adicionales índice,  añadimos Nombre_Cliente y Direccion_Cliente como columnas sin clave al índice no agrupado.

¿Qué significa esto? Esto se parece muchísimo a nuestro índice de cubierta, pero sólo la columna Numero_Cliente es parte de la clave real.

Nombre_Cliente y Direccion_Cliente siguen con la clave, sin embargo no son columnas clave, son columnas sin clave. Por hacer esto, obtenemos exactamente las mismas ventajas de un índice de cubierta en el cual no tenemos que realizar una consulta de marcador de libros para recuperar las columnas Nombre_Cliente y  Direccion_Cliente, dado que estas columnas son parte del índice.

¿Ahora tal vez se pregunte, cuál es la diferencia entre los índices de cubierta y los índices de columna sin clave, dado que ambas producen los mismos resultados? Buena pregunta, y ahora voy a decirle por qué usted podría querer considerar un índice de columna sin clave respecto de la utilización de un índice de cubierta. Las ventajas de ello son:

 Sí, estas son pequeñas diferencias, pero muchas pequeñas diferencias suman y pueden ayudar a aumentar el rendimiento de su servidor SQL Server. De hecho, puede querer repasar todos sus actualmente existentes índices de cubiertas y cambiarlos a índices de columnas sin clave no agrupado para ver si su rendimiento de ejecución de consultas aumenta. Dependiendo de las circunstancias, el rendimiento de verdad podría aumentar.

Las columnas sin clave se crean usando la cláusula INCLUDE de la declaración CREATE INDEX. Por ejemplo:

CREATE INDEX IX_Nombre_Tabla ON Table (Nombre_Índice_de_Columna_Clave) INCLUDE (Columna1, Columna2, Columna3)

 En el caso anterior, un índice no agrupado con una columna clave se crea con tres columnas sin clave.

¿Entonces, qué inconvenientes se deben a la utilización de índices de columnas sin clave no agrupado? En realidad, ellos son los mismos que para índices de cubierta, e incluyen: 

 Mientras lo anterior es también verdadero para los índices de cubierta, ellos pueden ser  ligeramente más problemáticos con índices no agrupados de columnas sin clave porque presentan el potencial de consumir más espacio que los índices de cubierta. Entonces, tiene que ser cuidadoso en sopesar los pros y los contras de los índices no agrupados de columnas sin clave, tal como siempre ha tenido que hacer con los índices de cubierta.

Para aprender más sobre cómo trabajan los índices no agrupados de columnas sin clave, primero identifique un índice de cubierta actual en su base de datos, mediante una consulta que se ejecute sobre él con frecuencia, y desde luego obteniendo las ventajas completas del índice de cubierta. Use Profiler para capturar estadísticas de rendimiento para la consulta durante un día. Entonces modifique el índice de cubierta existente para hacerlo un índice no agrupado de columnas sin clave. Después de ésto, use Profiler nuevamente para capturar las estadística de rendimiento para la misma consulta durante un día. Entonces, compare los dos y observe cuál opción de indexado trabaja mejor para usted.

Para aprender más sobre los pros y los contras de los índices no agrupados de columnas sin clave, mire Libros en Línea.


 ComentarioAbril 2009

de JESÚS, Sebastián: 39 01-31 91
GUASTI, Hernán: 39 01-31  90
PINTO, Damián: 39 01-31 97

 

Luego de analizar el documento, llegamos a la conclusión que la utilización de la nueva funcionalidad de SQL Server 2005 llamado “columna sin clave de índices no agrupado” es similar a la de índice de cubierta, solo que un poco más potente, pues difieren en algunas características, como por ejemplo: 

 Luego de ver algunas características comparativas, notamos que, en un principio, “columna sin claves en índices” podría reemplazar por completo al “índice de cubierta”, pero hay que tener en cuenta que los aspectos negativos del índice de cubierta se ven un poco maximizados en la nueva funcionalidad, como puede ser:

 Cabe aclarar que, al igual que con “índice de cubierta”, en la nueva funcionalidad solamente se podrá implementar un solo índice por cada tabla, por lo que hay que ser eficientes con los datos / columnas a seleccionar para el indexado, ya que la idea es ganar rendimiento en el funcionamiento de la base de datos, por lo que se deberá analizar detenidamente cada caso en particular.

A %d blogueros les gusta esto: