Directrices especiales para índices con particiones
Aunque los índices con particiones pueden implementarse independientemente de sus tablas base, por lo general tiene sentido diseñar una tabla con particiones y, a continuación, crear un índice en la tabla. Al hacerlo, SQL Server crea particiones en el índice con el mismo esquema de partición y columna de partición que la tabla. En consecuencia, en el índice se crean particiones básicamente de la misma forma que en la tabla. De este modo, el índice se alinea con la tabla.
SQL Server no alinea el índice con la tabla si especifica un esquema de partición diferente o un grupo de archivos independiente en el que colocar el índice durante la creación.
La alineación de un índice con una tabla con particiones es especialmente importante si prevé que va a crecer con la adición de nuevas particiones o que va a participar con frecuencia en modificadores de partición. Para obtener más información, vea Diseñar particiones para administrar subconjuntos de datos. Cuando una tabla y sus índices están alineados, SQL Server puede dividir las particiones de forma rápida y eficaz al mismo tiempo que mantiene la estructura de la partición tanto en la tabla como en sus índices.
Nota
Un índice no tiene por qué participar en la misma función de partición con nombre para alinearse con su tabla base. Sin embargo, la función de partición del índice y la tabla base debe ser básicamente la misma puesto que 1) los argumentos de las funciones de la partición tienen el mismo tipo de datos, 2) definen el mismo número de particiones y 3) definen los mismos valores de límite para las particiones.
La ficha Opciones de optimización del Asistente para la optimización de motor de base de datos ofrece la opción Particiones alineadas para especificar que los nuevos índices recomendados se alineen con sus tablas base. La opción Mantener particiones alineadas se puede utilizar para el mismo fin y también para quitar cualquier índice no alineado que exista. Para obtener más información, vea Asistente para la optimización de motor de base de datos (ficha Opciones de optimización). Por lo general, el Asistente para la optimización de motor de base de datos se puede utilizar para recomendar índices de rendimiento, que pueden incluir una mezcla de índices alineados y no alineados. Para obtener más información, vea Introducción al Asistente para la optimización de motor de base de datos.
El diseño de un índice con particiones independiente (no alineado) de la tabla base puede ser útil en los siguientes casos:
En la tabla base no se han creado particiones.
La clave de índice es única y no contiene la columna de partición de la tabla.
Desea que la tabla base participe en combinaciones colocadas con más tablas utilizando columnas de combinación diferentes.
Nota
Para habilitar la división de particiones, todos los índices de la tabla deben estar alineados.
Cuando cree un índice con particiones, tenga en cuenta la información de las siguientes secciones.
Crear particiones en índices únicos
Al crear una partición en un índice único (agrupado o no), la columna de partición se debe elegir entre las columnas utilizadas en la clave de índice único.
Nota
Esta restricción permite que SQL Server investigue solamente una partición para asegurarse de que no exista un duplicado del nuevo valor de clave en la tabla.
Si no es posible incluir la columna de partición en la clave única, utilice un desencadenador DML para forzar su exclusividad.
Crear particiones en índices agrupados
Al crear particiones en un índice agrupado, la clave de agrupación debe contener la columna de partición. Cuando se crean particiones en un índice agrupado no único y la columna de partición no se especifica explícitamente en la clave de agrupación, SQL Server agrega de forma predeterminada la columna de partición a la lista de claves de índice agrupado. Si el índice agrupado es único, deberá especificar explícitamente que la clave de índice agrupado contiene la columna de partición.
Crear particiones en índices no agrupados
Al crear particiones en un índice no agrupado único, la clave de índice debe contener la columna de partición. Cuando se crea un índice no agrupado no único, SQL Server agrega de forma predeterminada la columna de partición como una columna sin clave (incluida) del índice para garantizar que el índice está alineado con la tabla base. SQL Server no agrega la columna de partición al índice si ya está presente en el índice.
Limitaciones de memoria e índices con particiones
Las limitaciones de memoria pueden afectar al rendimiento o capacidad de SQL Server para crear un índice con particiones. Esto sucede especialmente cuando el índice no está alineado con su tabla base o no está alineado con su índice agrupado si ya se ha aplicado un índice agrupado a la tabla.
Cuando SQL Server realiza la ordenación para crear índices con particiones, primero crea una tabla de orden para cada partición. A continuación, crea las tablas de orden en el grupo de archivos respectivo de cada partición o en tempdb si se ha especificado la opción de índice SORT_IN_TEMPDB.
Cada tabla de orden requiere una cantidad mínima de memoria para su creación. Cuando crea un índice con particiones que está alineado con su tabla base, las tablas de orden se crean de una en una con menos memoria. Sin embargo, cuando crea un índice con particiones no alineado, las tablas de orden se crean al mismo tiempo.
En consecuencia, debe haber disponible memoria suficiente para permitir la realización de estas ordenaciones simultáneas. Cuanto mayor es el número de particiones, mayor es la cantidad de memoria necesaria. El tamaño mínimo para cada tabla de orden y para cada partición es de 40 páginas y 8 kilobytes por página. Por ejemplo, un índice con particiones no alineado con 100 particiones necesita memoria suficiente para ordenar en serie 4.000 (40 * 100) páginas al mismo tiempo. Si esta memoria está disponible, la operación de creación será satisfactoria, aunque ello afectará negativamente al rendimiento. Si esta memoria no está disponible, se producirá un error durante la operación de creación. De forma alternativa, un índice con particiones alineado con 100 particiones sólo necesita memoria suficiente para ordenar 40 páginas, ya que las ordenaciones no se realizan al mismo tiempo.
Tanto para los índices alineados como para los no alineados, el requisito de memoria puede ser mayor si SQL Server está aplicando grados de paralelismo a la operación de creación en un equipo con varios procesadores. Esto es así porque cuanto mayores son los grados de paralelismo, mayor es también el requisito de memoria. Por ejemplo, si SQL Server establece los grados de paralelismo en 4, un índice no alineado con 100 particiones necesitará memoria suficiente para que cuatro procesadores puedan ordenar 4.000 páginas al mismo tiempo o 16.000 páginas. Si el índice con particiones está alineado, el requisito de memoria se reduce a cuatro procesadores que ordenan 40 páginas o 160 (4 * 40) páginas. Puede utilizar la opción de índice MAXDOP para reducir manualmente los grados de paralelismo. Para obtener más información, vea Configurar operaciones de índice en paralelo.
Para obtener más información acerca de cómo SQL Server realiza las operaciones de ordenación al crear índices, vea tempdb y la creación de índices.
Vea también