Tablas e índices con particiones

SQL Server admite la creación de particiones de tablas e índices. Los datos de tablas e índices con particiones se dividen en unidades que se pueden distribuir entre más de un grupo de archivos de una base de datos. Los datos se particionan horizontalmente, de modo que los grupos de filas se asignan a particiones individuales. Todas las particiones de un único índice o tabla deben residir en la misma base de datos. La tabla o índice se trata como una sola entidad lógica cuando se realizan consultas o actualizaciones en los datos. Las tablas e índices con particiones no están disponibles en todas las ediciones de MicrosoftSQL Server. Para obtener una lista de las características compatibles con las ediciones de SQL Server, vea Características compatibles con las ediciones de SQL Server 2014.

Ventajas de la creación de particiones

La creación de particiones de tablas o índices grandes puede tener las siguientes ventajas de administración y rendimiento.

• Puede transferir o acceder a subconjuntos de datos de forma rápida y eficaz, al tiempo que mantiene la integridad de una recopilación de datos. Por ejemplo, una operación como cargar datos de un OLTP a un sistema OLAP tarda solo segundos, en lugar de los minutos y horas que tarda la operación cuando los datos no están particionados.

• Puede realizar operaciones de mantenimiento en una o varias particiones más rápidamente. Las operaciones son más eficaces porque solo tienen como destino estos subconjuntos de datos, en lugar de toda la tabla. Por ejemplo, puede optar por comprimir datos en una o varias particiones o recompilar una o varias particiones de un índice.

• Puede mejorar el rendimiento de las consultas, en función de los tipos de consultas que se ejecutan con frecuencia y en la configuración de hardware. Por ejemplo, el optimizador de consultas puede procesar consultas de equi-join entre dos o más tablas con particiones más rápidamente cuando las columnas de partición de las tablas son las mismas, ya que se pueden combinar las propias particiones.

  Cuando SQL Server realiza la ordenación de datos para las operaciones de E/S, ordena primero los datos por partición. SQL Server accede a una unidad a la vez, lo cual puede reducir el rendimiento. Para mejorar el rendimiento de la ordenación de datos, divida los archivos de datos de las particiones en más de un disco mediante la configuración de un RAID. De este modo, aunque SQL Server sigue ordenando los datos por partición, puede acceder a todas las unidades de cada partición al mismo tiempo.

  Además, puede mejorar el rendimiento habilitando la extensión de bloqueo en el nivel de partición en lugar de toda una tabla. Esto puede reducir el conflicto de bloqueo en la tabla.

Componentes y conceptos

Los siguientes términos son aplicables a la creación de particiones de tabla e índice.

Función partition
Objeto de base de datos que define cómo se asignan las filas de una tabla o índice a un conjunto de particiones en función de los valores de una columna determinada, denominada columna de creación de particiones. Es decir, la función de partición define el número de particiones que tendrá la tabla y cómo se definen los límites de las particiones. Por ejemplo, dada una tabla que contiene datos de pedidos de ventas, puede que desee dividir la tabla en doce particiones (mensuales) basadas en una datetime columna como una fecha de venta.

Esquema de partición
Objeto de base de datos que asigna las particiones de una función de partición a un conjunto de grupos de archivos. La razón principal para colocar las particiones en grupos de archivos independientes es asegurarse de que puede realizar operaciones de copia de seguridad de forma independiente en particiones. Esto se debe a que puede realizar copias de seguridad en grupos de archivos individuales.

Columna de partición
Columna de una tabla o índice que una función de partición usa para particionar la tabla o el índice. Las columnas calculadas que participan en una función de partición deben marcarse explícitamente como PERSISTENTE. Todos los tipos de datos que son válidos para su uso como columnas de índice se pueden usar como una columna de partición, excepto timestamp. No se pueden especificar los tipos de datos ntext, text, image, xml, varchar(max), nvarchar(max) o varbinary(max). Además, no se pueden especificar columnas de tipo de datos de tipo definido por el usuario y tipo de datos de alias en el Common Language Runtime (CLR) de Microsoft .NET Framework.

Índice alineado
Índice que se basa en el mismo esquema de partición que su tabla correspondiente. Cuando una tabla y sus índices están en alineación, SQL Server puede cambiar las particiones de forma rápida y eficaz al tiempo que mantiene la estructura de partición de la tabla y sus índices. Un índice no tiene que 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 deben ser esencialmente iguales, en ese 1) los argumentos de las funciones de 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.

Índice no alineado
Índice particionado independientemente de su tabla correspondiente. Es decir, el índice tiene un esquema de partición diferente o se coloca en un grupo de archivos independiente de la tabla base. El diseño de un índice con particiones no alineado puede ser útil en los casos siguientes:

• La tabla base no se ha particionado.

• La clave de índice es única y no contiene la columna de partición de la tabla.

• Quiere que la tabla base participe en combinaciones intercaladas con más tablas mediante diferentes columnas de combinación.

Eliminación de particiones
El proceso por el que el optimizador de consultas accede solo a las particiones pertinentes para satisfacer los criterios de filtro de la consulta.

Directrices de rendimiento

El nuevo límite superior de 15 000 particiones afecta a la memoria, las operaciones de índice con particiones, los comandos DBCC y las consultas. En esta sección se describen las implicaciones de rendimiento de aumentar el número de particiones superiores a 1000 y se proporcionan soluciones alternativas según sea necesario. Con el límite en el número máximo de particiones que se incrementan a 15 000, puede almacenar datos durante más tiempo. Sin embargo, solo debe conservar los datos mientras sea necesario y mantener un equilibrio entre el rendimiento y el número de particiones.

Uso y directrices de memoria

Se recomienda usar al menos 16 GB de RAM si hay un gran número de particiones en uso. Si el sistema no tiene memoria suficiente, las instrucciones del lenguaje de manipulación de datos (DML), las instrucciones data Definition Language (DDL) y otras operaciones pueden producir errores debido a una memoria insuficiente. Los sistemas con 16 GB de RAM que ejecutan muchos procesos intensivos en memoria pueden agotarse de memoria en operaciones que se ejecutan en un gran número de particiones. Por lo tanto, cuanto más memoria tenga más de 16 GB, menos probable es que encuentre problemas de rendimiento y memoria.

Las limitaciones de memoria pueden afectar al rendimiento o la capacidad de SQL Server para crear un índice con particiones. Esto es especialmente el caso cuando el índice no está alineado con su tabla base o no está alineado con su índice agrupado, si la tabla ya tiene aplicado un índice agrupado.

Operaciones de índice particionado

Las limitaciones de memoria pueden afectar al rendimiento o la capacidad de SQL Server para crear un índice con particiones. Este es especialmente el caso de índices no alineados. La creación y regeneración de índices no alineados en una tabla con más de 1.000 particiones es posible, pero no se admite. Si se hace, se puede degradar el rendimiento o consumir excesiva memoria durante estas operaciones.

La creación y regeneración de índices alineados podría tardar más tiempo en ejecutarse a medida que aumenta el número de particiones. Se recomienda no ejecutar varios comandos de creación y recompilación del índice al mismo tiempo que puede encontrarse con problemas de rendimiento y memoria.

Cuando SQL Server realiza la ordenación para compilar índices con particiones, primero crea una tabla de ordenación para cada partición. A continuación, compila las tablas de ordenación en el grupo de archivos correspondiente de cada partición o en tempdb, si se especifica la opción de índice SORT_IN_TEMPDB. Cada tabla de ordenación requiere una cantidad mínima de memoria para compilar. Cuando se crea un índice con particiones que se alinea con su tabla base, las tablas de ordenación se crean de una en una, con menos memoria. Sin embargo, cuando se crea un índice con particiones no alineados, las tablas de ordenación se compilan al mismo tiempo. Como resultado, debe haber suficiente memoria para manejar estas ordenaciones simultáneas. Cuanto mayor sea el número de particiones, más memoria necesaria. El tamaño mínimo de cada tabla de ordenación, para cada partición, es de 40 páginas, con 8 kilobytes por página. Por ejemplo, un índice con particiones no alineados con 100 particiones requiere suficiente memoria para ordenar serialmente 4000 páginas (40 * 100) al mismo tiempo. Si esta memoria está disponible, la operación de compilación se realizará correctamente, pero el rendimiento puede sufrir. Si esta memoria no está disponible, se producirá un error en la operación de compilación. Como alternativa, un índice alineado con particiones con 100 particiones solo requiere memoria suficiente para ordenar 40 páginas, ya que las ordenación no se realizan al mismo tiempo.

Para los índices alineados y no alineados, el requisito de memoria puede ser mayor si SQL Server aplica grados de paralelismo a la operación de compilación en un equipo con varios procesadores. Esto se debe a que cuanto mayor sea el grado de paralelismo, mayor será el requisito de memoria. Por ejemplo, si SQL Server establece grados de paralelismo en 4, un índice con particiones no alineados con 100 particiones requiere memoria suficiente para que cuatro procesadores ordenen 4000 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 usar la opción de índice MAXDOP para reducir manualmente los grados de paralelismo.

Comandos DBCC

Con un mayor número de particiones, los comandos DBCC podrían tardar más tiempo en ejecutarse a medida que aumenta el número de particiones.

Preguntas

Las consultas que usan la eliminación de particiones podrían tener un rendimiento comparable o mejorado con un mayor número de particiones. Las consultas que no usan la eliminación de particiones podrían tardar más tiempo en ejecutarse a medida que aumenta el número de particiones.

Por ejemplo, supongamos que una tabla tiene 100 millones de filas y columnas A, By C. En el escenario 1, la tabla se divide en 1000 particiones en la columna A. En el escenario 2, la tabla se divide en 10 000 particiones en la columna A. Una consulta en la tabla que tiene un filtrado de cláusula WHERE en la columna A realizará la eliminación de particiones y examinará una partición. Esa misma consulta puede ejecutarse más rápido en el escenario 2, ya que hay menos filas que examinar en una partición. Una consulta que tenga un filtrado de cláusula WHERE en la columna B examinará todas las particiones. La consulta puede ejecutarse más rápido en el escenario 1 que en el escenario 2, ya que hay menos particiones que examinar.

Las consultas que usan operadores como TOP o MAX/MIN en columnas distintas de la columna de partición pueden experimentar un rendimiento reducido con la creación de particiones porque se deben evaluar todas las particiones.

Cambios de comportamiento en el cálculo de estadísticas durante las operaciones de índice con particiones

A partir de SQL Server 2012, las estadísticas no se crean examinando todas las filas de la tabla cuando se crea o se vuelve a generar un índice con particiones. En su lugar, el optimizador de consultas usa el algoritmo de muestreo predeterminado para generar estadísticas. Después de actualizar una base de datos con índices con particiones, es posible que observe una diferencia en los datos del histograma de estos índices. Este cambio en el comportamiento puede no afectar al rendimiento de las consultas. Para obtener estadísticas en índices con particiones examinando todas las filas de la tabla, use CREATE STATISTICS o UPDATE STATISTICS con la cláusula FULLSCAN.

Tareas relacionadas

Contenido relacionado

Puede encontrar las siguientes notas del producto sobre estrategias e implementaciones de índices y tablas con particiones útiles.

• Estrategias de tabla e índice con particiones mediante SQL Server 2008

• Cómo implementar una ventana deslizante automática

• Carga masiva en una tabla con particiones

• Mejoras de procesamiento de consultas en tablas e índices con particiones

• Principales 10 procedimientos recomendados para crear un almacenamiento de datos relacional a gran escala