Tablas e índices con particiones

  • Artículo

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

SQL Server, Azure SQL Database y Azure SQL Managed Instance admiten la creación de particiones de tablas e índices. Los datos de tablas e índices con particiones se dividen en unidades que pueden propagarse por más de un grupo de archivos de la base de datos o almacenarse en un único grupo de archivos. Cuando existen varios archivos en un grupo de archivos, los datos se distribuyen entre archivos mediante el algoritmo de relleno proporcional. Los datos se dividen en sentido horizontal, de forma que los grupos de filas se asignan a particiones individuales. Las particiones de un índice o una tabla deben encontrarse en la misma base de datos. La tabla o el índice se tratarán como una sola entidad lógica cuando se realicen consultas o actualizaciones en los datos.

Antes de SQL Server 2016 (13.x) SP1, las tablas e índices con particiones no estaban disponibles en todas las ediciones de SQL Server. Para obtener una lista de las características admitidas por las ediciones de SQL Server, consulta Ediciones y características admitidas de SQL Server 2022. Las tablas e índices con particiones están disponibles en todos los niveles de servicio de Azure SQL Database y Azure SQL Managed Instance.

La creación de particiones de tablas también está disponible en grupos de SQL dedicados en Azure Synapse Analytics, con algunas diferencias de sintaxis. Obtenga más información en Creación de particiones de tablas en un grupo de SQL dedicado.

Importante

El motor de base de datos admite hasta 15.000 particiones de forma predeterminada. En versiones anteriores a SQL Server 2012 (11.x), el número de particiones se limitaba a 1000 de forma predeterminada.

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.

  • Se puede transferir u obtener acceso a subconjuntos de datos de forma rápida y eficaz, a la vez que mantiene la integridad de una recopilación de datos. Por ejemplo, una operación como la carga de datos desde un sistema OLTP a un sistema OLAP tarda solo unos segundos, en lugar de los minutos y las horas que se requieren cuando no se ha realizado una partición de los datos.

  • Puede realizar operaciones de mantenimiento o retención de datos en una o más particiones más rápidamente. Las operaciones son más eficaces porque solo afectan a estos subconjuntos de datos, y no a toda la tabla. Por ejemplo, se puede elegir comprimir los datos en una o varias particiones o recompilar una o varias particiones de un índice o truncar datos en una única partición. También puede cambiar particiones individuales de una tabla y a una tabla de archivo.

  • Se puede mejorar el rendimiento de las consultas, en función de los tipos de consultas que se suelen ejecutar. Por ejemplo, el optimizador de consultas puede procesar consultas de combinación de igualdad entre dos o más tablas con particiones más rápidamente cuando las columnas de partición son las mismas que las columnas en las que se combinan las tablas. Consulte la sección Consultas a continuación para más información.

Puede mejorar el rendimiento habilitando la extensión de bloqueo en el nivel de partición y no en toda la tabla. Esto puede reducir la contención en la tabla por bloqueo. Para reducir la contención de bloqueo y permitir la extensión de bloqueo a la partición, establezca la opción LOCK_ESCALATION de la instrucción ALTER TABLE en AUTO.

Componentes y conceptos

Los siguientes términos son aplicables para las particiones de tablas e índices.

Función de partición

Una función de partición es un objeto de base de datos que define la forma de asignar las filas de una tabla o un índice a un conjunto de particiones a partir de los valores de una determinada columna, denominada columna de partición. Cada valor de la columna de partición es una entrada a la función de partición, que devuelve un valor de partición.

La función de partición define el número de particiones y los límites de las particiones que tendrá la tabla. Por ejemplo, dada una tabla con datos de pedidos de ventas, puede crear 12 particiones (mensuales) de la tabla tomando como base una columna datetime, por ejemplo una fecha de ventas.

Un tipo de intervalo (LEFT o RIGHT), especifica cómo se colocarán los valores de límite de la función de partición en las particiones resultantes:

  • LEFT especifica que el intervalo de valores de límite pertenece al lado izquierdo del valor de límite, cuando el Motor de base de datos ordena los valores del intervalo en orden ascendente de izquierda a derecha. En otras palabras, el valor de límite más alto se incluirá dentro de una partición.
  • RIGHT especifica que el intervalo de valores de límite pertenece al lado derecho del valor de límite, cuando el Motor de base de datos ordena los valores del intervalo en orden ascendente de izquierda a derecha. En otras palabras, el valor de límite más bajo se incluirá en cada partición.

Si no se especifica LEFT o RIGHT, el intervalo LEFT es el valor predeterminado.

Por ejemplo, la siguiente función de partición divide una tabla o un índice en 12 particiones, una para cada mes de un año natural de valores en una columna datetime. Se usa un intervalo RIGHT, que indica que los valores de límite servirán como valores de límite inferior en cada partición. Los intervalos RIGHT suelen ser más sencillos a la hora de trabajar con ellos para crear particiones de una tabla basada en una columna de tipos de fatos datetime o datetime2, ya que las filas con un valor de medianoche se almacenarán en la misma partición que las filas con valores posteriores en el mismo día. Del mismo modo, si se usa el tipo de datos de date y el uso de particiones de un mes o más, un intervalo RIGHT mantiene el primer día del mes en la misma partición que los días posteriores de ese mes. Esto ayuda a eliminar particiones de forma precisa al consultar el valor de datos de un día completo.

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)  
AS RANGE RIGHT FOR VALUES ('2022-02-01', '2022-03-01', '2022-04-01',  
               '2022-05-01', '2022-06-01', '2022-07-01', '2022-08-01',   
               '2022-09-01', '2022-10-01', '2022-11-01', '2022-12-01');

En la tabla siguiente se muestra cómo se crearían particiones en una tabla o un índice que usa esta función de partición en la columna de partición datecol. El 1 de febrero es el primer punto de límite definido en la función, por lo que actúa como el límite inferior de la partición 2.

Partición 1 2 ... 11 12
Valores datecol<2022-02-01 12:00AM datecol>= 2022-02-01 12:00AM AND datecol<2022-03-01 12:00AM datecol>= 2022-11-01 12:00AM AND col1<2022-12-01 12:00AM datecol>= 2022-12-01 12:00AM

Para RANGE LEFT y RANGE RIGHT, la partición situada más a la izquierda tiene el valor mínimo del tipo de datos como límite inferior y la partición situada más a la derecha tiene el valor máximo del tipo de datos como límite superior.

Encontrará más ejemplos de funciones de partición LEFT y RIGHT en CREATE PARTITION FUNCTION (Transact-SQL).

Esquema de partición

Un esquema de partición es un objeto de base de datos que asigna las particiones de una función de partición a un grupo de archivos o a múltiples grupos de archivos.

Encontrará una sintaxis de ejemplo para crear esquemas de partición en CREATE PARTITION SCHEME (Transact-SQL).

Grupos de archivos

La principal razón para colocar las particiones en varios grupos de archivos es garantizar que se puedan realizar operaciones de copia de seguridad y restauración en particiones de forma independiente. Esto se debe a que se pueden realizar copias de seguridad en grupos de archivos individuales. Cuando se usa almacenamiento en capas, el uso de varios grupos de archivos permite asignar particiones específicas a niveles de almacenamiento específicos, por ejemplo, colocar particiones más antiguas y con menos frecuencia a las que se accede con menos frecuencia en almacenamiento más lento y menos costoso. Todas las demás ventajas de las particiones se aplican independientemente del número de grupos de archivos usados o de la colocación de particiones en grupos de archivos específicos.

La administración de archivos y grupos de archivos para tablas con particiones puede agregar una complejidad significativa a las tareas administrativas a lo largo del tiempo. Si los procedimientos de copia de seguridad y restauración no se benefician del uso de varios grupos de archivos, se recomienda un único grupo de archivos para todas las particiones. Las mismas reglas para diseñar archivos y grupos de archivos se aplican a los objetos con particiones que se aplican a objetos sin particiones.

Nota:

La creación de particiones es totalmente compatible con Azure SQL Database. Dado que solo se admite el grupo de archivos PRIMARY en Azure SQL Database, todas las particiones deben colocarse en el grupo de archivos PRIMARY.

Encontrará código de ejemplo para crear grupos de archivos para SQL Server y Azure SQL Managed Instance en Opciones File y Filegroup de ALTER DATABASE (Transact-SQL).

Columna de partición

La columna de una tabla o índice que una función de partición usa para crear particiones en la tabla o índice. Se aplican las consideraciones siguientes al seleccionar una columna de creación de particiones:

  • Las columnas calculadas que participan en una función de partición deben crearse explícitamente como PERSISTED.
    • Dado que solo se puede usar una columna como columna de partición, en algunos casos la concatenación de varias columnas con una columna calculada puede ser útil.
  • Las columnas de todos los tipos de datos válidos para el uso en columnas de clave de índice pueden utilizarse como una columna de partición con la excepción de timestamp.
  • Las columnas de tipos de datos de objetos grandes (LOB) ntext, text, image, xml, varchar(max), nvarchar(max) y varbinary(max), no pueden especificarse.
  • Tampoco se pueden especificar columnas de tipo de datos de alias y de tipo definido por el usuario de Common Language Runtime (CLR) de Microsoft .NET Framework.

Para crear particiones de un objeto, especifique el esquema de partición y la columna de partición en las instrucciones CREATE TABLE (Transact-SQL), ALTER TABLE (Transact-SQL) y CREATE INDEX (Transact-SQL).

Al crear un índice no agrupado, si no se especificó partition_scheme_name o filegroup y se crearon particiones en la tabla, el índice se coloca en el mismo esquema de partición y usa la misma columna de partición que en la tabla subyacente. Para cambiar cómo se particiona un índice existente, use CREATE INDEX con la cláusula DROP_EXISTING. Esto le permite particionar un índice sin particiones, crear un índice con particiones no particionado o cambiar el esquema de partición del índice.

Índices alineados

Un índice que se compila con el mismo esquema de partición que su tabla correspondiente. Cuando una tabla y sus índices están alineados, el motor de base de datos puede cambiar las particiones de la tabla 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. Un índice no tiene por qué participar en la misma función de partición con nombre para alinearse con su tabla básica. Sin embargo, la función de partición del índice y la tabla base deben ser básicamente iguales, dado que:

  • Los argumentos de las funciones de partición tienen el mismo tipo de dato.
  • Definen el mismo número de particiones.
  • Definen los mismos valores de límite para las particiones.

Creación de particiones en índices agrupados

Al crear particiones en un índice clúster, la clave de agrupación en clústeres 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 en clústeres, el motor de base de datos agrega de forma predeterminada la columna de partición a la lista de claves de índice agrupado. Si el índice clúster es único, deberá especificar explícitamente que la clave de índice clúster contiene la columna de partición. Para obtener más información sobre los índices agrupados y la arquitectura de índices, vea Instrucciones de diseño de índices agrupados.

Creación de particiones en índices no agrupados

Al crear particiones en un índice no clúster único, la clave de índice debe contener la columna de partición. Al crear particiones de un índice no agrupado que no es único, el motor de base de datos agrega de forma predeterminada la columna de partición como una columna sin clave (incluida) del índice para asegurarse de que este está alineado con la tabla base. El motor de base de datos no agrega la columna de partición al índice si ya está presente en él. Para obtener más información sobre los índices no agrupados y la arquitectura de índices, vea Instrucciones de diseño de índices no agrupados.

Índice no alineado

Un índice no alineado se particiona de forma diferente de la tabla correspondiente. Es decir, el índice tiene un esquema de partición diferente o está colocado en un grupo de archivos o conjunto de grupos de archivos independiente de la tabla base. El diseño de un índice con particiones no alineado puede ser útil en los siguientes casos:

  • En la tabla básica 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 por colocación con más tablas usando columnas de combinación diferentes.

Eliminación de particiones

El proceso por el que el optimizador de consultas tiene acceso únicamente a las particiones pertinentes para cumplir los criterios de filtro de la consulta.

Obtenga más información sobre la eliminación de particiones y los conceptos relacionados en Mejoras de procesamiento de consultas en tablas e índices con particiones.

Limitaciones

  • El ámbito de una función y un esquema de partición se limita a la base de datos en la que se han creado. En la base de datos, las funciones de partición residen en un espacio de nombres independiente de las demás funciones.

  • Si alguna fila de una tabla con particiones tiene valores NULL en la columna de creación de particiones, estas filas se colocan en la partición más a la izquierda. Sin embargo, si se especifica NULL como primer valor de límite y se especifica RANGE RIGHT en la definición de función de partición, la partición situada más a la izquierda permanece vacía y los valores NULL se colocan en la segunda partición.

Directrices de rendimiento

El motor de base de datos admite hasta 15.000 particiones de forma predeterminada por tabla o índice. Sin embargo, el uso de más de 1000 particiones tiene implicaciones en la memoria, las operaciones de índice con particiones, los comandos DBCC y las consultas. Esta sección describe las implicaciones para el rendimiento de usar más 1.000 particiones y proporciona soluciones según sea necesario.

Con hasta 15 000 particiones permitidas por tabla o índice con particiones, puede almacenar datos durante largas duraciones en una sola tabla. Sin embargo, debe conservar datos solo mientras sea necesario y mantener un equilibrio entre el rendimiento y el número de particiones.

Instrucciones y uso de la memoria

Se recomienda usar al menos 16 GB de RAM si un gran número de particiones están en uso. Si el sistema no tiene suficiente memoria, las instrucciones de lenguaje de manipulación de datos (DML), las instrucciones de lenguaje de definición de datos (DDL) y otras operaciones pueden generar errores debido a memoria insuficiente. Los sistemas con 16 GB de RAM que funcionan con muchos procesos intensivos de memoria pueden quedarse sin memoria en operaciones que ejecutan en un gran número de particiones. Por consiguiente, cuanta más memoria se tenga por encima de 16 GB menor es la posibilidad de enfrentarse a problemas de rendimiento y de memoria.

Las limitaciones de memoria pueden afectar al rendimiento o capacidad del motor de base de datos para crear un índice con particiones. Esto sucede especialmente cuando el índice no está alineado con su tabla básica o no está alineado con su índice agrupado si la tabla ya tiene un índice agrupado.

En SQL Server y Azure SQL Managed Instance, puede aumentar la opción index create memory (KB) de configuración del servidor. Para más información, vea Establecer la opción de configuración del servidor Memoria para creación de índices. Para Azure SQL Database, considere la posibilidad de aumentar temporalmente o permanentemente el objetivo de nivel de servicio de la base de datos en Azure Portal para asignar más memoria.

Operaciones de índices con particiones

La creación y recompilación de índices no alineados en una tabla con más de 1000 particiones es posible, pero no se admite. Si se hace, se puede degradar el rendimiento o consumir excesiva memoria durante estas operaciones.

Crear y recompilar índices alineados podría tardar mucho más tiempo en ejecutarse cuando el número de particiones aumenta. Se recomienda que no ejecute varios comandos de crear y recompilar índices al mismo tiempo, ya que quizás se enfrente a problemas de rendimiento y memoria.

Cuando el motor de base de datos realiza la ordenación para crear índices con particiones, primero crea una tabla de orden para cada partición. Luego, compila 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 compilación. Cuando crea un índice con particiones que está alineado con su tabla básica, las tablas de ordenación se crean de una en una con menos memoria. Sin embargo, cuando crea un índice con particiones no alineado, las tablas de ordenación 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 solo 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 el motor de base de datos usa grados de paralelismo a la operación de creación en un equipo con varios procesadores. Esto es así porque cuanto mayor es el grado de paralelismo (DOP), mayor es también el requisito de memoria. Por ejemplo, si el motor de base de datos establece los grados de paralelismo (DOP) 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 usar la opción de índice MAXDOP para reducir manualmente los grados de paralelismo.

Comandos DBCC

Con un número de particiones mayor, los comandos DBCC como DBCC CHECKDB y DBCC CHECKTABLE pueden tardar más tiempo en ejecutarse cuando el número de particiones aumenta.

Consultas

Tras realizar una partición en una tabla o índice, las consultas en que se usa la eliminación de particiones pueden tener un rendimiento comparable o mejorado con un número de particiones mayor. Las consultas que no usan eliminación de particiones podrían tardar más tiempo en ejecutarse cuando el número de particiones aumenta.

Por ejemplo, suponga que una tabla tiene 100 millones de filas y columnas A, By C.

  • En el escenario 1, la tabla se dividen en 1000 particiones en la columna A.
  • En el escenario 2, la tabla se dividen en 10.000 particiones en la columna A.

Una consulta en la tabla que tenga un filtrado de cláusulas WHERE en la columna A realizará la eliminación de la partición y examinará una partición. La misma consulta puede ejecutarse con mayor rapidez en el escenario 2 al haber menos filas para examinar en una partición. Una consulta que tenga un filtrado de cláusulas WHERE en la columna B examinará todas las particiones. La consulta puede ejecutarse con mayor rapidez en el escenario 1 que en el escenario 2 ya que hay menos particiones para examinar.

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

De forma similar, una consulta que realiza una búsqueda de una sola fila o un examen de intervalo pequeño tardará más en una tabla con particiones que en una tabla sin particiones si el predicado de consulta no incluye la columna de creación de particiones, ya que tendrá que realizar tantas búsquedas o exámenes como haya particiones. Por este motivo, la creación de particiones rara vez mejora el rendimiento en los sistemas OLTP en los que estas consultas son comunes.

Si ejecuta con frecuencia consultas que implican una combinación de igualdad entre dos o más tablas con particiones, sus columnas con particiones deberían ser las mismas que las columnas en las que se combinan las tablas. Además, las tablas, o sus índices, deberían colocarse. Esto significa que, o bien utilizan la misma función de partición, o bien utilizan funciones de partición distintas que son esencialmente la misma, dado que:

  • Tienen el mismo número de parámetros que se utilizan para crear particiones y los parámetros correspondientes son de los mismos tipos de datos.
  • Definen el mismo número de particiones.
  • Definen los mismos valores de límite para las particiones.

De este modo, el optimizador de consultas puede procesar la combinación con mayor rapidez, porque las propias particiones se pueden combinar. Si una consulta combina dos tablas que no están colocadas o no tienen particiones en el campo de combinación, la presencia de particiones puede ralentizar el procesamiento de consultas en lugar de acelerarlo.

Es posible que le resulte útil usar $PARTITION en algunas consultas. Obtenga más información en $PARTITION (Transact-SQL).

Para obtener más información sobre el control de particiones en el procesamiento de consultas, incluida la estrategia de ejecución de consultas paralela para tablas e índices con particiones y procedimientos recomendados adicionales, consulte Mejoras de procesamiento de consultas en tablas e índices con particiones.

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

En Azure SQL Database, Azure SQL Managed Instance y SQL Server 2012 (11.x) y versiones posteriores, 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 a partir de una versión de SQL Server inferior a 2012 (11.x), puede observar una diferencia en los datos de histograma para estos índices. Este cambio de comportamiento puede no afectar al rendimiento de las consultas. Para obtener estadísticas sobre índices con particiones examinando todas las filas de la tabla, use CREATE STATISTICS o UPDATE STATISTICS con la cláusula FULLSCAN.

Contenido relacionado

Obtenga más información sobre la partición de tablas y estrategias de índice en los artículos siguientes: