Guía de diseño y arquitectura de índices

Se aplica a:SQL ServerAzure SQL DatabaseInstancia administrada de Azure SQLBase de datos SQL en Microsoft Fabric

Diseñar índices eficaces es clave para lograr un buen rendimiento de la base de datos y la aplicación. Una falta de índices, sobreindización o índices mal diseñados son fuentes principales de problemas de rendimiento de la base de datos.

En esta guía se describe la arquitectura y los aspectos básicos del índice, y se proporcionan procedimientos recomendados para ayudarle a diseñar índices eficaces para satisfacer las necesidades de las aplicaciones.

Para obtener más información sobre los tipos de índice disponibles, consulte Índices.

En esta guía se tratan los siguientes tipos de índices:

Formato de almacenamiento principal	Tipo de índice
Repositorio de filas basado en disco
	Clustered
	Nonclustered
	Unique
	Filtered
Columnstore
	Almacén de columnas agrupado
	Almacén de columnas no agrupado
Memory-optimized
	Hash
	Optimizado para memoria no agrupado

Para obtener información sobre los índices XML, vea Índices XML (SQL Server) e Índices XML selectivos (SXI).

Para más información sobre los índices espaciales, vea Información general sobre los índices espaciales.

Para obtener más información sobre los índices de texto completo, vea Rellenar índices de texto completo.

Conceptos básicos del índice

Piense en un libro corriente: al final del libro hay un índice en el que puede localizar rápidamente la información del libro. El índice es una lista ordenada de palabras clave y, junto a cada palabra clave, hay un conjunto de números de página que redirigen a las páginas en las que aparece cada palabra clave.

Un índice de almacén de filas es similar: es una lista ordenada de valores y para cada valor hay punteros a las páginas de datos donde se encuentran estos valores. El propio índice también se almacena en páginas, denominadas páginas de índice. En un libro normal, si el índice abarca varias páginas y necesita encontrar referencias a todas las páginas que contienen la palabra SQL, por ejemplo, tendría que hojear desde el inicio del índice hasta localizar la página de índice que contiene la palabra clave SQL. Desde allí, seguiría los punteros a todas las páginas del libro. Esto se podría optimizar aún más si al principio del índice se crea una sola página que contiene una lista alfabética de dónde se puede encontrar cada letra. Por ejemplo: "De la A a la D - página 121", "De la E a la G - página 122", y así sucesivamente. Esta página adicional eliminaría el paso de tener que pasar las páginas por el índice para encontrar la posición de inicio. Esta página no existe en los libros corrientes, pero sí en los índices de almacén de columnas. Esta página se denomina "página raíz" del índice. La página raíz es la página inicial de la estructura de árbol que se usa en un índice de almacén de columnas. Siguiendo la analogía del árbol, las páginas finales que contienen punteros a los datos reales se conocen como "páginas hoja" del árbol.

Un índice es una estructura en disco o en memoria asociada con una tabla o vista que acelera la recuperación de filas de la tabla o vista. Un índice de almacén de filas contiene claves creadas a partir de los valores de una o varias columnas de la tabla o vista. En el caso de los índices de almacén de filas, estas claves se almacenan en una estructura de árbol (árbol B+) que permite al motor de base de datos encontrar las filas asociadas a los valores de clave de forma rápida y eficaz.

Un índice de almacén de filas almacena los datos organizados lógicamente como una tabla con filas y columnas, y se almacena físicamente en un formato de datos de fila denominado almacén de filas¹. Hay una manera alternativa de almacenar datos en columnas, denominada columnstore.

El diseño de los índices adecuados para una base de datos y su carga de trabajo es un acto de equilibrio complejo entre la velocidad de consulta, el costo de actualización de índices y el costo de almacenamiento. Los índices de almacén de filas basados en disco estrechos o los índices con pocas columnas de la clave de índice requieren menos espacio de almacenamiento y una sobrecarga de actualización más pequeña. Por otro lado, los índices amplios podrían mejorar un mayor número de consultas. Es posible que tenga que experimentar con varios diseños diferentes antes de encontrar el conjunto más eficaz de índices. A medida que evoluciona la aplicación, es posible que los índices necesiten cambiar para mantener un rendimiento óptimo. Los índices se pueden agregar, modificar y quitar sin afectar al diseño de la aplicación o el esquema de la base de datos. Por lo tanto, no debe dudar en experimentar con índices diferentes.

Normalmente, el optimizador de consultas del motor de base de datos elige los índices más eficaces para ejecutar una consulta. Para ver qué índices usa el optimizador de consultas para una consulta específica, en SQL Server Management Studio, en el menú Consulta , seleccione Mostrar plan de ejecución estimado o Incluir plan de ejecución real.

No equipare siempre la utilización de índices con un buen rendimiento ni el buen rendimiento al uso eficaz del índice. Si la utilización de un índice contribuyera siempre a producir el mejor rendimiento, el trabajo del optimizador de consultas sería muy sencillo. En realidad, una elección incorrecta de índice puede provocar un rendimiento bajo. Por lo tanto, la tarea del optimizador de consultas es seleccionar un índice o una combinación de índices, solo cuando mejora el rendimiento y evitar la recuperación indizada cuando dificulta el rendimiento.

Un error de diseño común es crear muchos índices especulativamente para "dar las opciones del optimizador". La sobreindexación resultante ralentiza las modificaciones de datos y puede causar problemas de simultaneidad.

¹ Los almacenes de filas han sido la forma tradicional de almacenar los datos de una tabla relacional. Rowstore hace referencia a una tabla en la que el formato de almacenamiento de datos subyacente es un montón, un árbol B+ (índice agrupado) o una tabla optimizada para memoria. Almacén de filas basado en disco excluye las tablas optimizadas para memoria.

Tareas de diseño de índices

Las tareas siguientes componen la estrategia recomendada para el diseño de índices:

1. Comprenda las características de la base de datos y la aplicación.

   Por ejemplo, en una base de datos de procesamiento de transacciones en línea (OLTP) con modificaciones frecuentes de datos que deben mantener un alto rendimiento, algunos índices de almacén de filas estrechos destinados a las consultas más críticas serían un buen diseño inicial de índice. Para un rendimiento de procesamiento extremadamente alto, considere las tablas e índices optimizados para memoria, que proporcionan un diseño sin bloqueos y cerraduras. Para obtener más información, consulte Directrices de diseño de índices no agrupados optimizados para memoria y Directrices de diseño de índices hash en esta guía.

   Por el contrario, para una base de datos de análisis o almacenamiento de datos (OLAP) que debe procesar conjuntos de datos muy grandes rápidamente, el uso de índices de almacén de columnas agrupados sería especialmente adecuado. Para obtener más información, consulte Índices de almacén de columnas: información general o arquitectura de índices de almacén de columnas en esta guía.

2. Comprenda las características de las consultas usadas con más frecuencia.

   Por ejemplo, saber que una consulta usada con frecuencia combina dos o más tablas le ayuda a determinar el conjunto de índices para estas tablas.

3. Comprenda la distribución de datos en las columnas usadas en los predicados de consulta.

   Por ejemplo, un índice puede ser útil para las columnas con muchos valores de datos distintos, pero menos para las columnas con muchos valores duplicados. En el caso de las columnas con muchos NUL o aquellos que tienen subconjuntos bien definidos de datos, puede usar un índice filtrado. Para obtener más información, vea Directrices para diseñar índices filtrados en esta guía.

4. Determine qué opciones de índice pueden mejorar el rendimiento.

   Por ejemplo, la creación de un índice agrupado en una tabla grande existente podría beneficiarse de la ONLINE opción de índice. La opción ONLINE permite que la actividad simultánea en los datos subyacentes continúe mientras el índice se crea o regenera. El uso de la compresión de datos de fila o página puede mejorar el rendimiento reduciendo la superficie de E/S y memoria del índice. Para obtener más información, vea CREATE INDEX.

5. Examine los índices existentes en la tabla para evitar la creación de índices duplicados o muy similares.

   A menudo es mejor modificar un índice existente que crear un índice nuevo pero principalmente duplicado. Por ejemplo, considere la posibilidad de agregar una o dos columnas adicionales incluidas a un índice existente, en lugar de crear un nuevo índice con estas columnas. Esto es especialmente relevante cuando se ajustan los índices no agrupados con sugerencias de índices faltantes, o si usa el Asesor de optimización del motor de base de datos, donde podrían ofrecerse variaciones similares de índices en la misma tabla y columnas.

Directrices generales para el diseño de índices

Comprender las características de las columnas de base de datos, consultas y tablas puede ayudarle a diseñar índices óptimos inicialmente y modificar el diseño a medida que evolucionan las aplicaciones.

Consideraciones sobre la base de datos

Cuando diseñe un índice, tenga en cuenta las siguientes directrices acerca de la base de datos:

• Un gran número de índices en una tabla afecta al rendimiento de las instrucciones INSERT, UPDATE, DELETE y MERGE porque los datos de los índices pueden tener que cambiar a medida que cambian los datos de la tabla. Por ejemplo, si una columna se usa en varios índices y ejecuta una UPDATE instrucción que modifica los datos de esa columna, también se debe actualizar cada índice que contenga esa columna.

  • Evite crear demasiados índices en tablas que se actualizan con mucha frecuencia y mantenga los índices estrechos, es decir, defínalos con el menor número de columnas posible.

  • Puede tener más índices en tablas que tengan pocas modificaciones de datos, pero grandes volúmenes de datos. Para estas tablas, una variedad de índices puede ayudar a consultar el rendimiento mientras la sobrecarga de actualización del índice sigue siendo aceptable. Sin embargo, no cree índices especulativamente. Supervise el uso del índice y quite los índices sin usar a lo largo del tiempo.

• Es posible que la indexación de tablas pequeñas no sea óptima porque el motor de base de datos puede tardar más en recorrer el índice buscando datos que en realizar un escaneo de la tabla base. Por lo tanto, es posible que nunca se usen índices en tablas pequeñas, pero deben actualizarse a medida que se actualizan los datos de la tabla.

• Los índices de las vistas pueden proporcionar mejoras de rendimiento significativas cuando la vista contiene agregaciones o combinaciones. Para obtener más información, consulte Creación de vistas indizadas.

• Las bases de datos de las réplicas principales de Azure SQL Database generan automáticamente recomendaciones de rendimiento del asesor de bases de datos para los índices. Opcionalmente, puede habilitar el ajuste automático de índices.

• El Almacén de consultas ayuda a identificar las consultas con un rendimiento poco óptimo y proporciona un historial de planes de ejecución de consultas que le permiten ver los índices seleccionados por el optimizador. Puede usar estos datos para hacer que los cambios de optimización de índices sean más impactantes al centrarse en las consultas que consumen recursos y más frecuentes.

Consideraciones de consulta

Cuando diseñe un índice, tenga en cuenta las siguientes directrices acerca de las consultas:

• Cree índices no agrupados en las columnas que se usan con frecuencia en predicados y expresiones de combinación en consultas. Estas son las columnas sargables . Sin embargo, debe evitar agregar columnas innecesarias a los índices. Agregar demasiadas columnas de índice puede afectar negativamente al espacio en disco y al rendimiento de la actualización del índice.

  El término SARGable en bases de datos relacionales hace referencia a un predicadocapaz de Search ARGque puede usar un índice para acelerar la ejecución de la consulta. Para obtener más información, consulte SQL Server y la guía de diseño y arquitectura de índices de Azure SQL.

  Tip

  Asegúrese siempre de que la carga de trabajo de consulta usa realmente los índices que cree. Quite los índices sin usar.

  Las estadísticas de uso de índices están disponibles en sys.dm_db_index_usage_stats y sys.dm_db_index_operational_stats.

• La utilización de índices puede mejorar el rendimiento de las consultas, ya que los datos necesarios para satisfacer las necesidades de la consulta existen en el propio índice. Es decir, solo se requieren las páginas de índice, y no las páginas de datos de la tabla o el índice clúster, para recuperar los datos solicitados; por lo tanto, se reduce la E/S de disco global. Por ejemplo, una consulta de las columnas A y B en una tabla que tiene un índice compuesto creado en las columnas A, B y C puede recuperar los datos especificados del índice.

  Note

  Un índice de cobertura es un índice no agrupado que satisface todo el acceso a datos por una consulta directamente sin tener acceso a la tabla base.

  Estos índices tienen todas las columnas SARGable necesarias en la clave del índice, y las columnas Non-SARGable como columnas incluidas. Esto significa que todas las columnas necesarias para la consulta, ya sea en las WHEREcláusulas , JOINy GROUP BY , o en las SELECT cláusulas o UPDATE , están presentes en el índice.

  Es posible que haya considerablemente menos E/S para ejecutar la consulta, si el índice es lo suficientemente estrecho en comparación con las filas y columnas de la propia tabla; es decir, si constituye un pequeño subconjunto de todas las columnas.

  Considere la posibilidad de crear índices al recuperar una pequeña parte de una tabla grande, donde esa pequeña porción está definida por un predicado fijo.

  Evite crear un índice de cobertura con demasiadas columnas porque disminuye su ventaja al inflar el almacenamiento de la base de datos, la E/S y la superficie de memoria.

• Escriba consultas que inserten o modifiquen tantas filas como sea posible en una sola instrucción, en lugar de utilizar varias consultas para actualizar las mismas filas. Esto reduce la sobrecarga de actualización del índice.

Consideraciones sobre columnas

Cuando diseñe un índice, tenga en cuenta las siguientes directrices acerca de las columnas:

• Mantenga la longitud de la clave de índice corta, especialmente para los índices agrupados.

• Las columnas que son de los tipos de datos ntext, text, image, varchar(max), nvarchar(max), varbinary(max), json y vector no se pueden especificar como columnas de clave de índice. Sin embargo, las columnas con estos tipos de datos se pueden agregar a un índice no clúster como columnas de índice sin clave (incluidas). Para obtener más información, consulte la sección Uso de columnas incluidas en índices no clúster en esta guía.

• Examine la unicidad de las columnas. Un índice único en lugar de un índice no único en las mismas columnas de clave proporciona información adicional para el optimizador de consultas que hace que el índice sea más útil. Para obtener más información, vea Directrices para diseñar índices únicos en esta guía.

• Examine la distribución de los datos en la columna. Crear un índice en una columna con muchas filas pero pocos valores distintos podría no mejorar el rendimiento de las consultas, aunque el optimizador de consultas use el índice. Como analogía, un directorio telefónico físico ordenado alfabéticamente en nombre de familia no acelera la localización de una persona si todas las personas de la ciudad se denominan Smith o Jones. Para obtener más información acerca de la distribución de datos, vea Statistics.

• Considere la posibilidad de usar índices filtrados en columnas que tienen subconjuntos bien definidos, por ejemplo, columnas con muchas NUL, columnas con categorías de valores y columnas con distintos intervalos de valores. Un índice filtrado bien diseñado puede mejorar el rendimiento de las consultas, reducir los costos de actualización de índices y reducir los costos de almacenamiento almacenando un pequeño subconjunto de todas las filas de la tabla si ese subconjunto es relevante para muchas consultas.

• Tenga en cuenta el orden de las columnas de clave de índice si la clave contiene varias columnas. La columna que se usa en el predicado de consulta en una expresión de igualdad (=), expresión de desigualdad (>,>=,<,<=) o BETWEEN expresión, o que participa en una combinación, debe colocarse primero. Las demás columnas deben ordenarse basándose en su nivel de diferenciación, es decir, de más distintas a menos distintas.

  Por ejemplo, si el índice se define como LastName, FirstNameel índice es útil cuando el predicado de consulta de la WHERE cláusula es WHERE LastName = 'Smith' o WHERE LastName = Smith AND FirstName LIKE 'J%'. Sin embargo, el optimizador de consultas no usaría el índice para una consulta que solo buscaba en WHERE FirstName = 'Jane'o el índice no mejoraría el rendimiento de dicha consulta.

• Considere la posibilidad de indexar columnas calculadas si se incluyen en predicados de consulta. Para obtener más información, vea Índices en columnas calculadas.

Características de índice

Después de determinar que un índice resulta adecuado para una consulta, puede seleccionar el tipo de índice que mejor se ajusta a la situación. Entre las características de índice se incluyen las siguientes:

• Agrupado o no agrupado
• Único o no único
• Columna única o multicolumna
• Orden ascendente o descendente para las columnas de clave del índice
• Todas las filas o solo las filtradas para índices de tipo no agrupado
• Almacén de columnas o almacén de filas
• Hash o no agrupado para tablas optimizadas para memoria

Colocación de índices en grupos de archivos o esquemas de particiones

Al desarrollar la estrategia de diseño del índice, debe tener en cuenta la ubicación de los índices en los grupos de archivos asociados con la base de datos.

De forma predeterminada, los índices se almacenan en el mismo grupo de archivos que la tabla base (índice agrupado o montón) en la que se crea el índice. Otras configuraciones son posibles, entre las que se incluyen:

• Cree índices no clúster en un grupo de archivos distinto del grupo de archivos de la tabla base.

• Crear particiones de índices clúster y no clúster repartidos en varios grupos de archivos.

En el caso de las tablas sin particiones, el enfoque más sencillo suele ser el mejor: crear todas las tablas en el mismo grupo de archivos y agregar tantos archivos de datos al grupo de archivos como sea necesario para usar todo el almacenamiento físico disponible.

Los enfoques de selección de ubicación de índices más avanzados se pueden tener en cuenta cuando el almacenamiento en capas está disponible. Por ejemplo, podría crear un grupo de archivos para tablas a las que se accede con frecuencia con archivos en discos más rápidos y un grupo de archivos para archivar tablas en discos más lentos.

Puede mover una tabla con un índice agrupado de un grupo de archivos a otro al eliminar el índice agrupado y especificar un nuevo grupo de archivos o esquema de partición en la cláusula MOVE TO de la instrucción DROP INDEX o utilizando la instrucción CREATE INDEX con la cláusula DROP_EXISTING.

Índices con particiones

También puede considerar la posibilidad de particionar montones basados en disco, índices agrupados e índices no agrupados en varios grupos de archivos. Los índices con particiones se particionan horizontalmente (por fila) en función de una función de partición. La función de partición define cómo se asigna cada fila a una partición en función de los valores de una columna determinada que designe, denominada columna de creación de particiones. Un esquema de partición especifica la asignación de un conjunto de particiones a un grupo de archivos.

La creación de particiones de un índice puede proporcionar las siguientes ventajas:

• Hacer que las bases de datos de gran tamaño sean más fáciles de administrar. Los sistemas OLAP, por ejemplo, pueden implementar ETL compatibles con particiones que simplifican considerablemente la adición y eliminación de datos de forma masiva.

• Haga que determinados tipos de consultas, como las consultas analíticas de ejecución prolongada, se ejecuten más rápido. Cuando las consultas usan un índice con particiones, el motor de base de datos puede procesar varias particiones al mismo tiempo y omitir (eliminar) particiones que la consulta no necesita.

Advertencia

La creación de particiones rara vez mejora el rendimiento de las consultas en los sistemas OLTP, pero puede introducir una sobrecarga significativa si una consulta transaccional debe tener acceso a muchas particiones.

Para obtener más información, vea Índices y tablas con particiones.

Directrices para diseñar el criterio de ordenación de los índices

Al definir índices, considere si cada columna de clave de índice debe almacenarse en orden ascendente o descendente. El valor predeterminado es ascendente. La sintaxis de las instrucciones CREATE INDEX, CREATE TABLE y ALTER TABLE admite las palabras clave ASC (ascendente) y DESC (descendente) en columnas individuales de índices y restricciones.

La especificación del orden en que se almacenan los valores de clave en un índice es de utilidad cuando las consultas que hacen referencia a la tabla tienen cláusulas ORDER BY que especifican distintas direcciones para las columnas de clave del índice. En estos casos, el índice puede eliminar la necesidad de un operador de Sort en el plan de consulta.

Por ejemplo, los compradores del departamento de compras de los ciclos de Adventure Works tienen que evaluar la calidad de los productos que compran a los proveedores. Los compradores están más interesados en encontrar productos enviados por proveedores con una alta tasa de rechazo.

Como se muestra en la siguiente consulta efectuada en la base de datos de muestra AdventureWorks, para recuperar los datos que cumplen estos criterios es necesario organizar la columna RejectedQty de la tabla Purchasing.PurchaseOrderDetail en orden descendente (de mayor a menor) y la columna ProductID en orden ascendente (de menor a mayor).

SELECT RejectedQty,
       ((RejectedQty / OrderQty) * 100) AS RejectionRate,
       ProductID,
       DueDate
FROM Purchasing.PurchaseOrderDetail
ORDER BY RejectedQty DESC, ProductID ASC;

El siguiente plan de ejecución para esta consulta muestra que el optimizador de consultas usó un operador Sort para devolver el conjunto de resultados en el orden especificado por la ORDER BY cláusula .

Si se crea un índice de almacén de filas basado en disco con columnas de clave que coinciden con las de la cláusula de la ORDER BY consulta, se elimina el operador Sort del plan de consulta, lo que hace que el plan de consulta sea más eficaz.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);

Una vez ejecutada la consulta de nuevo, el siguiente plan de ejecución muestra que el operador Sort ya no está presente y se usa el índice no clúster recién creado.

El motor de base de datos puede examinar un índice en cualquier dirección. Un índice definido como RejectedQty DESC, ProductID ASC se puede seguir usando para una consulta en la que se invierten las direcciones de ordenación de las columnas de la ORDER BY cláusula . Por ejemplo, una consulta con la ORDER BY cláusula ORDER BY RejectedQty ASC, ProductID DESC puede usar el mismo índice.

Solo se pueden especificar criterios de ordenación para la columnas de clave del índice. La vista de catálogo sys.index_columns informa de si una columna de índice se almacena en orden ascendente o descendente.

instrucciones de diseño de índices agrupados

El índice agrupado almacena todas las filas y todas las columnas de una tabla. Las filas se ordenan en el orden de los valores de clave de índice. Solo puede haber un índice agrupado por tabla.

El término tabla base puede hacer referencia a un índice agrupado o a un montón. Un montón es una estructura de datos no ordenada en el disco que contiene todas las filas y todas las columnas de una tabla.

Con algunas excepciones, cada tabla debe tener un índice agrupado. Las propiedades deseables del índice agrupado son:

Propiedad	Description
Estrechar	La clave de índice agrupada forma parte de cualquier índice no clúster en la misma tabla base. Una clave estrecha o una clave en la que la longitud total de las columnas de clave es pequeña, reduce la sobrecarga de almacenamiento, E/S y memoria de todos los índices de una tabla. Para calcular la longitud de la clave, agregue los tamaños de almacenamiento de los tipos de datos usados por las columnas de clave. Para obtener más información, consulte Categorías de tipos de datos.
Único	Si el índice agrupado no es único, se agrega automáticamente una columna uniqueifier interna de 4 bytes a la clave de índice para garantizar la unicidad. Agregar una columna única existente a la clave de índice agrupada evita la sobrecarga de almacenamiento, E/S y memoria de la columna uniqueifier en todos los índices de una tabla. Además, el optimizador de consultas puede generar planes de consulta más eficaces cuando un índice es único.
Cada vez mayor	En un índice cada vez mayor, los datos siempre se agregan en la última página del índice. Esto evita las divisiones de página en el centro del índice, lo que reduce la densidad de página y reduce el rendimiento.
Inmutable	La clave de índice agrupada forma parte de cualquier índice no clúster. Cuando se modifica una columna de clave de un índice agrupado, también se debe realizar un cambio en todos los índices no clúster, lo que agrega una sobrecarga de CPU, registro, E/S y memoria. La sobrecarga se evita si las columnas de clave del índice agrupado son inmutables.
Solo tiene columnas que no aceptan valores NULL	Si una fila tiene columnas que aceptan valores NULL, debe incluir una estructura interna denominada bloque NULL, que agrega 3-4 bytes de almacenamiento por fila en un índice. Hacer que todas las columnas del índice agrupado no sean anulables evita sobrecarga.
Solo tiene columnas de ancho fijo	Las columnas que usan tipos de datos de ancho variable, como varchar o nvarchar , usan un valor adicional de 2 bytes en comparación con los tipos de datos de ancho fijo. El uso de tipos de datos de ancho fijo, como int , evita esta sobrecarga en todos los índices de la tabla.

Satisfacer tantas de estas propiedades como sea posible al diseñar un índice agrupado hace que no solo el índice agrupado, sino también todos los índices no clúster de la misma tabla sean más eficaces. El rendimiento se mejora evitando las sobrecargas de almacenamiento, E/S y memoria.

Por ejemplo, una clave de índice agrupada con una sola columna int o bigint que no acepta valores NULL tiene todas estas propiedades si se rellena por una cláusula IDENTITY o una restricción predeterminada mediante una secuencia y no se actualiza después de insertar una fila.

Por el contrario, una clave de índice agrupada con una sola columna uniqueidentifier es más amplia porque usa 16 bytes de almacenamiento en lugar de 4 bytes para int y 8 bytes para bigint y no satisface la propiedad cada vez mayor a menos que los valores se generen secuencialmente.

Tip

Al crear una PRIMARY KEY restricción, se crea automáticamente un índice único que admite la restricción. De forma predeterminada, este índice está agrupado; Sin embargo, si este índice no satisface las propiedades deseadas del índice agrupado, puede crear la restricción como no agrupada y crear un índice agrupado diferente en su lugar.

Si no crea un índice agrupado, la tabla se almacena como un montón, que generalmente no se recomienda.

Arquitectura de los índices agrupados

Los índices de almacén de filas se organizan como árboles B+. Las páginas de un árbol B+ de índice se llaman nodos del índice. El nodo superior del árbol B+ se llama nodo raíz. Los nodos inferiores del índice se denominan nodos hoja. Los niveles del índice entre el nodo raíz y los nodos hoja se conocen en conjunto como niveles intermedios. En un índice clúster, los nodos hoja contienen las páginas de datos de la tabla subyacente. El nodo raíz y los nodos intermedios incluyen páginas de índice que contienen filas de índice. Cada fila de índice contiene un valor clave y un puntero a una página de nivel intermedio en el árbol B+, o bien a una fila de datos del nivel hoja del índice. Las páginas de cada nivel del índice se vinculan en una lista con vínculos dobles.

Los índices agrupados tienen una fila en sys.partitions para cada partición usada por el índice, con index_id = 1. De forma predeterminada, un índice clúster tiene una sola partición. Cuando un índice agrupado tiene varias particiones, cada partición tiene una estructura de árbol B+ independiente que contiene los datos de esa partición específica. Por ejemplo, si un índice agrupado tiene cuatro particiones, hay cuatro estructuras de árbol B+, una en cada partición.

En función de los tipos de datos del índice clúster, cada estructura de índice clúster tiene una o más unidades de asignación en las que almacenar y administrar los datos de una partición específica. Como mínimo, cada índice clúster tiene una unidad de asignación IN_ROW_DATA por partición. El índice agrupado también tiene una LOB_DATA unidad de asignación por partición si contiene columnas de objetos grandes (LOB), como nvarchar(max). También tiene una ROW_OVERFLOW_DATA unidad de asignación por partición si contiene columnas de longitud variable que superan el límite de tamaño de fila de 8060 bytes.

Las páginas de la estructura de árbol B+ se ordenan en el valor de la clave de índice agrupada. Todas las inserciones se realizan en la página donde el valor de clave de la fila insertada se ajusta a la secuencia de ordenación entre las páginas existentes. Dentro de una página, las filas no se almacenan necesariamente en ningún orden físico. Sin embargo, la página mantiene una ordenación lógica de filas mediante una estructura interna denominada matriz de ranuras. Las entradas de la matriz de ranuras se mantienen en el orden de clave de índice.

En esta ilustración se muestra la estructura de un índice clúster en una sola partición.

Directrices para diseñar índices no agrupados

La principal diferencia entre un índice agrupado y un índice no agrupado es que un índice no clúster contiene un subconjunto de las columnas de la tabla, normalmente ordenados de forma diferente del índice agrupado. Opcionalmente, se puede filtrar un índice no clúster, lo que significa que contiene un subconjunto de todas las filas de la tabla.

Un índice no agrupado basado en disco contiene los localizadores de filas que apuntan a la ubicación de almacenamiento de las filas en la tabla base. Se pueden crear varios índices no clúster en una tabla o una vista indizada. Por lo general, los índices no agrupados deben diseñarse para mejorar el rendimiento de las consultas usadas con frecuencia que necesitarían examinar la tabla base de lo contrario.

Al igual que cuando se utiliza un índice de un libro, el optimizador de consultas busca valores de datos en el índice no clúster para encontrar la ubicación del valor de datos en la tabla y, a continuación, recupera los datos directamente de esa ubicación. Este sistema convierte a los índices no clúster en la opción más apropiada para las consultas de coincidencia exacta, dado que el índice contiene entradas que describen la ubicación exacta en la tabla de los valores de datos que se buscan en las consultas.

Por ejemplo, para consultar la tabla HumanResources.Employee de todos los empleados que informan a un gerente específico, el optimizador de consultas podría usar el índice IX_Employee_ManagerID; este tiene ManagerID como su primera columna de clave. Dado que los ManagerID valores se ordenan en el índice no clúster, el optimizador de consultas puede encontrar rápidamente todas las entradas del índice que coinciden con el valor especificado ManagerID . Cada entrada de índice apunta a la página y fila exactas de la tabla base donde se pueden recuperar los datos correspondientes de todas las demás columnas. Una vez que el optimizador de consultas encuentra todas las entradas del índice, puede ir directamente a la página y fila exactas para recuperar los datos en lugar de examinar toda la tabla base.

Arquitectura de los índices no agrupados

Los índices no clúster de almacén de filas basados en disco tienen la misma estructura de árbol B+ que los índices agrupados, excepto las diferencias siguientes:

• Un índice no clúster no contiene necesariamente todas las columnas y filas de la tabla.

• El nivel hoja de un índice no agrupado está compuesto por páginas de índices, en lugar de páginas de datos. Las páginas de índice del nivel hoja de un índice no agrupado contienen columnas de clave. Opcionalmente, también pueden contener un subconjunto de otras columnas de la tabla como columnas incluidas, para evitar recuperarlas de la tabla base.

Los localizadores de filas en filas de índice no clúster son un puntero a una fila o son una clave de índice agrupada para una fila, que se describe de la siguiente manera:

• Si la tabla tiene un índice clúster o si el índice está en una vista indizada, el localizador de fila es la clave del índice clúster para la fila.

• Si la tabla es un montón, lo que significa que no tiene ningún índice agrupado, el localizador de fila es un puntero a la fila. El puntero se genera a partir del identificador (Id.) de archivo, el número de página y el número de la fila dentro de la página. El puntero completo se conoce como Id. de fila (RID).

Los localizadores de filas también garantizan la singularidad de las filas de índices no agrupados. En la tabla siguiente se describe cómo el motor de base de datos agrega localizadores de filas a los índices no agrupados:

Tipo de tabla base	Tipo de índice no agrupado	Localizador de filas
Heap
	Nonunique	RID agregado a columnas de clave
	Unique	RID agregado a columnas incluidas
Índice agrupado único
	Nonunique	Claves de índice agrupado agregadas a columnas de clave
	Unique	Claves de índice agrupado agregadas a columnas incluidas
Índice agrupado no único
	Nonunique	Claves de índice agrupado y valor de unicidad (si lo hay) agregado a columnas de clave
	Unique	Claves de índice agrupado y valor de unicidad (si lo hay) agregado a columnas incluidas

El motor de base de datos nunca almacena una columna determinada más de una vez en un índice no clúster. El orden de clave de índice especificado por el usuario cuando crea un índice no clúster siempre se respeta: todas las columnas de localizador de filas que se deben agregar a la clave de un índice no clúster se agregan al final de la clave, siguiendo las columnas especificadas en la definición de índice. Los localizadores de filas de clave de índice agrupados en un índice no clúster se pueden usar en el procesamiento de consultas, independientemente de si se especifican explícitamente en la definición de índice o se agregan implícitamente.

En los ejemplos siguientes muestran cómo se implementan los localizadores de filas en índices no agrupados:

Índice agrupado	Definición de índices no agrupados	Definición de índices no agrupados con localizadores de filas	Explanation
Índice agrupado único con columnas de clave (A, B, C)	Índice no clúster y no único con columnas de clave (B, A) y columnas incluidas (E,G)	Columnas de clave (B, A, C) y columnas incluidas (E, G)	El índice no clúster no es único, así que el localizador de filas debe estar en las claves del índice. Las columnas B y A del localizador de filas ya están, así que solo se agrega la columna C. La columna C se agrega al final de la lista de la columna de clave.
Índice agrupado único con columna de clave (A)	Índice no agrupado y no único con columnas de clave (B, C) y columna incluida (A)	Columnas de clave (B, C, A)	El índice no clúster no es único, por lo que el localizador de filas se agrega a la clave. La columna A todavía no está especificada como columna de clave, por lo que se agrega al final de la lista de columnas de clave. La columna A ya está en la clave, por lo que no es necesario almacenarla como columna incluida.
Índice agrupado único con columna de clave (A,B)	Índice no agrupado único con columna de clave (C)	Columna de clave (C) y columnas incluidas (A,B)	El índice no agrupado es único, por lo que el localizador de filas se agrega a las columnas incluidas.

Los índices no agrupados tienen una fila en sys.partitions para cada partición usada por el índice, con index_id > 1. De forma predeterminada, un índice no clúster tiene una sola partición. Cuando un índice no clúster tiene varias particiones, cada una tiene una estructura de árbol B+ que contiene las filas de índice de esa partición específica. Por ejemplo, si un índice no clúster tiene cuatro particiones, hay cuatro estructuras de árbol B+, una en cada partición.

En función de los tipos de datos del índice no agrupado, cada estructura de índice no agrupado tiene una o más unidades de asignación en las que almacenar y administrar los datos de una partición específica. Como mínimo, cada índice no clúster tiene una IN_ROW_DATA unidad de asignación por partición que almacena las páginas de árbol de índice B+. El índice no clúster también tiene una LOB_DATA unidad de asignación por partición si contiene columnas de objetos grandes (LOB), como nvarchar(max). Además, tiene una ROW_OVERFLOW_DATA unidad de asignación por partición si contiene columnas de longitud variable que superan el límite de tamaño de fila de 8060 bytes.

En la siguiente ilustración se muestra la estructura de un índice no clúster en una sola partición.

Uso de columnas incluidas en índices no clúster

Además de las columnas de clave, un índice no agrupado también puede tener columnas sin clave almacenadas en el nivel hoja. Estas columnas no clave se denominan columnas incluidas y se especifican en la cláusula INCLUDE de la instrucción CREATE INDEX.

Un índice con columnas no clave incluidas puede mejorar significativamente el rendimiento de las consultas cuando cubre la consulta, es decir, cuando todas las columnas usadas en la consulta están en el índice como columnas clave o no clave. Las mejoras de rendimiento se logran porque el motor de base de datos puede encontrar todos los valores de columna dentro del índice; No se tiene acceso a la tabla base, lo que da lugar a menos operaciones de E/S de disco.

Si una consulta debe recuperar una columna, pero no se usa en los predicados de consulta, agregaciones y ordenaciones, agréguela como una columna incluida y no como una columna clave. Esto tiene las siguientes ventajas:

• Las columnas incluidas pueden usar tipos de datos no permitidos como columnas de clave de índice.

• El motor de base de datos no considera las columnas incluidas al calcular el número de columnas de clave de índice o el tamaño de clave de índice. Con las columnas incluidas, no está limitado por el tamaño máximo de clave de 900 bytes. Puede crear índices más amplios que abarquen más consultas.

• Al mover una columna de la clave de índice a las columnas incluidas, la compilación del índice tarda menos tiempo porque la operación de ordenación del índice se vuelve más rápida.

Si la tabla tiene un índice agrupado, la columna o columnas definidas en la clave de índice agrupado se añaden automáticamente a todos los índices no agrupados no únicos de la tabla. No es necesario especificarlos en la clave de índice no agrupada o como columnas incluidas.

Directrices para índices con columnas incluidas

Tenga en cuenta las siguientes instrucciones al diseñar índices no agrupados con columnas incluidas:

• Las columnas incluidas solo se pueden definir en índices no agrupados en tablas o vistas indexadas.

• Se admiten todos los tipos de datos, a excepción de text, ntexte image.

• Las columnas calculadas que son deterministas, y precisas o imprecisas, pueden ser columnas incluidas. Para obtener más información, vea Índices en columnas calculadas.

• Al igual que con las columnas clave, las columnas calculadas derivadas de los tipos de datos image, ntext y text se pueden incluir como columnas siempre que el tipo de datos de la columna calculada sea permitido en una columna incluida.

• Los nombres de columna no se pueden especificar en la lista INCLUDE ni en la lista de columnas de clave.

• Los nombres de columna no se pueden repetir en la lista INCLUDE.

• Al menos una columna de clave debe definirse en un índice. El número máximo de columnas incluidas es 1023. Éste es el número máximo de columnas de la tabla menos 1.

• Independientemente de la presencia de columnas incluidas, las columnas de clave de índice deben seguir las restricciones de tamaño de índice existentes de 16 columnas de clave máxima y un tamaño total de clave de índice de 900 bytes.

Recomendaciones de diseño para índices con columnas incluidas

Considere la posibilidad de rediseñar índices no clúster con un tamaño de clave de índice grande para que solo las columnas usadas en predicados de consulta, agregaciones y ordenes sean columnas clave. Haga que todas las demás columnas que abarcan la consulta sean columnas sin clave incluidas. De esta forma, tiene todas las columnas necesarias para abarcar la consulta pero la clave de índice en sí será pequeña y eficaz.

Por ejemplo, suponga que desea diseñar un índice para abarcar la siguiente consulta.

SELECT AddressLine1,
       AddressLine2,
       City,
       StateProvinceID,
       PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' AND N'99999';

Para abarcar la consulta, cada columna debe definirse en el índice. Aunque puede definir todas las columnas como columnas de clave, el tamaño de clave debe ser de 334 bytes. Como la única columna que se usa como criterio de búsqueda es la columna PostalCode, que tiene una longitud de 30 bytes, un mejor diseño del índice definiría PostalCode como columna de clave e incluiría todas las demás columnas como columnas sin clave.

La siguiente instrucción crea un índice con columnas incluidas para abarcar la consulta.

CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

Para validar que el índice atiende la consulta, cree el índice y, después, muestre el plan de ejecución estimado. Si el plan de ejecución muestra un operador Index Seek para el IX_Address_PostalCode índice, la consulta está cubierta por el índice.

Consideraciones de rendimiento para índices con columnas incluidas

Evite crear índices con un gran número de columnas incluidas. Aunque el índice podría estar abarcando más consultas, su ventaja de rendimiento se reduce.

• Caben menos filas de índice en una página. Esto aumenta la E/S de disco y reduce la eficacia de la memoria caché.

• Se necesita más espacio en disco para almacenar el índice. En concreto, agregar tipos de datos varchar(max),nvarchar(max), varbinary(max)o xml en columnas incluidas puede aumentar significativamente los requisitos de espacio en disco. Esto se debe a que los valores de columnas se copian en el nivel hoja del índice. Por lo tanto, residen en el índice y en la tabla base.

• El rendimiento de la modificación de datos disminuye porque muchas columnas deben modificarse tanto en la tabla basada como en el índice no clúster.

Debe determinar si las ganancias en el rendimiento de las consultas superan la disminución del rendimiento de la modificación de datos y el aumento de los requisitos de espacio en disco.

Directrices para diseñar índices únicos

Un índice único garantiza que la clave de índice no contiene valores duplicados. La creación de un índice único solo es posible cuando la unicidad es una característica de los propios datos. Por ejemplo, para asegurarse de que los valores de la columna NationalIDNumber de la tabla HumanResources.Employee son únicos, cuando la clave principal es EmployeeID, cree una restricción UNIQUE en la columna NationalIDNumber. La restricción rechaza cualquier intento de introducir filas con números de identificación nacional duplicados.

En el caso de índices únicos para varias columnas, el índice asegura que cada combinación de valores de la clave de índice sea única. Por ejemplo, si se crea un índice único en una combinación de LastName, FirstName y MiddleName, no podrían tener dos filas en la tabla los mismos valores para estas columnas.

Tanto los índices clúster como los no clúster pueden ser únicos. Puede crear un índice clúster único y varios índices no clúster únicos en la misma tabla.

Entre las ventajas de los índices únicos se incluyen:

• Se aplican reglas de negocio que requieren unicidad de datos.
• Se proporciona información adicional útil para el optimizador de consultas.

Si se crea una restricción PRIMARY KEY o UNIQUE, se creará automáticamente un índice único en las columnas especificadas. No existen diferencias significativas entre la creación de una restricción UNIQUE y la creación de un índice único independiente de una restricción. La validación de datos tiene lugar de la misma manera y el optimizador de consultas no establece diferencias entre un índice único creado por una restricción y uno creado manualmente. Sin embargo, debe crear una restricción de UNIQUE o PRIMARY KEY en la columna cuando el objetivo sea la aplicación de las normas empresariales. Al hacer esto, el objetivo del índice queda claro.

Consideraciones sobre índices únicos

• Un índice único, una restricción UNIQUE o una restricción PRIMARY KEY no se pueden crear si existen valores de clave duplicados en los datos.

• Si los datos son únicos y desea hacer cumplir la exclusividad, la creación de un índice único en lugar de un índice no único en la misma combinación de columnas proporciona información adicional para el optimizador de consultas que puede dar como resultado unos planes de ejecución más eficaces. En este caso, se recomienda crear una UNIQUE restricción o un índice único.

• Un índice no clúster único puede incluir columnas sin clave. Para obtener más información, consulte Uso de columnas incluidas en índices no clúster.

• A diferencia de una PRIMARY KEY restricción, se puede crear una UNIQUE restricción o un índice único con una columna anulable en la clave de índice. Para el propósito de hacer cumplir la unicidad, se consideran iguales dos valores NULL. Por ejemplo, esto significa que en un índice único de una sola columna, la columna solo puede ser NULL para una fila de la tabla.

Directrices para diseñar índices filtrados

Un índice filtrado es un índice no clúster optimizado, especialmente adecuado para las consultas que requieren un pequeño subconjunto de datos en la tabla. Usa un predicado de filtro en la definición de índice para indexar una parte de las filas de la tabla. Un índice filtrado bien diseñado puede mejorar el rendimiento de las consultas, reducir los costos de actualización de índices y reducir los costos de almacenamiento de índices en comparación con un índice de tabla completa.

Los índices filtrados pueden proporcionar las siguientes ventajas respecto a los índices de tabla completa:

• Mejor rendimiento de las consultas y mayor calidad del plan

  Un índice filtrado bien diseñado mejora el rendimiento de las consultas y la calidad del plan de ejecución porque es menor que un índice no clúster de tabla completa. Un índice filtrado tiene estadísticas filtradas, que son más precisas que las estadísticas de tabla completa porque cubren solo las filas del índice filtrado.

• Costos reducidos de actualización de índices

  Un índice solo se actualiza cuando las instrucciones del lenguaje de manipulación de datos (DML) afectan a los datos del índice. Un índice filtrado reduce los costos de actualización de índices en comparación con un índice no clúster de tabla completa porque es más pequeño y solo se actualiza cuando se ven afectados los datos del índice. Se puede disponer de una gran cantidad de índices filtrados, sobre todo cuando contienen datos que raramente se ven afectados. Del mismo modo, si un índice filtrado contiene solo los datos afectados con frecuencia, el tamaño menor del índice reduce el costo de actualizar las estadísticas.

• Costos reducidos de almacenamiento de índices

  La creación de un índice filtrado puede reducir la cantidad de almacenamiento en disco de índices no agrupado, cuando no sea necesario un índice de tabla completa. Es posible que pueda reemplazar un índice no clúster de tabla completa con varios índices filtrados sin aumentar significativamente los requisitos de almacenamiento.

Los índices filtrados son útiles cuando las columnas contienen subconjuntos bien definidos de datos. Algunos ejemplos son:

• Columnas que contienen muchos valores nulos.

• Columnas heterogéneas que contienen categorías de datos.

• Columnas que contienen intervalos de valores como cantidades, tiempo y fechas.

Los costos de actualización reducidos para los índices filtrados son más notables cuando el número de filas del índice es pequeño en comparación con un índice de tabla completa. Si el índice filtrado incluye la mayoría de las filas en la tabla, puede resultar más costoso mantenerlo que un índice de tabla completa. En ese caso, debe utilizar un índice de tabla completa en lugar de un índice filtrado.

Los índices filtrados se definen en una tabla y solamente admiten operadores de comparación simples. Si necesita una expresión de filtro que tenga lógica compleja o haga referencia a varias tablas, debe crear una columna calculada indizada o una vista indizada.

Consideraciones de diseño de índices filtrados

Para diseñar índices filtrados efectivos, es importante entender qué consultas utiliza la aplicación y cómo se relacionan con los subconjuntos de datos. Algunos ejemplos de datos que tienen subconjuntos bien definidos son columnas con muchas NULL, columnas con categorías heterogéneas de valores y columnas con distintos intervalos de valores.

Las siguientes consideraciones de diseño proporcionan varios escenarios para cuando un índice filtrado puede proporcionar ventajas sobre los índices de tabla completa.

Índices filtrados para subconjuntos de datos

Cuando una columna solamente tiene pocos valores pertinentes para las consultas, puede crear un índice filtrado en el subconjunto de valores. Por ejemplo, cuando la columna es principalmente NULL y la consulta solo requiere valores que no son NULL, puede crear un índice filtrado que contenga las filas que no son NULL.

Por ejemplo, la base de datos de muestra AdventureWorks tiene una tabla Production.BillOfMaterials con 2679 filas. La EndDate columna tiene solo 199 filas que contienen un valor distinto de NULL y las otras 2480 filas contienen NULL. El siguiente índice filtrado cubre las consultas que devuelven las columnas definidas en el índice y que requieren solo filas con un valor distinto de NULL para EndDate.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;

El índice filtrado FIBillOfMaterialsWithEndDate es válido para la consulta siguiente. Muestre el plan de ejecución estimado para determinar si el optimizador de consultas ha usado el índice filtrado.

SELECT ProductAssemblyID,
       ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
      AND ComponentID = 5
      AND StartDate > '20080101';

Para obtener más información sobre cómo crear índices filtrados y cómo definir la expresión de predicado del índice filtrado, vea Creación de índices filtrados.

Índices filtrados para datos heterogéneos

Cuando una tabla tiene filas de datos heterogéneos, se puede crear un índice filtrado para una o varias categorías de datos.

Por ejemplo, cada uno de los productos de la tabla Production.Product está asignado a un ProductSubcategoryIDque, a su vez, está asociado a las categorías de producto Bikes, Components, Clothing o Accessories. Estas categorías son heterogéneas porque sus valores de columna en la tabla Production.Product no están suficientemente correlacionados. Por ejemplo, las columnas Color, ReorderPoint, ListPrice, Weight, Classy Style tienen características únicas para cada categoría de producto. Suponga que se realizan consultas frecuentes de accesorios cuyas subcategorías están comprendidas entre 27 y 36 inclusive. Puede mejorar el rendimiento de las consultas de accesorios si crea un índice filtrado en las subcategorías de accesorios como se muestra en el ejemplo siguiente.

CREATE NONCLUSTERED INDEX FIProductAccessories
ON Production.Product (ProductSubcategoryID, ListPrice)
INCLUDE (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;

El índice FIProductAccessories filtrado cubre la siguiente consulta porque los resultados de la consulta están contenidos en el índice y el plan de consulta no requiere tener acceso a la tabla base. Por ejemplo, la expresión de predicado de la consulta ProductSubcategoryID = 33 es un subconjunto del predicado del índice filtrado ProductSubcategoryID >= 27 y ProductSubcategoryID <= 36, las columnas ProductSubcategoryID y ListPrice del predicado de la consulta son ambas columnas de clave del índice, y el nombre se almacena en el nivel hoja del índice como una columna incluida.

SELECT Name,
       ProductSubcategoryID,
       ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33
      AND ListPrice > 25.00;

Clave y columnas incluidas en índices filtrados

Se recomienda agregar un pequeño número de columnas en una definición de índice filtrada, solo según sea necesario para que el optimizador de consultas elija el índice filtrado para el plan de ejecución de consultas. El optimizador de consultas puede elegir un índice filtrado para la consulta, independientemente de que cubra la consulta o no. Sin embargo, es más probable que el optimizador de consultas elija un índice filtrado si cubre la consulta.

En algunos casos, un índice filtrado cubre la consulta sin incluir las columnas en la expresión del índice filtrado como columnas incluidas o de clave en la definición del índice filtrado. Las instrucciones siguientes explican los casos en que una columna de la expresión del índice filtrado debe ser una columna incluida o de clave en la definición del índice filtrado. Los ejemplos hacen referencia al índice filtrado FIBillOfMaterialsWithEndDate que se creó previamente.

Una columna de la expresión del índice filtrado no tiene por qué ser una columna incluida o de clave en la definición del índice filtrado cuando la expresión del índice filtrado es equivalente al predicado de la consulta y la consulta no devuelve la columna de la expresión del índice filtrado con los resultados de la consulta. Por ejemplo, FIBillOfMaterialsWithEndDate cubre la consulta siguiente porque el predicado de consulta es equivalente a la expresión de filtro, y EndDate no se devuelve con los resultados de la consulta. El FIBillOfMaterialsWithEndDate índice no necesita EndDate como clave ni columna incluida en la definición de índice filtrado.

SELECT ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

Una columna de la expresión del índice filtrado debe ser una columna incluida o de clave de la definición del índice filtrado cuando el predicado de la consulta usa la columna en una comparación no equivalente a la expresión del índice filtrado. Por ejemplo, FIBillOfMaterialsWithEndDate es válido para la consulta siguiente porque selecciona un subconjunto de filas del índice filtrado. Sin embargo, no cubre la consulta siguiente porque EndDate se utiliza en la comparación EndDate > '20040101', que no es equivalente a la expresión del índice filtrado. El procesador de consultas no puede ejecutar esta consulta sin examinar los valores de EndDate. Por tanto, EndDate debe ser una columna incluida o de clave de la definición del índice filtrado.

SELECT ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate > '20040101';

Una columna de la expresión del índice filtrado debe ser una columna incluida o de clave en la definición del índice filtrado si la columna está en el conjunto de resultados de la consulta. Por ejemplo, FIBillOfMaterialsWithEndDate no atiende la consulta siguiente porque devuelve la columna EndDate en los resultados de la consulta. Por tanto, EndDate debe ser una columna incluida o de clave de la definición del índice filtrado.

SELECT ComponentID,
       StartDate,
       EndDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

La clave de índice agrupado de la tabla no tiene por qué ser una columna incluida o de clave de la definición del índice filtrado. La clave de índice cluster se incluye de forma automática en todos los índices no clúster, incluidos los índices filtrados.

Operadores de conversión de datos en el predicado de filtro

Si el operador de comparación especificado en la expresión del índice filtrado del índice filtrado produce una conversión de datos implícita o explícita, se produce un error cuando la conversión se realice en el lado izquierdo de un operador de comparación. Una posible solución es escribir la expresión del índice filtrado con el operador de conversión de datos (CAST or CONVERT) en el lado derecho del operador de comparación.

En el ejemplo siguiente se crea una tabla con columnas de distintos tipos de datos.

CREATE TABLE dbo.TestTable
(
    a INT,
    b VARBINARY(4)
);

En la siguiente definición de índice filtrado, la columna b se convierte implícitamente en un tipo de datos entero para compararlo con la constante 1. Esto genera el mensaje de error 10611 porque la conversión se produce en el lado izquierdo del operador del predicado filtrado.

CREATE NONCLUSTERED INDEX TestTabIndex
ON dbo.TestTable (a, b)
    WHERE b = 1;

La solución consiste en convertir la constante del lado derecho de forma que sea del mismo tipo que la columna b, tal como se muestra en el ejemplo siguiente:

CREATE INDEX TestTabIndex
ON dbo.TestTable (a, b)
    WHERE b = CONVERT (VARBINARY(4), 1);

Cuando se mueve la conversión de datos del lado izquierdo al lado derecho de un operador de comparación, es posible que cambie el significado de la conversión. En el ejemplo anterior, cuando el CONVERT operador se agregó al lado derecho, la comparación cambió de una comparación int a una comparación varbinary .

Arquitectura de los índices de almacén de columnas

Un índice de almacén de columnas es una tecnología para almacenar, recuperar y administrar datos mediante un formato de datos de columnas, denominado almacén de columnas. Para obtener más información, consulte Índices de almacén de columnas: información general.

Para obtener información sobre la versión y conocer las novedades, visite Novedades de los índices de almacén de columna.

Conocer estos conceptos básicos facilita la comprensión de otros artículos sobre almacenamiento por columnas que explican cómo usar esta tecnología de forma eficaz.

El almacenamiento de datos utiliza almacenamiento en columnas y almacenamiento en filas

Al tratar los índices de almacén de columnas, se usan los términos almacén de filas y almacén de columnas para hacer hincapié en el formato del almacenamiento de datos. Los índices de almacén de columnas utilizan estos dos tipos de almacenamiento.

• Un almacén de columnas son datos organizados lógicamente como una tabla con filas y columnas, y almacenados físicamente en un formato de columnas.

  Un índice de almacén almacena físicamente la mayoría de los datos en formato de almacén de columnas. Con este formato, los datos se comprimen y descomprimen como columnas. No hace falta descomprimir otros valores que no haya solicitado la consulta en cada una de las filas. De este modo, se puede examinar rápida una columna entera de una tabla grande.

• Un almacén de filas son datos organizados lógicamente como una tabla con filas y columnas, y almacenados físicamente después en un formato de filas. Esta ha sido la manera tradicional de almacenar datos de tabla relacional, como un índice de árbol B+ agrupado o un montón.

  Un índice de almacén de columnas también almacena físicamente algunas filas en un formato de almacén de filas denominado almacén delta. Se trata de un lugar donde se colocan las filas que son insuficientes para poder comprimirse en el almacén de columnas. Cada grupo de filas delta se implementa como un índice de árbol B+ agrupado, que es un almacenamiento en formato de filas.

Las operaciones se realizan en segmentos de columna y grupos de filas

El índice de almacén de columnas agrupa las filas en unidades administrables. Cada una de estas unidades se denomina grupo de filas. Para obtener el mejor rendimiento, el número de filas de un grupo de filas es lo suficientemente grande como para mejorar la relación de compresión y lo suficientemente pequeño como para beneficiarse de las operaciones en memoria.

Por ejemplo, el índice de almacén de columnas realiza estas operaciones en grupos de filas:

• Comprime los grupos de filas en el almacén de columnas. La compresión se realiza en cada segmento de columna de un grupo de filas.

• Combina grupos de filas durante una ALTER INDEX ... REORGANIZE operación, incluida la eliminación de datos eliminados.

• Vuelve a crear todos los grupos de filas durante una ALTER INDEX ... REBUILD operación.

• Informa de la fragmentación y el estado de los grupos de filas en las vistas de administración dinámica (DMV).

El almacén delta se compone de uno o varios grupos de filas denominados grupos de filas delta. Cada grupo de filas delta es un índice de árbol B+ agrupado que almacena pequeñas cargas masivas e inserciones hasta que el grupo de filas contiene 1048 576 filas, en cuyo momento un proceso denominado tupla-mover comprime automáticamente un grupo de filas cerrado en el almacén de columnas.

Para obtener más información sobre los estados de los grupos de filas, vea sys.dm_db_column_store_row_group_physical_stats.

Tip

Tener demasiados grupos de filas pequeños reduce la calidad del índice de almacén de columnas. Una operación de reorganización combina grupos de filas más pequeños, siguiendo una directiva de umbral interno que determina cómo quitar filas eliminadas y combinar los grupos de filas comprimidos. Después de una combinación, se mejora la calidad del índice.

En SQL Server 2019 (15.x) y versiones posteriores, el trasladador de tuplas es asistido por una tarea de combinación en segundo plano que comprime automáticamente los grupos de filas delta abiertos más pequeños que han existido por cierto tiempo, determinado por un umbral interno, o combina grupos de filas comprimidos de los que se ha eliminado un gran número de filas.

Cada columna tiene algunos de sus valores en cada grupo de filas. Estos valores se denominan segmentos de columna. Cada grupo de filas contiene un segmento de cada columna de la tabla. Cada columna tiene un segmento de columna en cada grupo de filas.

Cuando el índice de almacén de columnas comprime un grupo de filas, lo hace con cada segmento de columna de manera independiente. Para descomprimir una columna entera, el índice de almacén de columnas solo debe descomprimir un segmento de columna de cada grupo de filas.

Las inserciones y las cargas pequeñas pasan al almacén delta

Un índice de almacén de columnas mejora el rendimiento y la compresión del almacén de columnas comprimiendo, al menos, 102 400 filas a la vez en el índice de almacén de columnas. Para comprimir las filas de forma masiva, el índice de almacén de columnas acumula inserciones y cargas pequeñas en el almacén delta. Las operaciones de deltastore se gestionan en segundo plano. Para devolver los resultados de la consulta, el índice de almacén de columnas agrupado combina los resultados de la consulta tanto del almacén de columnas como del almacén delta.

Las filas pasan al almacén delta cuando se dan estas circunstancias:

• Insertado con la instrucción INSERT INTO ... VALUES.

• Al final de una carga masiva y cuando tienen menos de 102 400 filas.

• Updated. Cada actualización se implementa como una eliminación y una inserción.

El almacén delta también guarda una lista de identificadores de las filas eliminadas que se han marcado como eliminadas, pero que aún no se han suprimido físicamente del almacén de columnas.

Cuando se llenan los grupos de filas delta se comprimen en el almacén de columnas.

Los índices agrupados de almacén de columnas recopilan hasta 1 048 576 filas en cada grupo de filas delta antes de comprimir dicho grupo de filas en el almacén de columnas. De este modo, se mejora la compresión del índice de almacén de columnas. Cuando un grupo de filas delta alcanza el número máximo de filas, pasa de un estado OPEN a un estado CLOSED. Un proceso en segundo plano denominado "motor de tupla" comprueba si hay grupos de filas cerrados. Si el proceso encuentra un grupo de filas cerrado, comprime el grupo de filas y lo almacena en el almacén de columnas.

Cuando se ha comprimido un grupo de filas delta, el grupo de filas delta existente pasa al estado TOMBSTONE para que lo quite más adelante el motor de tupla cuando no haya referencias a él, y el nuevo grupo de filas comprimido se marca como COMPRESSED.

Para obtener más información sobre los estados de los grupos de filas, vea sys.dm_db_column_store_row_group_physical_stats.

Puede forzar grupos de filas delta del almacén de columnas mediante ALTER INDEX para generar o reorganizar el índice. Si hay presión de memoria durante la compresión, el índice de almacén de columnas podría reducir el número de filas en el grupo de filas comprimido.

Cada partición de tabla tiene sus propios grupos de filas y grupos de filas delta

El concepto de creación de particiones es el mismo en un índice agrupado, un heap y un índice columnstore. Al crear particiones de una tabla, esta se divide en grupos de filas más pequeños según un rango de valores de columna. A menudo, se utiliza para administrar los datos. Por ejemplo, podría crear una partición para cada año de datos y, a continuación, usar el cambio de partición para archivar datos antiguos a almacenamiento menos costoso.

Los grupos de filas siempre se definen dentro de una partición de tabla. Cuando un índice de almacén de columnas tiene particiones, cada una de ellas tiene sus propios grupos de filas y grupos de filas delta comprimidos. Una tabla no particionada contiene una partición.

Tip

Considere la posibilidad de usar la creación de particiones de tabla si es necesario quitar datos del almacén de columnas. Cambiar y truncar particiones que ya no son necesarias es una estrategia eficaz para eliminar datos sin introducir fragmentación en el almacén de columnas.

Cada partición puede contener varios grupos de filas delta

Cada partición puede tener más de un grupos de filas delta. Cuando el índice de almacén de columnas necesita agregar datos a un grupo de filas delta y otro grupo de filas delta está bloqueado por otra transacción, el índice de almacén de columnas intenta obtener un bloqueo en un grupo de filas delta diferente. Si no hay ningún grupo de filas delta disponible, el índice de almacén columnas crea un grupo de filas delta. Por ejemplo, una tabla con 10 particiones podría tener fácilmente 20 o más grupos de filas delta.

Combine los índices de almacén de filas y de columnas en la misma tabla

Un índice no agrupado contiene una copia de parte o la totalidad de las filas y columnas de la tabla subyacente. El índice se define como una o varias columnas de la tabla y tiene una condición opcional que filtra las filas.

Puede crear un índice de almacén de columnas no agrupado actualizable en una tabla de almacén de filas. El índice de columnas almacena una copia de los datos, por lo que necesita más almacenamiento. Sin embargo, los datos del índice columnstore se comprimen a un tamaño mucho menor que el que requiere la tabla rowstore. Al hacerlo, puede ejecutar análisis en el índice de almacén de columnas y las cargas de trabajo OLTP en el índice de almacén de filas al mismo tiempo. El almacén de columnas se actualiza cuando cambian los datos de la tabla de almacén de filas, de modo que ambos índices trabajan con los mismos datos.

Una tabla de almacén de filas puede tener un índice de almacén de columnas no agrupado. Para obtener más información, consulte Índices de almacén de columnas: guía de diseño.

Puede tener uno o varios índices de almacén de filas no agrupados en una tabla de almacén de columnas agrupada. Gracias a ello, podrá realizar búsquedas de tabla eficaces en el almacén de columnas subyacente. También habrá disponibles otras opciones. Por ejemplo, puede aplicar la unicidad mediante una UNIQUE restricción en la tabla de rowstore. Cuando un valor no único no se inserta en la tabla de almacén de filas, el motor de base de datos tampoco inserta el valor en el almacén de columnas.

Consideraciones de rendimiento de almacén de columnas no agrupadas

La definición del índice de almacén de columnas no agrupado admite el uso de una condición de filtrado. Para minimizar el efecto de rendimiento de agregar un índice de almacén de columnas, use una expresión de filtro para crear un índice de almacén de columnas no agrupado solo en el subconjunto de datos necesarios para el análisis.

Una tabla optimizada para memoria puede tener un índice columnar. Puede crearla cuando se cree la tabla o agregarla más adelante con ALTER TABLE.

Para obtener más información, consulte Índices de almacén de columnas: rendimiento de consultas.

Directrices de diseño de índices hash optimizados para memoria

Al usar In-Memory OLTP, todas las tablas optimizadas para memoria deben tener al menos un índice. Para una tabla optimizada para memoria, todos los índices también están optimizados para memoria. Los índices de hash son uno de los tipos de índice posibles en una tabla optimizada para memoria. Para obtener más información, vea Índices de tablas optimizadas para memoria.

Arquitectura de índice hash optimizada para memoria

Los índices de hash constan de una matriz de punteros, y cada elemento de la matriz se llama "cubo de hash".

• Cada depósito tiene 8 bytes, que se usan para almacenar la dirección de memoria de una lista de vínculos de entradas de índice.
• Cada entrada es un valor correspondiente a una clave de índice, además de la dirección de su fila correspondiente en la tabla subyacente optimizada para memoria.
• Cada entrada apunta a la siguiente entrada en una lista de vínculos de entradas, todas ellas encadenadas al depósito actual.

El número de cubos debe especificarse en el momento de creación del índice:

• Cuanto menor sea la proporción de depósitos con respecto a las filas de la tabla o valores distintos, mas larga será la lista de vínculos de depósito promedio.
• Las listas de vínculos cortas se ejecutan más rápidamente que las listas de vínculos largas.
• El número máximo de cubos en los índices de hash es de 1 073 741 824.

Tip

Para determinar el BUCKET_COUNT correcto para los datos, consulte Configuración del número de cubos de índice de hash.

La función hash se aplica a las columnas de clave de índice, y el resultado de la función determina a qué contenedor pertenece dicha clave. Los cubos tienen un puntero a las filas cuyos valores de clave con hash se asignan a estos cubos.

La función hash que se utiliza para los índices hash tiene las siguientes características:

• El motor de base de datos tiene una función hash que se utiliza para todos los índices hash.
• La función hash es determinista. Siempre se asigna el mismo valor de clave de entrada al mismo cubo del índice de hash.
• Se pueden asignar múltiples claves de índice al mismo cubo de hash.
• La función hash está equilibrada, lo que significa que los valores de clave de índice en los cubos de hash siguen normalmente una distribución normal o de Poisson, no una distribución plana lineal.
• La distribución de Poisson no es una distribución uniforme. Los valores de clave de índice no se distribuyen uniformemente en cubos de hash.
• Si dos claves de índice se asignan al mismo cubo de hash, hay una colisión de hash. Un gran número de colisiones de hash puede tener un efecto en el rendimiento de las operaciones de lectura. Un objetivo realista es que el 30 % de los cubos contenga dos valores clave diferentes.

La interacción del índice de hash y los cubos se resume en la siguiente imagen.

Configuración del número de cubos de índice de hash

El número de cubos de índice de hash se especifica al crear el índice y se puede modificar con la sintaxis ALTER TABLE...ALTER INDEX REBUILD.

En la mayoría de los casos, el número de cubos debe estar comprendido entre 1 y 2 veces el número de valores distintos en la clave de índice. Es posible que no siempre pueda predecir cuántos valores tiene una clave de índice determinada. El rendimiento sigue siendo bueno por lo general si el valor BUCKET_COUNT está dentro de 10 veces el número real de valores de clave. A este respecto, suele ser mejor realizar estimaciones por lo alto que por lo bajo.

Un número muy pequeño de cubos puede tener las siguientes desventajas:

• Más colisiones de hash de valores de clave distintos.
• Cada valor distinto está obligado a compartir el mismo cubo con otro valor distinto.
• Aumenta el promedio de longitud de cadena por cubo.
• Cuanto más larga sea la cadena de cubos, menor será la velocidad de búsquedas de igualdad en el índice.

Un número muy alto de cubos puede tener las siguientes desventajas:

• Un número excesivo de cubos puede generar más cubos vacíos.
• Los depósitos vacíos repercuten en el rendimiento de los exámenes de índice completos. Si los exámenes se realizan con frecuencia, inclínese por un número de cubos cercano al número de valores de clave de índice distintos.
• Los depósitos vacíos usan la memoria, aunque cada depósito emplea únicamente 8 bytes.

Note

Agregar más depósitos no hace nada para reducir el encadenamiento de entradas que comparten un valor duplicado. La tasa de duplicación de valores se usa para decidir si un índice hash o un índice no clúster es el tipo de índice adecuado, no para calcular el recuento de cubos.

Consideraciones de rendimiento para los índices hash

El rendimiento de un índice de hash es:

• Excelente cuando el predicado de la cláusula WHERE contiene un valor exacto para cada columna de la clave de índice de hash. Un índice de hash revertirá a un recorrido dado un predicado de desigualdad.
• Deficiente cuando el predicado de la cláusula WHERE busca un rango de valores en la clave de índice.
• Deficiente cuando el predicado de la cláusula WHERE establece un valor específico para la primera columna de una clave de índice de hash de dos columnas, pero ninguno para las otras columnas de la clave.

Tip

El predicado debe incluir todas las columnas de la clave de índice hash. El índice hash requiere la clave completa para acceder al índice.

Si se usa un índice hash y el número de claves de índice únicas es más de 100 veces menor que el recuento de filas, considere la posibilidad de aumentar a un número de cubos mayor para evitar cadenas de filas grandes o usar un índice no agrupado en su lugar.

Creación de un índice hash

Al crear un índice hash, tenga en cuenta lo siguiente:

• Solo puede existir un índice de hash en una tabla optimizada para memoria. No puede existir en una tabla basada en disco.
• Un índice hash no es único de forma predeterminada, pero se puede declarar como único.

En el ejemplo siguiente se crea un índice hash único:

ALTER TABLE MyTable_memop ADD INDEX ix_hash_Column2
    UNIQUE HASH (Column2) WITH (BUCKET_COUNT = 64);

Versiones de fila y recolección de basura en tablas optimizadas para memoria

En una tabla optimizada para memoria, cuando una fila se ve afectada por una UPDATE instrucción , la tabla crea una versión actualizada de la fila. Durante la transacción de actualización, es posible que otras sesiones puedan leer la versión anterior de la fila y, por tanto, evitar la degradación del rendimiento asociada a un bloqueo de fila.

El índice de hash también podría tener versiones diferentes de las entradas para dar cabida a la actualización.

Más adelante cuando las versiones anteriores ya no se necesiten, un subproceso de recolección de elementos no utilizados (GC) recorre transversalmente los cubos y sus listas de vínculos para eliminar las entradas más antiguas. El subproceso GC funciona mejor si las longitudes de cadena de las listas de vínculo son cortas. Para obtener más información, vea Recolección de elementos no utilizados de OLTP en memoria.

Directrices para diseñar índices no agrupados optimizados para memoria

Además de los índices hash, los índices no agrupados son los otros tipos de índice posibles en una tabla optimizada para memoria. Para obtener más información, vea Índices de tablas optimizadas para memoria.

Arquitectura de índice no clúster optimizada para memoria

Los índices no agrupados en tablas optimizadas para memoria se implementan mediante una estructura de datos denominada árbol Bw, previsto originalmente y descrito por Microsoft Research en 2011. Un árbol Bw es la variación sin bloqueos ni bloqueos temporales de un árbol B. Para más información, vea Árbol Bw: un árbol B para nuevas plataformas de hardware.

En un nivel alto, el árbol Bw se puede entender como un mapa de páginas organizadas por identificador de página (PidMap), una instalación para asignar y reutilizar identificadores de página (PidAlloc) y un conjunto de páginas vinculadas en el mapa de páginas y entre sí. Estos tres subcomponentes de alto nivel constituyen la estructura interna básica de un árbol Bw.

Su estructura es similar a la de un árbol B porque cada página tiene un conjunto de valores de clave que están ordenados. Además, en el índice hay niveles, y cada uno de ellos apunta a un nivel inferior. A su vez, los niveles hoja apuntan a una fila de datos. Sin embargo, hay varias diferencias.

Al igual que con los índices hash, se pueden vincular varias filas de datos para admitir el control de versiones. Los punteros de página entre niveles son identificadores de página lógicos, es decir, desplazamientos a una tabla de asignación de páginas que, a su vez, contiene la dirección física de cada página.

No hay actualizaciones de páginas de índice en la ubicación. Se han incorporado nuevas páginas delta con esta finalidad.

• Para las actualizaciones de página no es necesario ningún bloqueo o bloqueo temporal.
• Las páginas de índice no son de un tamaño fijo.

El valor clave en cada página de nivel no hoja es el valor más alto que contiene el nodo hijo al que apunta, y cada fila también contiene el identificador de página lógica. En las páginas de nivel hoja, junto con el valor de clave, contiene la dirección física de la fila de datos.

Las búsquedas puntuales son similares a los árboles B, salvo que, dado que las páginas están vinculadas en una sola dirección, el motor de base de datos sigue los punteros a la derecha de la página, donde cada página no hoja tiene el valor más alto de su elemento secundario, en lugar del valor más bajo como en un árbol B.

Si una página de nivel hoja tiene que cambiar, el motor de base de datos no modifica la propia página. En su lugar, el motor de base de datos crea un registro delta que describe el cambio y lo anexa a la página anterior. Después, también actualiza la dirección de la tabla de asignación de página para dicha página anterior a la dirección del registro delta que se convierte en la dirección física de esta página.

Hay tres operaciones distintas que pueden ser necesarias para administrar la estructura de un árbol BW: consolidación, división y combinación.

Consolidación de deltas

Una larga cadena de registros delta puede degradar el rendimiento de la búsqueda, ya que podría requerir un recorrido de cadena largo al buscar a través de un índice. Si se agrega un nuevo registro delta a una cadena que ya contiene 16 elementos, los cambios en los registros delta se consolidan en la página de índice a la que se hace referencia. Después, la página se recompila incluyendo los cambios indicados por el nuevo registro delta que haya desencadenado la consolidación. La página recompilada tiene el mismo identificador de página pero una nueva dirección de memoria.

Dividir página

Los páginas de índice de un árbol BW se amplían en función de las necesidades: pueden almacenar una sola fila o bien un máximo de 8 KB. Una vez que la página de índice llegue a los 8 KB, una nueva inserción de una sola fila hace que la página de índice se divida. En el caso de las páginas internas, significa que no queda más espacio para agregar otro valor de clave y otro puntero. En el caso de las páginas hoja, significa que la fila sería demasiado grande para ajustarla a la página una vez que se incorporen todos los registros delta. La información de estadísticas que hay en el encabezado de página hoja lleva a cabo un seguimiento de la cantidad de espacio necesaria para consolidar los registros delta. Esta información se ajusta a medida que se agrega cada nuevo registro delta.

Se aplica una operación de división en dos pasos atómicos. En el diagrama que hay a continuación, pongamos que una página hoja fuerza una división porque se está insertando una clave con valor 5, y que hay una página no hoja que apunta al final de la página de nivel hoja (valor de clave 4).

Paso 1: Asigne dos nuevas páginas P1 y P2, y divida las filas de la anterior página P1 en estas nuevas páginas, incluida la fila que se acaba de insertar. Se usa un nuevo espacio en la tabla de asignación de páginas para almacenar la dirección física de la página P2. Las páginas P1 y P2 no están accesibles para ninguna operación simultánea todavía. Además, se establece el puntero lógico de P1 a P2. Después, en un paso atómico, se actualiza la tabla de asignación de páginas para cambiar el puntero de la anterior P1 a la nueva P1.

Paso 2: La página no hoja apunta a la página P1, pero no hay ningún puntero directo de una página no hoja a la página P2. Solo se puede acceder a P2 mediante P1. Para crear un puntero de una página no hoja a una P2, asigne una nueva página no hoja (página de índice interno), copie todas las filas de la antigua página no hoja y agregue una nueva fila para que apunte a P2. Una vez lo haya hecho, en un paso atómico, actualice la tabla de asignación de páginas para cambiar el puntero de la antigua página no hoja a la nueva página no hoja.

Página de combinación

Cuando una operación da como resultado que una DELETE página tenga menos del 10 % del tamaño máximo de página (8 KB) o con una sola fila en ella, esa página se combina con una página contigua.

Cuando se elimina una fila de una página, se agrega un registro delta para la eliminación. Además, se realiza una comprobación para determinar si la página de índice (página no hoja) se califica para la combinación. En esta operación se comprueba si el espacio restante después de eliminar la fila es inferior al 10 por ciento del tamaño máximo de página. Si cumple los requisitos, la combinación se realiza en tres pasos atómicos.

En la imagen siguiente, pongamos que una operación DELETE elimina el valor de clave 10.

Paso 1: Se crea una página delta que representa el valor de clave 10 (triángulo azul), y el puntero correspondiente de la página no hoja Pp1 se establece en la nueva página delta. Además, se crea una página delta de combinación (triángulo verde) y se vincula para que apunte a la página delta. En este punto, ninguna transacción simultánea puede ver las dos páginas (delta y delta de combinación). En un paso atómico, el puntero a la página de nivel hoja P1 de la tabla de asignación de páginas se actualiza para que apunte a la página delta de combinación. Después de este paso, la entrada del valor de clave 10 en Pp1 apunta a la página delta de combinación.

Paso 2: Debe eliminarse la fila que representa el valor de clave 7 en la página no hoja Pp1 y debe actualizarse la entrada del valor de clave 10 para que apunte a P1. Para ello, se asigna una nueva página no hoja Pp2 y todas las filas de Pp1 se copian, excepto la que representa el valor de clave 7. Después, la fila del valor de clave 10 se actualiza para que apunte a la página P1. Una vez hecho esto, en un paso atómico, la entrada de la tabla de asignación de páginas que apunta a Pp1 se actualiza para que apunte a Pp2. Ya no se puede acceder a Pp1.

Paso 3: Se combinan las páginas de nivel hoja P2 y P1, y se eliminan las páginas delta. Para ello, se asigna una nueva página P3, se combinan las filas de la P2 a la P1 y los cambios de la página delta se incluyen en la nueva P3. Después, en un paso atómico, la entrada de la tabla de asignación de páginas que apunta a la página P1 se actualiza para que apunte a la página P3.

Consideraciones de rendimiento para índices no clúster optimizados para memoria

El rendimiento de un índice no clúster es mejor que con índices hash al consultar una tabla optimizada para memoria con predicados de desigualdad.

Una columna de una tabla optimizada para memoria puede formar parte de un índice hash y de un índice no clúster.

Cuando una columna de clave de índice no agrupado tiene muchos valores duplicados, el rendimiento puede reducirse para las actualizaciones, inserciones y eliminaciones. Una manera de mejorar el rendimiento en esta situación es agregar una columna que tenga una mejor selectividad en la clave de índice.

Metadatos de índice

Para examinar metadatos de índice como definiciones de índice, propiedades y estadísticas de datos, use las siguientes vistas del sistema:

• sys.objects
• sys.indexes
• sys.index_columns
• sys.columns
• sys.types
• sys.partitions
• sys.internal_partitions
• sys.dm_db_index_usage_stats
• sys.dm_db_partition_stats
• sys.dm_db_index_operational_stats

Las vistas anteriores se aplican a todos los tipos de índice. Para los índices de almacén de columnas, use además las siguientes vistas:

• sys.column_store_row_groups
• sys.column_store_segments
• sys.column_store_dictionaries
• sys.dm_column_store_object_pool
• sys.dm_db_column_store_row_group_operational_stats
• sys.dm_db_column_store_row_group_physical_stats

En el caso de los índices de almacén de columnas, todas las columnas se almacenan en los metadatos como columnas incluidas. El índice de almacén de columnas no tiene columnas de clave.

Para los índices en tablas optimizadas para memoria, use además las siguientes vistas:

• sys.hash_indexes
• sys.dm_db_xtp_hash_index_stats
• sys.dm_db_xtp_index_stats
• sys.dm_db_xtp_nonclustered_index_stats
• sys.dm_db_xtp_object_stats
• sys.dm_db_xtp_table_memory_stats
• sys.memory_optimized_tables_internal_attributes

Contenido relacionado

• CREAR ÍNDICE (Transact-SQL)
• Optimización del mantenimiento de índices para mejorar el rendimiento de las consultas y reducir el consumo de recursos
• Tablas e índices con particiones
• Índices de las tablas con optimización para memoria
• Índices de columnas: visión general
• Índices en columnas calculadas
• Ajuste de índices no agrupados con sugerencias de índices que faltan