Tablas e índices con particiones

Se aplica a: SQL Server Azure SQL DatabaseAzure 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 se pueden distribuir entre más de un grupo de archivos en una base de datos o almacenados 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, consulte Ediciones y características admitidas de SQL Server 2016. 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 varias 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, puede optar por comprimir datos en una o varias particiones, recompilar una o varias particiones de un índice o truncar los datos en una sola partición. También puede cambiar las particiones individuales de una tabla y en una tabla de archivo.

  • Puede mejorar el rendimiento de las consultas, en función de los tipos de consultas que se ejecutan con frecuencia. 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 en lugar de en una tabla completa. 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 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 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 que contiene datos de pedidos de ventas, puede que desee dividir la tabla en 12 particiones (mensuales) en función de una columna datetime , como una fecha de venta.

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:

  • Un intervalo LEFT especifica que el valor de límite pertenece al lado izquierdo del intervalo de valor del límite cuando el motor de base de datos ordena los valores de 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.
  • Un intervalo RIGHT especifica que el valor de límite pertenece al lado derecho del intervalo de valor del límite cuando el motor de base de datos ordena los valores de 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 índice en 12 particiones, una por cada mes de los valores de un año 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 de trabajar con al crear particiones de una tabla en función de una columna de tipos de datos 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. De forma similar, si usa el tipo de datos de fecha y usa 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 precisas al consultar los 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.

Partition 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.

Obtenga 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 varios grupos de archivos.

Busque 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. Al usar almacenamiento en capas, el uso de varios grupos de archivos permite asignar particiones específicas a niveles de almacenamiento específicos, por ejemplo, para colocar particiones más antiguas y a las que se accede con menos frecuencia en un 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 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 PRIMARY grupo de archivos en Azure SQL Database, todas las particiones deben colocarse en el PRIMARY grupo de archivos.

Busque código de ejemplo para crear grupos de archivos para SQL Server y Azure SQL Managed Instance en opciones de archivo y grupo de archivos 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 partición:

  • Las columnas calculadas que participan en una función de partición se deben crear explícitamente como PERSISTED.
    • Puesto 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 que son válidos para su uso como columnas de clave de índice se pueden usar como columna de partición, excepto la marca de tiempo.
  • No se pueden especificar columnas de tipos de datos de objetos grandes (LOB), como ntext, text, image, xml, varchar(max), nvarchar(max)y varbinary(max).
  • No se pueden especificar columnas de tipo y alias definidos 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 especifica partition_scheme_name o grupo de archivos y la tabla tiene particiones, el índice se coloca en el mismo esquema de partición, con la misma columna de partición que 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 sin particiones 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 en alineación, el motor de base de datos puede cambiar las particiones dentro o fuera de la tabla 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 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 de í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. Al crear particiones de un índice clúster no único y la columna de creación de particiones no se especifica explícitamente en la clave de agrupación en clústeres, el motor de base de datos agrega la columna de partición de forma predeterminada a la lista de claves de índice agrupadas. 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 de índices no clúster

Al crear particiones en un índice no clúster único, la clave de índice debe contener la columna de partición. Al particionar un índice no único, no agrupado, el motor de base de datos agrega la columna de partición de forma predeterminada como una columna no clave (incluida) del índice para asegurarse de que el índice está alineado con la tabla base. El motor de base de datos no agrega la columna de creación de particiones al índice si ya está presente en el índice. 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 a la tabla correspondiente. Es decir, el índice tiene un esquema de partición diferente que lo coloca en un grupo de archivos independiente o conjunto de grupos de archivos de la tabla base. El diseño de un índice con particiones no alineado 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 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 de la izquierda. Sin embargo, si se especifica NULL como el primer valor de límite y RANGE RIGHT se especifica en la definición de función de partición, la partición de la izquierda permanece vacía y las NUL se colocan en la segunda partición.

Directrices de rendimiento

El motor de base de datos admite hasta 15 000 particiones 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. En esta sección se describen las implicaciones de rendimiento del uso de más de 1000 particiones y se proporcionan soluciones alternativas 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, solo debe conservar los datos siempre que 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 un gran número de particiones están en uso. Si el sistema no tiene suficiente memoria, (DML) las instrucciones de lenguaje de manipulación de datos, (DDL) instrucciones de lenguaje de definición de datos y otras operaciones pueden generar errores debido a la 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 la capacidad del motor de base de datos 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 un índice agrupado.

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

Operaciones de índice con particiones

Es posible crear y volver a generar índices no alineados en una tabla con más de 1000 particiones, pero no se admite. Si se hace, se puede degradar el rendimiento o consumir excesiva memoria durante estas operaciones.

La creación y recompilación de índices alineados podría tardar más tiempo en ejecutarse a medida que aumenta el número de particiones. 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 compila una tabla de ordenación 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 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 solo necesita memoria suficiente para ordenar 40 páginas, ya que las ordenaciones no se realizan al mismo tiempo.

Para los índices alineados y no alineados, el requisito de memoria puede ser mayor si el motor de base de datos usa paralelismo de consultas en la operación de compilación en un equipo con varios procesadores. Esto se debe a que mayor es el grado de paralelismo (DOP), mayor será el requisito de memoria. Por ejemplo, si el motor de base de datos establece DOP en 4, un índice con particiones no alineadas 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 como DBCC CHECKDB y DBCC CHECKTABLE pueden tardar más tiempo en ejecutarse a medida que aumenta el número de particiones.

Consultas

Después de crear particiones de una tabla o índice, las consultas que usan la eliminación de particiones pueden tener un rendimiento comparable o mejorado con un mayor número de particiones. 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 divide 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 usan la misma función de partición con nombre o usan diferentes funciones de partición que son básicamente las mismas, en 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, vea 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 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.

Pasos siguientes

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