Mejora del rendimiento con índices amplios o Covered Indexes (Más información AQUI)
La noción de un índice en cuestión es la de SQL Server no necesita utilizar las búsquedas entre el índice no agrupado y la tabla para devolver los resultados de la consulta. Debido a que un índice agrupado de la tabla actual, los índices agrupados siempre cubren las consultas.
Tener en cuenta que el índice para abarcar/cubrir, debe contener todas las columnas de referencia en la consulta (en cualquier cláusula SELECT, JOIN, WHERE, GROUP BY, HAVING,y así sucesivamente). Considere la siguiente tabla de SQL y consulta
SELECT Column1 FROM Test.TableA
WHERE Column2 = 1;
Si queremos hacer un índice para cubrir esta consulta, debe contener al menos las columnas de la columna 1 y Columna2. Usted puede hacer esto de varias maneras. Todos los siguientes índices que cubren esta consulta:
CREATE NONCLUSTERED INDEX TestIndex ON Test.TableA (Col1, Col2);
CREATE NONCLUSTERED INDEX TestIndex ON Test.TableA (Col2, Col1);
CREATE NONCLUSTERED INDEX TestIndex ON Test.TableA (Col1) INCLUDE (Col2);
CREATE NONCLUSTERED INDEX TestIndex ON Test.TableA (Col2) INCLUDE (Col1);
CREATE NONCLUSTERED INDEX TestIndex ON Test.TableA (Col1, Col2, Col3);
CREATE NONCLUSTERED INDEX TestIndex ON Test.TableA (Col3) INCLUDE (Col1, Col2);
Como puede ver, las columnas sólo tienen que encontrar en el índice, su posición y si se encuentran en la clave del índice. Por supuesto, tanto el plan de ejecución y el rendimiento de la puede diferir mucho entre estos índices, sin embargo, que todos cubren la consulta. La mejora en el rendimiento obtenido mediante el uso de un índice cubierto suele ser ideal para las consultas que devuelven un gran número de filas (una consulta no selectiva) y menor para las consultas que devuelven filas (una consulta selectiva).Recuerde que un pequeño número de filas es un término relativo, que podría significar 10 para una tabla con un par de cientos de filas y 1.000 para una tabla con millones de filas.
En esta sección se presenta una comparación de rendimiento de cuatro consultas.La tabla en la que las consultas se ejecutan en contra tiene el siguiente esquema y se rellena con 1.000.000 de filas:
CREATE TABLE Test.CoveredIndexTest (
Col1 INT NOT NULL ,Col2 NVARCHAR(2047) NOT NULL );
INSERT Test.CoveredIndexTest (Col1, Col2) VALUES (0, 'A lonely row...');
INSERT Test.CoveredIndexTest (Col1, Col2)
SELECT TOP(999999) message_id, text FROM sys.messages AS sm
CROSS JOIN (
SELECT TOP(15) 1 AS Col FROM sys.messages
) AS x;
En la máquina de prueba en este ejemplo, el tamaño de esta tabla es 27,377páginas (aproximadamente 213 megabytes). Además, tenga en cuenta que la tabla es un montón, es decir, que no tiene un índice agrupado definido en él. Las consultas y los índices utilizados en esta prueba tienen las definiciones que figuran en el siguiente código. Las métricas de rendimiento (medido en la página de lecturas)para las consultas se muestran en la siguiente tabla
--índices No Cubiertos:
CREATE NONCLUSTERED INDEX NonCovered ON Test.CoveredIndexTest (Col1);
--índices Cubiertos:
CREATE NONCLUSTERED INDEX Covered ON Test.CoveredIndexTest (Col1) INCLUDE (Col2);
-- Consulta 1:
-- Retorna solo una fila.
SELECT Col1, Col2 FROM Test.CoveredIndexTest
WHERE Col1 = 0;
-- Consulta 2:
-- retorna aproximadamente el 0.1% de las filas de la tabla.
-- (1,056 rows)
SELECT Col1, Col2 FROM Test.CoveredIndexTest
WHERE Col1 BETWEEN 1205 AND 1225;
-- Consulta 3:
-- retorna aproximadamente el 0.5% de las filas de la tabla..
-- (5,016 rows)
SELECT Col1, Col2 FROM Test.CoveredIndexTest
WHERE Col1 BETWEEN 1205 AND 1426;
-- Consulta 4 (non-selective):
-- retorna aproximadamente el 5 % de las filas de la tabla..
-- (50,028 rows)
SELECT Col1, Col2 FROM Test.CoveredIndexTest
WHERE Col1 BETWEEN 1205 AND 2298;
Tabla de rendimiento de las consultas de Lecturas lógicas
| Consulta 1 (1 sola fila) | Consulta 2 (selectiva) | Consulta 3 ( un poco Selectiva) | consulta 4 (no selectiva) | |
| Sin Índices | 29,141 pages | 29,141 pages | 29,141 pages | 29,141 pages |
| índice no cubiertos | 4pages | 1,703 pages | 5,099 pages | 46,647 pages |
| índice cubiertos | 3 pages | 43 pages | 142 pages | 1,346 pages |
La métrica de rendimiento que se muestra en esta tabla es el número de páginas de datos que SQL Server realiza durante la ejecución de la consulta (SET STATISTICS IO ON, logical reads). Tenga en cuenta que la consulta selectiva de llamadas (Consulta 2) devuelve 0,01 por ciento de las filas de la tabla. Para una tabla de este tamaño, esta cantidad asciende a 1.000 filas. Si usted está hablando con alguien sobre el número de filas que se ven afectadas por una consulta, y él o ella dice que el número es "sólo un pequeño porcentaje de la mesa", esto generalmente se traduce en un montón de registros.
Algunas de las conclusiones que podemos extraer de la prueba se dan aquí. (Esto es sólo en relación con el rendimiento de lectura, el rendimiento de escritura se describe más adelante.)
- Un índice de cubierto siempre se comporta mejor que un índice no cubiertos.
- Para consultas que devuelven un número muy limitado de filas, un índice no cubiertos también funciona muy bien.
- Para la consulta un poco selectivos (Consulta 3), el índice no cubiertos lee más de 34 veces más páginas que el índice de cobertura. En este caso, una consulta se considera selectivo por el optimizador cuando ésta se adecuaba a menos de aproximadamente 0,77 por ciento de la tabla.
En las versiones de SQL Server anteriores a SQL Server 2005, la creación de índices no agrupados cubiertos con frecuencia puede ser imposible debido a un índice podría contener no más de 16 columnas o sea no más de 900 bytes. Entre las nuevas características se incluye que la columna permite agregar columnas a un índice sin necesidad de agregarlos a la clave del índice. Columnas incluidas no puede ser utilizado para tareas tales como el filtrado o la clasificación, su único beneficio es la reducción de lecturas de páginas a través de consultas con índices cubiertos evitando consultas de tabla.
Un índice puede tener un máximo de 1.023 columnas, y una tabla puede tener un máximo de 1.024 columnas, por lo que es posible crear un índice no agrupado que cubra toda la tabla, que es casi como tener un segundo índice agrupado! Además, las columnas que utilizan uno de los tipos de datos de gran tamaño [VARCHAR(max), NVAR-CHAR(max), VARBINARY(max), XML,TEXT, NTEXT, and IMAGE] pueden ser incluidos en un índice que incluye una columna.
Sólo las columnas que se utilizan para el filtrado, agrupación o clasificación deben ser parte de la clave del índice, en todas las otras columnas incluidas en el índice deberían incluirse columnas. Además de permitir más columnas en el índice, incluir las columnas tienen otros beneficios. En la siguiente secuencia de comandos SQL,una tabla con 1.000.000 de filas se crea con dos índices. Un índice tiene todas las columnas de la clave del índice, mientras que el otro índice sólo tiene una columna en la clave (la que se filtra), y el resto de las columnas están incluidos. El ancho de cada fila en el índice es un poco más de 300 bytes. Esto puede sonar como una fila de índice muy amplia, pero este tipo de ancho no es infrecuente. Esto también se compensa por el hecho de que la tabla de prueba sólo contiene 1 millón de filas,para tablas más grandes, la anchura del índice no tiene por qué ser tan grande para hacer una diferencia de rendimiento. El siguiente script define (y llena) los objetos y los índices utilizados en los ejemplos siguientes:
CREATE TABLE Test.IncludedColumnsTest(
PKCol UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID()
PRIMARY KEY CLUSTERED
,Col1 INT IDENTITY NOT NULL ,Col2 CHAR(20) NOT NULL
,Col3 CHAR(20) NOT NULL ,Col4 CHAR(20) NOT NULL
,Col5 CHAR(20) NOT NULL ,Col6 CHAR(20) NOT NULL
,Col7 CHAR(20) NOT NULL ,Col8 CHAR(20) NOT NULL
,Col9 CHAR(20) NOT NULL ,Col10 CHAR(20) NOT NULL
,Col11 CHAR(20) NOT NULL ,Col12 CHAR(20) NOT NULL
,Col13 CHAR(20) NOT NULL ,Col14 CHAR(20) NOT NULL
,Col15 CHAR(20) NOT NULL ,Col16 CHAR(20) NOT NULL
);
INSERT Test.IncludedColumnsTest (Col2, Col3, Col4, Col5, Col6, Col7, Col8,
Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16)
SELECT TOP(1000000)
CAST(message_id AS CHAR(20)) AS Col2 ,CAST(message_id AS CHAR(20)) AS Col3
,CAST(message_id AS CHAR(20)) AS Col4 ,CAST(message_id AS CHAR(20)) AS Col5
,CAST(message_id AS CHAR(20)) AS Col6 ,CAST(message_id AS CHAR(20)) AS Col7
,CAST(message_id AS CHAR(20)) AS Col8 ,CAST(message_id AS CHAR(20)) AS Col9
,CAST(message_id AS CHAR(20)) AS Col10 ,CAST(message_id AS CHAR(20)) AS Col11
,CAST(message_id AS CHAR(20)) AS Col12 ,CAST(message_id AS CHAR(20)) AS Col13
,CAST(message_id AS CHAR(20)) AS Col14 ,CAST(message_id AS CHAR(20)) AS Col15
,CAST(message_id AS CHAR(20)) AS Col16
FROM sys.messages AS sm
CROSS JOIN (
SELECT TOP(15) 1 AS Col FROM sys.messages
) AS x;
CREATE NONCLUSTERED INDEX IncludedColumns ON Test.IncludedColumnsTest (Col1)
INCLUDE (Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12,
Col13, Col14, Col15, Col16);
CREATE NONCLUSTERED INDEX NoIncludedColumns ON Test.IncludedColumnsTest
(Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11,
Col12, Col13, Col14, Col15, Col16);
La siguiente tabla muestra algunas de las interesantes diferencias entre los índices con y sin columnas incluidas.
| Con Columnas Incluidas | Sin Columnas Incluidas | |
| Tamaño Total | 40,147 pages | 41,743 pages |
| Tamaño del nivel nonleaf del índice | 146 pages | 1,743 pages |
| Profundidad del Índice | tres Niveles (un nivel raíz, un nivel intermedio y un nivel de hoja) | cinco Niveles (un nivel raíz, tres niveles intermedios y un nivel de hoja) |
| Tamaño promedio de las filas en los niveles nonleaf del índice | 27 bytes | 327 bytes |
| Tamaño promedio de las filas del nivel leaf del índice | 321 bytes | 321 bytes |
Usted puede recuperar esta información de la función de gestión dinámicasys.dm_db_index_physical_stats ejecutando la siguiente consulta:
SELECT * FROM sys.dm_db_index_physical_stats(
DB_ID() ,OBJECT_ID('Test.IncludedColumnsTest')
,NULL ,NULL ,'DETAILED' ) AS a;
El tamaño total del índice se redujo sólo en un 4 por ciento debido a los niveles de la hoja de ambos índices contienen los mismos datos. Sin embargo, los niveles no hoja (nonleaf) del índice con columnas incluidas contienen sólo la columna en que está (punteros, más al siguiente nivel) clave del índice, mientras que, por el otro índice, todas las columnas son parte de la clave del índice, por lo que cada fila en el nivel no hoja más o menos del mismo tamaño que la del nivel de hoja.
La Tabla siguiente muestra la disposición de cada nivel de un índice de que no incluye columnas(NoIncludedColumns).
| Nivel | Contenido |
| Raíz (Root) | 1 página con 4 filas que apunta al siguiente nivel |
| Primer Nivel Intermedio | 4 páginas con un total de 72 filas que apunta al siguiente nivel |
| Segundo Nivel Intermedio | 70 páginas con un total de 1668 filas que apunta al siguiente nivel |
| Tercer Nivel Intermedio | 1668 páginas con un total de 40000 filas que apunta al siguiente nivel |
| Hoja de Nivel (Leaf level) | 40000 páginas con un total de 1000000 filas que apunta al siguiente nivel |
Y ahora veremos una la tabla que mostrara la disposición de cada nivel de un índice IncludedColumns.
Nivel | Contenido |
| Raíz (Root) | 1 página con 145 filas que apunta al siguiente nivel |
| Nivel Intermedio | 145 páginas con un total de 40003 filas que apunta al siguiente nivel |
| Hoja de Nivel (Leaf level) | 40003 páginas con un total de 1000000 filas que apunta al siguiente nivel |
Debido a las filas de las páginas no hoja (nonleaf ) del índice de nivel NoIncludedColumns son sustancialmente mayores que los del índice IncludedColumns, más páginas (y por tanto más niveles) son necesarios para crear el árbol de equilibrado para el índice. Debido a que el índice de NoIncludedColumns es de dos niveles (es decir, un 40 por ciento) más que el índice de IncludedColumns,cada búsqueda a través del índice NoIncludedColumns necesita dos páginas más lecturas para llegar al fondo del índice. Esto podría no parecer mucho, pero si el índice se utiliza para las búsquedas repetidas, como para las uniones o consultas muy frecuentes, los niveles adicionales ocasionan una degradación del rendimiento.
En la tabla posterior a las consultas que veremos, tres consultas de ejemplo se muestran los que se unen a una tabla llamada Test.OtherTable con la tabla Test.IncludedColumnsTest mediante diferentes índices. Tenga en cuenta que las sugerencias de índice [WITH (INDEX)] sólo se utilizan para obligar a SQL Server para utilizar el índice especificado en lugar del índice óptimo (lo que sería el índice IncludedColumns). Un nuevo índice llamado NotCovered se agrega para mostrar el comportamiento de un índice no agrupado que no cubre la consulta. El siguiente script define objetos adicionales y los índices requeridos por el ejemplo:
-- Crear el índice NotCovered.
CREATE NONCLUSTERED INDEX NotCovered ON Test.IncludedColumnsTest (Col1);
-- Crear y rellenar la tabla Test.OtherTable.
CREATE TABLE Test.OtherTable (
PKCol INT IDENTITY NOT NULL PRIMARY KEY
,Col1 INT NOT NULL);
INSERT Test.OtherTable (Col1)
SELECT Col1 FROM Test.IncludedColumnsTest;
| Consulta | Definición | Lecturas de Paginas |
| Consulta 1 Índice: IncludedColumns El plan de ejecución se muestra en la figura 1.1 | SELECT o.PKCol, i.Col2
FROM Test.OtherTable AS o
INNER JOIN
Test.IncludedColumnsTest AS i
WITH(INDEX(IncludedColumns))
ON o.Col1 = i.Col1
WHERE o.PKCol BETWEEN 1
AND 10000;
| 32,726 pages |
| Consulta 2 Índice: NoIncludedColumns El plan de ejecución se muestra en la Figura 1.2. | SELECT o.PKCol, i.Col2
FROM Test.OtherTable AS o
INNER JOIN
Test.IncludedColumnsTest AS i
WITH(INDEX(NoIncludedColumns))
ON o.Col1 = i.Col1
WHERE o.PKCol BETWEEN 1
AND 10000;
| 53,994 pages |
| Consulta 3 Índice: NotCovered El plan de ejecución se muestra en la Figura 1.3 | SELECT o.PKCol, i.Col2
FROM Test.OtherTable AS o
INNER JOIN
Test.IncludedColumnsTest AS i
WITH(INDEX(NotCovered))
ON o.Col1 = i.Col1
WHERE o.PKCol BETWEEN 1
AND 10000;
| 62,900 pages |
Figura 1.1:
Figura 1.2
Figura 1.3
- Consulta 1, con el índice de IncludedColumns, es la consulta con mejor desempeño,con 32.726 lecturas de página.
- Consulta 2, con el índice de NoIncludedColumns, usado 53.994 lecturas de página. Como puede ver, la diferencia en el número de lecturas de páginas entre los dos índices es más o menos lo mismo que la diferencia en los niveles de índice (40 por ciento).
- Consulta 3, con el índice NotCovered, es la consulta de peor desempeño, con 62.900 lecturas de páginas adicionales debido a las lecturas necesarias para recuperar los datos que no se ha encontrado en el índice de la tabla. (Nota los bucles anidados adicionales de operador JOIN en el plan de ejecución de la consulta 3)
Debido a que un índice agrupado es la tabla actual, la lectura del índice agrupado no da lugar a búsquedas. Por lo tanto, un índice agrupado en general, se debe definir en las columnas que con frecuencia se consultan y suelen regresar una gran cantidad de datos. El uso de un índice agrupado evita el problema de las búsquedas y obtener una gran cantidad de filas. Dos buenos candidatos para el índice agrupado son la columnas más frecuentemente consultados , como la clave foránea de la tabla (una búsqueda en una clave externa suele dar como resultado muchas filas) o la columna de fecha . (Las búsquedas Fecha generalmente retornan un gran número de filas también.)
Otra consideración importante a la hora de seleccionar la columna o columnas sobre la que crear el índice agrupado es que el tamaño de la clave del índice agrupado debe ser tan pequeña como sea posible. Si existe un índice agrupado en una tabla,todos los índices no agrupados en esa tabla utiliza la clave del índice agrupado como el puntero de fila del índice no agrupado en la tabla. Si un índice agrupado no existe, el identificador de fila se utiliza, que ocupa 8 bytes de almacenamiento en cada fila de cada índice no agrupado. Esto puede aumentar significativamente el tamaño del índice para las grandes mesas. Considere el siguiente escenario:
- Tiene una tabla con filas de 40 millones.
- La tabla tiene cinco índices no agrupados.
- La clave del índice agrupado es de 60 bytes. (Esto no es raro que cuando se tiene los índices agrupados que abarcan varias columnas.)
40.000.000 * 5 * 60 = 12.000.000.000 bytes (cerca de 12 gigabytes)
Si la clave del índice agrupado fueran cambiados a una sola columna con un tipo de datos más pequeño, como un número entero de una clave externa, cada puntero de fila sería sólo de 4 bytes. Debido a 4 bytes se añaden a todos los duplicados de la clave del índice agrupado para mantener la única forma interna, el tamaño de clave de índice agrupado que resulta realmente sería de 8 bytes. El tamaño total de todos los punteros de fila, entonces sería la siguiente:
40.000.000 * 5 * 8 = 1.600.000.000 bytes (cerca de 1,5 GB)
La reducción en almacenamiento necesario es superior a 10 GB.
El rendimiento de lectura vs el rendimiento de escritura
La adición de índices sólo ayuda a aumentar el rendimiento de lectura. El rendimiento de escritura suele ser degradado ya que los índices deben mantenerse al día con los datos de la tabla.
Si una tabla tiene cinco índices no agrupados definidos en él, un INSERT en la tabla es realmente seis INSERT: uno para la tabla y uno para cada índice. Lo mismo ocurre con DELETE. Con las instrucciones UPDATE, sólo los índices que contienen las columnas que son actualizadas por la sentencia debe ser tocados.
Cuando las claves de índice se actualizan, la fila en el índice debe ser movido a la posición apropiada en el índice (a menos que la actualización modifica los datos sólo en las columnas incluidas). El resultado es que la actualización se divide en un DELETE seguida de una inserción. Dependiendo de la fragmentación interna de las páginas del índice, esto podría causar divisiones de páginas.
Considere la siguiente prueba de rendimiento simple en la tabla que contiene Test.IndexInsertTest 1.000.000 de filas. En cada prueba, 10.000 filas se insertan. La tabla se recrea entre las pruebas. En primer lugar, el INSERT lo realiza con la tabla sin ningún índices no agrupados, entonces se realiza con un índice no agrupado, y, finalmente, se lleva a cabo con cinco índices no agrupados. El siguiente código establece la prueba:
CREATE TABLE Test.IndexInsertTest (
PKCol UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID()
PRIMARY KEY CLUSTERED ,Col1 INT NOT NULL
);
INSERT Test.IndexInsertTest (Col1)
SELECT TOP(1000000)
ROW_NUMBER() OVER (ORDER BY message_id) AS Col1
FROM sys.messages AS sm
CROSS JOIN (
SELECT TOP(15) 1 AS Col FROM sys.messages
) AS x;
-- Reconstruir el índice agrupado de la tabla..
ALTER INDEX ALL ON Test.OtherTable REBUILD;
-- Crear una tabla que contiene las filas utilizados para realizar las inserciones.
CREATE TABLE Test.OtherTable (
PKCol INT IDENTITY(100000,4) NOT NULL PRIMARY KEY
,OtherCol INT NOT NULL);
INSERT Test.OtherTable (OtherCol)
SELECT Col1 FROM Test.IncludedColumnsTest
WHERE Col1 BETWEEN 1 AND 10000;
La siguiente es la primera prueba, sin índices no agrupados definidos en la tabla.
El plan de ejecución de esta sentencia INSERT se muestra en la figura siguiente
INSERT Test.IndexInsertTest (Col1) SELECT PKCol FROM Test.OtherTable;
El costo estimado de la consulta para la instrucción INSERT en esta prueba fue de 0,88, y SQL Server tocó 32.085 páginas en el Test.IndexInsertTest mesa mientras se realiza el INSERT.
La siguiente es la segunda prueba, con un índice no agrupado definido en la tabla.
El plan de ejecución de esta sentencia INSERT se muestra después:
-- 1. Quitar y volver a crear la tabla Test.IndexInsertTest.
-- 2. Añadir un índice no agrupado.
CREATE NONCLUSTERED INDEX NCIdx1 ON Test.IndexInsertTest (Col1);
-- 3. Ejecutar la instrucción de inserción.
INSERT Test.IndexInsertTest (Col1)
SELECT PKCol FROM Test.OtherTable;
El costo estimado de la consulta para la instrucción INSERT en esta prueba fue de 1,58, y SQL Server tocó 64.902 páginas en la tabla Test.IndexInsertTest mientras se realiza el INSERT. Esto es aproximadamente el doble del costo y el doble del número de páginas en comparación con la prueba 1.
La siguiente es la tercera prueba, con cinco índices no agrupados definidos en la tabla.
El plan de ejecución de esta sentencia INSERT se muestra en la Figura posterior:
-- 1. Quitar y volver a crear la tabla Test.IndexInsertTest.
-- 2. Añadir 5 índices no agrupados.
CREATE NONCLUSTERED INDEX NCIdx1 ON Test.IndexInsertTest (Col1);
CREATE NONCLUSTERED INDEX NCIdx2 ON Test.IndexInsertTest (Col1);
CREATE NONCLUSTERED INDEX NCIdx3 ON Test.IndexInsertTest (Col1);
CREATE NONCLUSTERED INDEX NCIdx4 ON Test.IndexInsertTest (Col1);
CREATE NONCLUSTERED INDEX NCIdx5 ON Test.IndexInsertTest (Col1);
-- 3. Ejecutar la instrucción de inserción.
INSERT Test.IndexInsertTest (Col1)
SELECT PKCol FROM Test.OtherTable;
Como puede ver, el costo para la realización de los insertos se duplicaron con cada nuevo índice no agrupado. Sin embargo, en este caso, cada índice no agrupado es aproximadamente el mismo ancho que la propia tabla. Para las tablas típicas, los índices no agrupados son más estrechas que la tabla y no afectan al rendimiento (en porcentaje), en el mismo grado que en esta prueba.
Debido a que la relación entre lectura y escritura varía mucho entre los sistemas (e incluso las tablas), suele ser una buena idea para crear índices para optimizar el rendimiento de lectura y luego probar el efecto que tienen los índices creados en el rendimiento de escritura. En tanto que el rendimiento de escritura es aceptable (y tiene suficiente espacio en disco para manejar los índices creados), puede mantener los índices creados. Generalmente se recomienda también ejecutar una prueba de vez en cuando para comprobar que la lectura frente a la escritura del de la tabla no ha cambiado.
También debe tener en cuenta que tanto el UPDATE y DELETE se benefician de ciertos índices para localizar las filas de la tabla que necesitan actualizar o eliminar.
Utilizando columnas calculadas (Más información AQUI)
Una columna calculada es por lo general derivados de otras columnas en la misma tabla y se puede hacer referencia tanto al sistema y funciones definidas por el usuario en su definición. Para poder crear un índice en una columna calculada, se debe cumplir con algunos requisitos.
Mediante la definición de una columna calculada y la indexación de ella, es posible hacer las consultas que normalmente requeriría un índice o una exploración de tabla en lugar de utilizar una operación de búsqueda. Considere la siguiente consulta de pedidos de venta en la base de datos AdventureWorks. El plan de la consulta de ejecución se muestra en la figura después:
USE AdventureWorks;
-- En primer lugar crear un índice en la columna
-- OrderDate para soportar esta consulta.
CREATE NONCLUSTERED INDEX OrderDateIndex ON
Sales.SalesOrderHeader (OrderDate);
GO
SET STATISTICS IO ON;
SELECT
COUNT(*) FROM Sales.SalesOrderHeader
WHERE MONTH(OrderDate) = 5;
La imagen muestra el plan de ejecución real de la instrucción SELECT sin SARG.
Dado que la consulta no ha utilizado un SARG válido (la columna en la cláusula WHERE se utiliza en una expresión), el índice OrderDateIndex sólo se puede utilizar para escanear y no para la búsqueda. Para ser capaz de producir una búsqueda de índice, SQL Server debe mantener un índice del resultado de la llamada a la función, en este caso, MONTH(OrderDate).Usted puede hacer esto mediante la adición de una columna calculada a la tabla y la indexación de la columna de la siguiente manera (el plan de ejecución de la consultase muestra en la Figura después):
-- Agrego la columna.
ALTER TABLE Sales.SalesOrderHeader
ADD OrderMonth AS MONTH(OrderDate);
-- Crear un índice en la columna calculada.
CREATE NONCLUSTERED INDEX OrderMonthIndex
ON Sales.SalesOrderHeader (OrderMonth);
GO
SET STATISTICS IO ON;
-- Ejecutar la consulta y de referencia de la nueva columna.
SELECT COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderMonth = 5;
En la figura anterior vemos el plan de ejecución real de la instrucción SELECT que utiliza la columna calculada en la cláusula WHERE
Esta vez, la consulta realiza una operación de búsqueda en el índice de la columna calculada, resultando en sólo ocho lecturas de página. Dependiendo de la complejidad de la definición de columna de la consulta y de la columna calculada, el optimizador utiliza automáticamente el índice de la columna calculada sin que se este haciendo referencia a la columna calculada en la consulta. La siguiente consulta, por ejemplo,también se genera el plan de ejecución mostrado anteriormente.
Como puede ver, SQL Server utiliza el índice de la columna calculada sin tener una referencia a él en la consulta. Esta es una gran característica, ya que permite añadir columnas calculadas y e índices sin tener que cambiar las consultas en aplicaciones o procedimientos almacenados para utiliza del nuevo índice.
Además de utilizar columnas calculadas indizadas con llamadas a funciones,también puede utilizar columnas calculadas indizadas para proporcionar índices en diferentes colaciones. Tenga en cuenta que usted tiene la tabla Test.Person con el nombre de columna mediante la intercalación Latin1_General_CI_AI. Ahora quiere encontrar todas las filas a partir del carácter Ö. En Latin1_General, los puntos sobre las O son considerados los acentos, pero en otros idiomas, como alemán y sueco, Ö es un carácter diferente a O. Tenga en cuenta que la tabla suele ser consultada por los clientes de habla Inglés, que esperan obtener tanto O como Ö como resultado de una búsqueda como LIKE ‘Ö%’ y, en ocasiones por los clientes suecos que esperar para obtener sólo Ö vuelta de esa misma búsqueda. Debido a la tabla suele ser consultada por los clientes de habla Inglés, tiene sentido mantener la colación Latin1_General_CI_AI, y cuando los clientes suecos consultar la tabla,utilizar la palabra clave COLLATE para utilizar la clasificación Finnish_Swedish_CI_AI explícitamente. Revise la siguiente secuencia de comandos y consultas. Los planes de ejecución para las dos consultas en la siguiente secuencia de comandos se muestran en las figuras respectivas:
-- Crear y rellenar la tabla CREATE TABLE Test.ProductNames ( Name NVARCHAR(50) COLLATE Latin1_General_CI_AI ); INSERT Test.ProductNames (Name) VALUES ('Öl'); INSERT Test.ProductNames (Name) VALUES ('Olja'); INSERT Test.ProductNames (Name) VALUES ('Beer'); INSERT Test.ProductNames (Name) VALUES ('Oil'); CREATE CLUSTERED INDEX NameIndex ON Test.ProductNames (Name); GO -- Consulta 1 -- Consulta para todos los nombres de productos que comienzan -- con la letra Ö usando la colación por defecto.
SELECT Name FROM Test.ProductNames
WHERE Name LIKE 'Ö%';
Este es el resultado de la consulta 1:
Name
-------------
Oil
Öl
Olja
y el plan de ejecución
Ahora veamos como podría ser la segunda consulta:
-- Consulta 2 -- Consulta para todos los nombres de productos que comienzan con la -- letra Ö mediante la intercalación Finnish_Swedish_CI_AI.
SELECT Name FROM Test.ProductNames
WHERE Name LIKE 'Ö%' COLLATE Finnish_Swedish_CI_AI;
Este es el resultado de la consulta 2:
Name
-------------
Öl
y el plan de ejecución
Al comparar los planes de ejecución de consultas 1 y 2, se puede ver que en la Consulta 2 la comparación necesita utilizar una intercalación distinta de la de la columna (y por lo tanto, el índice), un recorrido de índice agrupado se utiliza en lugar de un índice buscar, como en la Consulta 1. Mediante la adición de una columna calculada indizada a esta tabla y especificando la intercalación Finnish_Swedish_CI_AS de esta columna (como se muestra en la siguiente ejemplo de código), SQL Server puede utilizar automáticamente que el índice de cambio.Tenga en cuenta que la consulta en sí no necesita cambiar, y que esta es una solución viable sólo si se utiliza un número relativamente bajo de colaciones, ya que estos índices deben ser almacenados y mantenidos, como todos los otros índices.
El plan de ejecución para la consulta en el siguiente script se muestra en la figura posterior:
-- Añadir una columna calculada con otra intercalación. ALTER TABLE Test.ProductNames ADD Name_Finnish_Swedish_CI_AI AS Name COLLATE Finnish_Swedish_CI_AI; -- Crear un índice en la columna calculada. CREATE NONCLUSTERED INDEX NameIndex2 ON Test.ProductNames (Name_Finnish_Swedish_CI_AI); GO -- Consulta para todos los nombres de productos que comienzan -- con la letra Ö mediante la intercalación Finnish_Swedish_CI_AI
-- sin especificar la columna calculada.
SELECT Name FROM Test.ProductNames
WHERE Name LIKE 'Ö%' COLLATE Finnish_Swedish_CI_AI;
Este es el resultado de esta consulta:
Name
-------------
Öl
y el plan de ejecución (El plan de ejecución real de la consulta mediante un índice intercalación alternativo)
Uso de las vistas indexadas
Una vista en una base de datos normal, es sólo una declaración SELECT que se pueden utilizar en otras declaraciones SELECT. Estas vistas no tienen especial impacto en el rendimiento. A partir de SQL Server 2000, puede crear uno o varios índices en una vista, siempre y cuando la vista cumpla con ciertos requisitos. Estos requisitos son bastante amplias y se puede encontrar AQUI . Al crear un índice en una vista, ésta se materializa. Esto significa que, en el sentido lógico, sigue siendo una vista, pero la vista realmente almacena los datos encontrados en la vista. (Vistas materializadas se explican en detalle en el capítulo 5.) Si se cambian los datos en las tablas en que se basa el punto de vista, la vista se actualiza automáticamente para reflejar dichos cambios.
La creación de vistas indizadas puede mejorar el rendimiento de lectura de las consultas. Un aspecto importante de las vistas indizadas es que, dependiendo de la edición de SQL Server, el optimizador puede detectar automáticamente y utilizar una vista indizada que satisface una consulta determinada, aunque a la vista indizada no se haga referencia en la consulta. Sin embargo, esto es sólo para SQL Server 2008 Enterprise Edition y Developer Edition.
El siguiente ejemplo muestra una consulta y su plan de ejecución (que se muestra en la Figura posterior), sin una vista indizada:
USE AdventureWorks;
SELECT p.Name, sod.OrderQty, soh.OrderDate
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON sod.ProductID = p.ProductID
INNER JOIN Sales.SalesOrderHeader AS soh
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.TerritoryID = 1;
junto a su plan de ejecución
El costo de la consulta anterior fue de 2,03. A continuación, una vista indizada se ha creado para optimizar la consulta y luego la misma consulta se ejecuta de nuevo. El plan de ejecución para esta consulta se muestra en la figura 6.17. El primer índice creados en una vista deberá materializar la vista completa, lo que significa que el índice resultante debe ser un índice agrupado. El primer índice también debe ser único (que es la razón por la SalesOrderDetailID columna ha sido añadido a la vista indizada del ejemplo):
CREATE VIEW Sales.ProductsSoldVw
WITH SCHEMABINDING
AS
SELECT soh.TerritoryID ,sod.SalesOrderDetailID
,p.Name ,sod.OrderQty ,soh.OrderDate
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON sod.ProductID = p.ProductID
INNER JOIN Sales.SalesOrderHeader AS soh
ON soh.SalesOrderID = sod.SalesOrderID
GO
CREATE UNIQUE CLUSTERED INDEX ProductsSoldVwIdx
ON Sales.ProductsSoldVw (TerritoryID, SalesOrderDetailID);
GO
SELECT p.Name,sod.OrderQty,soh.OrderDate
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON sod.ProductID = p.ProductID
INNER JOIN Sales.SalesOrderHeader AS soh
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.TerritoryID = 1;
y el plan de ejecución respectivo:
Esta vez (si está utilizando SQL Server 2008 Enterprise Edition o Developer), la consulta utiliza la vista indizada, a pesar de que no se hace referencia, y el costo de la consulta se reduce a 0,12. Después de haber creado el índice agrupado único en la vista, es posible crear nuevos índices no agrupados en la misma vista.
Si usted está usando alguna versión de SQL Server que no sea Enterprise Edition o Developer Edition, la consulta debe hacer referencia a la vista directamente, y debe agregar la sugerencia de optimizador CON (NOEXPAND) a la consulta:Análisis del uso de Índice
SELECT Name,OrderQty,OrderDate FROM Sales.ProductsSoldVw WITH(NOEXPAND) WHERE TerritoryID = 1;
Debido a que los índices de incurrir en un costo (para el espacio de almacenamiento y para mantenerlos al día cuando se ejecutan las sentencias DML), es importante hacer un seguimiento de los índices que están siendo usados en las aplicaciones. Si un índice no se usa nunca, lo más probable es que se puede quitar tanto para ahorrar espacio y reducir el costo de las operaciones de escritura. Sin embargo, tenga en cuenta que algunos índices se crean para un fin específico, por ejemplo, para optimizar los informes de salario mensual. Por lo tanto, usted debe tener cuidado al borrar los índices no utilizados o rara vez se utilizan. Cuando se quita un índice que casi no se usa, debe documentar sus acciones a fin de que el índice se redujo puede volver a crear si es necesario más adelante.
Puede consultar la vista de administración dinámica (DMV) de sys.dm_db_index_usage_stats para encontrar información del uso de índice. Las columnas de este punto de vista que son particularmente interesantes se muestran en la Tabla 6.9, se puede encontrar documentación de toda la tabla AQUI.
Nombre de columna | Tipo de datos | Descripción |
| database_id | smallint | Id. de la base de datos en la que se define la tabla o vista. |
| object_id | int | Id. de la tabla o vista en la que se define el índice. |
| index_id | int | Id. del índice. |
| user_seeks | bigint | Número de consultas de búsqueda realizadas por el usuario. |
| user_scans | bigint | Número de consultas de recorrido realizadas por el usuario. |
| user_lookups | bigint | Número de búsquedas de marcadores realizadas por consultas de usuario. |
user_updates | bigint | Número de consultas de actualización realizadas por el usuario. |
| last_user_seek | datetime | Hora en que el usuario realizó la última búsqueda. |
| last_user_scan | datetime | Hora en que el usuario realizó el último recorrido. |
| last_user_lookup | datetime | Hora de la última búsqueda del usuario. |
Por lo general, debe consultar esta vista para los índices que tienen valores bajos en la columna user_seeks o user_scans. Todos los valores en la vista sys.dm_db_index_usage_stats se restablecen siempre que el servicio se reinicia SQL Server. Los valores de una base de datos específicos se eliminan si la base de datos es separadas(detached ) o apagar. Tenga en cuenta que los índices que no se han utilizado desde la vista sys.dm_db_index_usage_stats se restableció no están incluidos en la vista.
Particionamiento
A partir de SQL Server 2005, puede optar por hacer tablas de particiones e índices de forma horizontal (es decir, por filas) en partes más pequeñas. El uso principal de esto es mejorar el desempeño de las importaciones, reduciendo el trabajo necesario para llevar a cabo por SQL Server al importar datos. ¿Por qué se reduce el trabajo necesario? Es mucho trabajo para SQL Server para mantener los índices cuando las filas se insertan, actualizan o eliminan. Por supuesto, esto también es válido cuando se realiza una importación. Como se ha visto, esto provoca una gran cantidad de fragmentación.
Si ha causado mucha fragmentación, es probable que se quiera reconstruir los índices después de la importación. Teniendo en cuenta esto, es más rápido borrar todos los índices, importar los datos, a continuación, volver a crear todos los índices. De esta manera, SQL Server no desperdiciara los recursos manteniendo los índices durante la importación. Aquí es donde realmente sobresale la partición. ¿Qué pasa si, en lugar de volver a crear índices para toda la tabla, solo se hacen para los datos que se insertan? El rendimiento de la importación mejoraría enormemente. Con la partición, se podría crear una nueva tabla sin ningún índice de que, importar los datos en la tabla, cree los índices de esta nueva tabla y, finalmente, añadir una nueva partición de la tabla principal.
El particionamiento también puede mejorar el rendimiento de la consulta, pero el rendimiento de consulta es mejor ayuda mediante índices en lugar de particiones.
Para ser capaz de utilizar tablas de particiones e índices, primero tiene que crear dos objetos: una función de partición y un esquema de partición. La función de partición, simplemente define los puntos (o más bien, los valores), donde cada partición termina. El esquema de partición en la que define el grupo de archivos de cada partición va, tenga en cuenta que también puede definir un grupo de archivos para mantener todas las particiones.
Funciones de partición
Funciones de partición se crean mediante la sentencia CREATE PARTITION FUNCTION.
Una función de partición es simplemente una lista de hasta 999 valores que definen a los divisores entre las particiones.
Usted puede decidir si los valores se interpretan como "menor o igual que" (<=) o "menor que" (<) al definir la función de partición, ya sea izquierda o derecha.
El siguiente código crea una función de partición se define como la izquierda. Las particiones resultantes se muestran en la tabla posterior:
CREATE PARTITION FUNCTION PF(INT)
AS RANGE LEFT
FOR VALUES (10, 20, 30);
Ahora considere el siguiente código, lo que genera prácticamente la misma función de partición, pero se define como la derecha en vez de la izquierda. Las particiones resultantes se muestran después:
CREATE PARTITION FUNCTION pf (INT)
AS RANGE RIGHT
FOR VALUES (10, 20, 30);
Esquemas de partición
Se define una función de partición utilizando el CREATE PARTITION SCHEME . El esquema de partición es un mapa simple entre las particiones de una partición en particular y grupos de archivos.
La razón para el uso de los diferentes grupos de archivos para diferentes particiones suele ser capaz de almacenar diferentes partes de una tabla sobre los diferentes tipos de dispositivos de almacenamiento, es posible que desee almacenar los datos más antiguos en los dispositivos más lentos a menor costo y los nuevos datos en los dispositivos más rápido pero más caros .
El siguiente ejemplo se crea un esquema de partición que asigne cada una de las particiones definidas en la tabla siguiente a su grupo propio archivo, como se puede ver, el grupo mismo archivo se puede utilizar para múltiples particiones:
CREATE PARTITION SCHEME PS
AS PARTITION PF TO (FG1, FG2, FG1, FG2);
Como otro ejemplo, el comando mostrado aquí crea un esquema de asignación de particiones todas las particiones del grupo de archivos principal:
CREATE PARTITION SCHEME PS
AS PARTITION PF ALL TO ([PRIMARY]);
Creando tablas de particiones
Después de haber creado la función de partición y esquema de partición, se pueden crear tablas e índices en el esquema de partición utilizando la cláusula ON de la sentencia CREATE TABLE y CREATE INDEX. A pesar de que puede tener una tabla en un esquema de particiones y sus índices en diferentes esquemas de partición (o uno en un esquema de partición y uno no), se recomienda que todos ellos se creen en el mismo esquema de partición para apoyar agregar y quitar particiones si fuera necesario, sin tener que cerrar todas las aplicaciones. Una tabla con todos los índices definidos en el mismo esquema de partición se dice que han "alineado las particiones".
Los índices no agrupados y No únicos se alinean automáticamente con el esquema de partición de la tabla cuando se crea, es decir, que ni siquiera tener necesidad de especificar la cláusula ON para la instrucción CREATE INDEX.
Para índices únicos, sin embargo, debe incluir la columna de partición en el índice de la clave para que sea alineado con la tabla. Normalmente, esto contradice el objetivo de tener un índice único. Por ejemplo, si desea agregar un índice único en la columna de número de Seguro Social en una tabla con particiones en la columna ID, usted tiene que hacer es un índice compuesto sobre los SSN y el ID para alinearlo. Por lo general, debe tener un índice único definido como no alineados, y si es necesario añadir o eliminar una partición, debe quitar el índice de no alineados, añadir o eliminar la partición, y luego volver a crear el índice no alineados. Ejemplo, si usted tiene cinco índices alineados y no alineados uno, sólo tiene que volver a crear el índice de no alineados, mientras que sin particiones, posiblemente tenga que volver a crear los seis índices.
Ahora vamos a ver un ejemplo del uso de particiones para mejorar el rendimiento de las importaciones. El lote siguiente, se crea una función de partición y esquema de partición, así como una tabla y un índice no agrupado definido en el esquema de partición. Después se crean los objetos, una instrucción INSERT se utiliza para rellenar la tabla con un inicial de 19.185 filas. Tenga en cuenta que a pesar de que la instrucción CREATE INDEX no hace uso de la cláusula ON para especificar el esquema de partición, el índice se crea en el esquema de partición. La última parte de la escritura es una consulta en la vista de catálogo sys.partitions, que devuelve el 8 particiones creadas por el script: 4 de la tabla (heap, index_id = 0) y 4 para el índice no agrupado (index_id = 2):
USE AdventureWorks;
CREATE PARTITION FUNCTION PFCustomerID (INT)
AS RANGE LEFT
FOR VALUES (5000, 10000, 15000);
CREATE PARTITION SCHEME PSCustomerID
AS PARTITION PFCustomerID ALL TO ([PRIMARY]);
CREATE TABLE Test.CustomersPartitioned (
CustomerID INT IDENTITY NOT NULL
,AccountNumber VARCHAR(50) NOT NULL
,ModifiedDate DATETIME2 NOT NULL
) ON PSCustomerID (CustomerID);
CREATE NONCLUSTERED INDEX AccountNumberIdx
ON Test.CustomersPartitioned (AccountNumber);
INSERT Test.CustomersPartitioned (AccountNumber, ModifiedDate)
SELECT AccountNumber, ModifiedDate FROM Sales.Customer;
SELECT index_id, partition_number, rows FROM sys.partitions
WHERE object_id = OBJECT_ID('Test.CustomersPartitioned')
ORDER BY index_id, partition_number;
Aquí están los resultados de la consulta en la vista de catálogo sys.partitions:
Para importar datos en la tabla Test.CustomersPartitioned mediante la adición de una nueva partición, es necesario definir la nueva partición. Para hacer eso, usted necesita encontrar el valor divisor siguiente para la función de partición y agregarlo a la función de partición. La siguiente consulta busca la división siguiente de la función de partición PFCustomerID:
SELECT MAX(CustomerID) AS MaxCustomerID FROM Test.CustomersPartitioned;
Aquí está el resultado, que muestra el valor divisor que usted necesita:
MaxCustomerID
-------------
19185
Ahora que tiene el valor de 19185, se puede definir el valor divisor próximo 19185.
Debido a que la función de partición se define como la izquierda, la cuarta partición contiene ahora el rango de 15001 a 19185 CustomerID y la nueva partición contiene todos CustomerIDs superior a 19.185. El siguiente script altera la función de partición y muestra la nueva lista de las particiones en la tabla:
ALTER PARTITION FUNCTION PFCustomerID()
SPLIT RANGE (19185);
SELECT index_id, partition_number, rows FROM sys.partitions
WHERE object_id = OBJECT_ID('Test.CustomersPartitioned')
ORDER BY index_id, partition_number;
Aquí están los resultados de la consulta en la vista de catálogo sys.partitions con la nueva partición:
El siguiente paso es la importación de los nuevos datos, que se lleva a cabo mediante la creación de una nueva tabla con exactamente el mismo esquema que la tabla principal y luego importar los nuevos datos en ella. Tenga en cuenta que si utiliza varios grupos de archivos, esta nueva tabla debe existir en el grupo de archivos igual que la partición que desea convertir. El siguiente script crea la tabla, las importaciones de los datos, y crea el índice agrupado en la tabla. (Si usted tuviera todas las restricciones, como una clave primaria, una restricción CHECK o de clave externa, estas también hay que añadirlas antes de agregar la nueva tabla a la tabla de particiones.) Una advertencia con el índice no agrupado es que se deben incluir en la columna que está particionando la tabla de la definición del índice no agrupado para permitir que la nueva tabla que se añade a la tabla de particiones.Esto se hace automáticamente cuando la tabla ya tiene particiones, pero no para la nueva tabla, ya que simplemente no está dividido, en este momento, todavía es sólo una tabla normal:
-- Nueva tabla vacía.
CREATE TABLE Test.NewCustomers (
CustomerID INT IDENTITY(19186, 1) NOT NULL
,AccountNumber VARCHAR(50) NOT NULL
,ModifiedDate DATETIME2 NOT NULL);
-- Importación en la tabla vacía, tenga en cuenta que no hay índices
-- que necesiten ser mantenidos aun!
INSERT Test.NewCustomers (AccountNumber, ModifiedDate)
SELECT TOP(3000) AccountNumber, ModifiedDate
FROM Sales.Customer;
-- Ahora se crea el índice (y todas las restricciones necesarias).
CREATE NONCLUSTERED INDEX AccountNumberIdx
ON Test.NewCustomers (AccountNumber) INCLUDE (CustomerID);
Para finalmente agregar la tabla Test.NewCustomers de la tabla con particiones en la partición 5, se debe definir una restricción de comprobación en lo que garantiza que coincida con los valores permitidos para la partición de 5 (valores superiores a 19.185). El script siguiente se agrega la restricción CHECK y luego se cambia la nueva tabla con la partición actual 5 (que está vacía) en la tabla de particiones, y la partición actual 5 se convierte en la tabla vacía Test.NewCustomers, que luego se puede quitar:
-- Añadir la restricción CHECK.
ALTER TABLE Test.NewCustomers ADD CHECK(CustomerID > 19185);
-- Alternar entre la nueva tabla y la partición 5 en la tabla con particiones.
ALTER TABLE Test.NewCustomers SWITCH TO Test.CustomersPartitioned PARTITION 5;
-- Finalmente eliminar la tabla Test.NewCustomers.
DROP TABLE Test.NewCustomers;
-- Terminado:
SELECT index_id, partition_number, rows FROM sys.partitions
WHERE object_id = OBJECT_ID('Test.CustomersPartitioned')
ORDER BY index_id, partition_number;
Aquí están los resultados de la consulta en la vista de catálogo sys.partitions, mostrando el número de filas que se han añadido a la nueva partición:
¿Por qué es esta importación es mucho mejor ahora de lo que fue sin las particiones? En primer lugar, consideramos que sólo las filas importadas necesitan ser manipulado durante la operación, las filas existentes nunca fueron tocadas. Además,la tabla con particiones estaba mucho más disponible para los usuarios, ya que fue inaccesible sólo durante la ejecución de la sentencia ALTER TABLE. . .SWITCH. Debido a que ALTER TABLE. . . SWITCH. . . sólo cambia los punteros en el catálogo del sistema, se ejecuta prácticamente en muy poco tiempo,sin importar el número de filas que están siendo añadidos.
Así pues, usted puede mejorar potencialmente el rendimiento de la importación una gran cantidad mediante la aplicación de partición. Pero, ¿qué rendimiento de las consultas? Bueno, el particionamiento también puede mejorar el rendimiento de las consultas, sobre todo si la columna de partición es la que muchas veces se consulta. Si es así, SQL Server puede hacer la eliminación de particiones durante la optimización de lo que tiene que hacer buscar u operaciones de exploración sólo en determinadas particiones en lugar de en toda la tabla. Sin embargo, comparando el rendimiento de la consulta de una tabla con particiones sin un índice apropiado contra una tabla sin particiones con un índice adecuado, se ve que el índice se comporta mucho mejor que la partición (sin índice). La siguiente consulta se ejecuta en el esquema y las filas (originalmente las 19.185 filas) se define en el ejemplo anterior sobre la partición, y los resultados se muestran en la Tabla después de la consulta. La consulta está diseñado de manera que es necesario analizar sólo dos de las particiones de la tabla:
SET STATISTICS IO ON; SELECT COUNT(*) FROM Test.CustomersPartitioned WHERE CustomerID BETWEEN 1000 AND 10000;
Tenga en cuenta que cuando no hay un índice disponible, la consulta en la tabla con particiones se comporta mejor. Sin embargo, cuando el índice está disponible, la consulta en la tabla sin particiones se comporta mejor. ¿Por qué la consulta en la tabla sin particiones con un índice de un mejor desempeño que la consulta en la tabla con particiones con un índice? Debido a que en la tabla de particiones, la consulta realiza una operación de búsqueda en contra de dos índices (uno para cada partición), mientras que la consulta en la tabla sin particiones realiza una operación de búsqueda contra un solo índice.
Optimización automática de índices
Además de poder ajustar los índices en forma manual, SQL Server proporciona otras formas que le ayudarán a elegir la solución para la correcta indexación de una consulta específica. Al ver el plan de ejecución gráfico, puede ver una nota sobre un "Missing Index." En este caso, puede hacer clic en la nota índice que falta, lo que le permite recuperar la secuencia de comandos necesarios para crear el índice que falta. Un agregado de los índices que faltan y que el optimizador ha necesitado se pueden encontrar en las vistas de gestión de bases (DMV) sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups y sys.dm_db_missing_index_group_stats
También puede utilizar la utilidad gráfica “DTA” (Database Engine Tuning Advisor) para obtener información sobre los índices, vistas indizadas y particiones, incluso soluciones que pueden mejorar el rendimiento de la consulta. El DTA se puede ejecutar en contra de un guión que contenga consultas que necesiten ajuste o en contra de un archivo de traza de SQL Server que contiene una carga de trabajo de las consultas que necesitan ajuste.
Resumen de la lección
- Índices suelen ayudar a un rendimiento de lectura, pero puede perjudicar el rendimiento de escritura.
- Las vistas indizadas pueden aumentar el rendimiento incluso más que los índices,pero son restrictivas y por lo general no se pueden crear para la consulta completa.
- La decisión sobre qué columnas para poner en la clave del índice y que debe ser aplicado en las columnas incluidas es importante.
- Analizar los índices que están siendo usados y eliminar los que no. Esto ahorra espacio de almacenamiento y reduce al mínimo los recursos usados para mantener los índices de las operaciones de escritura.
No hay comentarios:
Publicar un comentario