SQL Server
Simplificación del mantenimiento de bases de datos con particiones de tablas
Noah Gomez
Resumen:
- Creación de tablas con particiones
- Adición y combinación de particiones
- Creación y administración de índices con particiones
Descargar el código de este artículo: GomezPartitioning2007_03.exe (156KB)
En el pasado, los administradores de bases de datos que administraban tablas con millones de filas de datos se veían obligados a crear varias tablas. Una vez que se creaban particiones de estas tablas, el administrador tenía que volverlas
a unir durante la ejecución de un gran número de consultas. La unión de particiones implicaba la creación de una vista con particiones o un procedimiento almacenado de contenedor que averiguara dónde se encontraban los datos y que ejecutara otro procedimiento almacenado con el fin de buscar sólo en las particiones necesarias para devolver el conjunto de datos.
Aunque estos métodos funcionaban, implicaban mucho trabajo. La administración de varias tablas y sus índices, así como los métodos utilizados para unirlas de nuevo a menudo daban como resultado problemas de administración y mantenimiento. Además, la creación de varias tablas para dividir los datos implicaba cierto grado de inflexibilidad, ya que los procedimientos almacenados, los trabajos de mantenimiento, los trabajos de Servicios de transformación de datos (DTS), el código de aplicación y otros procesos tenían que entender la naturaleza de la partición. Así, para que se pudieran agregar o eliminar estas semi-particiones sin modificar el código, estos elementos se creaban normalmente de una forma no dinámica y, como resultado, no eran eficaces.
Las ediciones Enterprise y Developer de SQL Server™ 2005 permiten dividir grandes cantidades de datos contenidos en una única tabla en varias particiones más pequeñas que se pueden administrar y mantener de forma más eficaz. La capacidad para crear segmentos de datos a los que se tiene acceso mediante un único punto de entrada reduce en gran medida los problemas de administración que surgen con el método antiguo. El uso de un único punto de entrada (el nombre de tabla o de índice) oculta los múltiples segmentos de datos del código de aplicación y permite al administrador o al desarrollador cambiar las particiones según sea necesario, sin tener que ajustar el código base.
En resumen, puede crear varias particiones, mover esas particiones, eliminar particiones antiguas e incluso cambiar el modo en que se crean las particiones de los datos sin tener nunca que ajustar el código de su aplicación. Su código de aplicación sencillamente sigue llamando al mismo nombre de tabla base o índice. Mientras tanto, puede reducir la cantidad de datos que contienen los índices individuales, al reducir a su vez los tiempos de mantenimiento de esos índices, y puede aumentar la velocidad de la carga de datos al cargar particiones vacías.
Técnicamente, todas las tablas de SQL Server 2005 se dividen, es decir, cada tabla tiene al menos una partición. Lo que hace SQL Server 2005 es permitir que los administradores de bases de datos creen particiones adicionales en cada tabla. Las particiones de tabla y de índice son particiones de nivel de fila difíciles de definir (la creación de particiones por columnas no está permitida) que permiten un único punto de entrada (el nombre de tabla o de índice) sin que el código de aplicación tenga que conocer el número de particiones tras ese punto de entrada. Las particiones se pueden crear en la tabla base y en los índices asociados con la tabla.
Creación de tablas con particiones
Las funciones y esquemas de particiones se utilizan para crear una tabla que tenga la capacidad de desarrollarse más allá de la única partición predeterminada. Estos objetos son los que permiten dividir los datos en segmentos específicos y controlar dónde se almacenan estos segmentos de datos dentro del diseño de almacenamiento. Puede, por ejemplo, distribuir los datos en varios conjuntos de discos en función de la antigüedad de los datos o según otros factores diferenciadores comunes. Tenga en cuenta que se puede dividir una tabla tomando como base una columna de la tabla y cada partición debe contener los datos que no se pueden colocar en el resto de particiones.
Funciones de partición Al crear una partición de una tabla, la primera decisión que tiene que tomar es cómo quiere dividir los datos en segmentos diferentes. Se utiliza una función de partición para asignar filas individuales de datos en diferentes particiones. Estas filas individuales de datos pueden venir asignadas por cualquier tipo de columna, excepto text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), tipos de datos de alias o tipos de datos definidos por el usuario de Common Language Runtime (CLR). Sin embargo, la función de creación de particiones debe poder colocar una fila de datos en únicamente una partición de tabla; no puede permitir que una fila de datos pertenezca a varias particiones al mismo tiempo.
Para crear una partición de una tabla, debe crear la columna de partición en la tabla de destino. Esta columna de partición puede existir en el esquema de tablas cuando la tabla se crea por primera vez o puede modificar la tabla y agregar la columna en una fecha posterior. La columna puede aceptar valores NULL, pero todas las filas que contengan valores NULL se colocarán de forma predeterminada en la partición del extremo izquierdo de la tabla. Para evitar que ocurra esto, especifique que los valores NULL se coloquen en la partición más a la derecha de la tabla cuando cree la función de partición. El uso de la partición izquierda o derecha constituye una decisión importante de diseño, ya que modifica el esquema de partición y agrega más particiones o elimina las existentes.
Al crear una función de partición, puede elegir una función de partición LEFT (izquierda) o RIGHT (derecha). La diferencia entre las particiones LEFT y RIGHT es el lugar en el que se colocan los valores límite dentro del esquema de partición. Las particiones LEFT (que son las predeterminadas) incluyen el valor límite en la partición, mientras que las particiones RIGHT colocan este valor en la partición siguiente.
Para entender este concepto algo mejor, veamos unas particiones LEFT y RIGHT sencillas:
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
CREATE PARTITION FUNCTION Right_Partition (int) AS RANGE RIGHT
FOR VALUES (1,10,100)
En la primera función (Left_Partition), los valores 1, 10 y 100 se colocan en las particiones primera, segunda y tercera, respectivamente. En la segunda función (Right_Partition), los valores se colocan en las particiones segunda, tercera y cuarta.
Al crear una tabla con particiones, es importante conseguir que las particiones sean lo más uniformes posible. Esto le ayudará a entender el espacio necesario para una partición. El uso de LEFT y RIGHT determinará el lugar en que se colocarán los datos, y este, a su vez, determinará el tamaño de la partición y el tamaño de cualquier índice creado en esta partición.
Puede determinar el número de partición en el que se colocará un valor de datos mediante la función $PARTITION, tal y como se muestra a continuación:
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Right_Partition (10)
En la primera instrucción SELECT, el resultado será 2. La segunda instrucción SELECT devolverá 3.
Esquemas de partición Tras crear la función y decidir cómo se dividirán los datos, tiene que decidir en qué lugar del subsistema de disco se crearán las particiones individuales. Utilice los esquemas de partición para crear este diseño de disco. Los esquemas de partición administran el almacenamiento de discos de particiones individuales mediante el uso de grupos de archivos para colocar cada partición en una ubicación del subsistema de disco. Puede configurar esquemas de partición para colocar todas las particiones en un solo grupo de archivos, colocar todas las particiones en grupos de archivos diferentes o que varias particiones compartan grupos de archivos. Este último método proporciona al administrador de bases de datos una gran flexibilidad al distribuir la E/S del disco.
En la figura 1 se muestran algunos métodos que puede utilizar para asignar un grupo de archivo o varios a un esquema de partición. Debe tener en cuenta que los grupos de archivos que utilice el esquema de partición deben existir previamente en la base de datos antes de crear el esquema divisor.
Figure 1 Asignación de grupos de archivos a un esquema de partición
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the different filegroups
CREATE PARTITION SCHEME Different_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
TO (Filegroup1, Filegroup2, Filegroup3, Filegroup4)
--Filegroups must currently exist in database
--Place multiple partitions into the different filegroups
CREATE PARTITION SCHEME Multiple_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
TO (Filegroup1, Filegroup2, Filegroup1, Filegroup2)
--Filegroups must currently exist in database
Si crea las funciones de partición de ejemplo mostradas en la figura 1 y utiliza el esquema de partición para crear una tabla, podrá determinar en qué lugar de las tablas recién divididas se colocarán las filas de datos individuales. A continuación, puede ver la distribución de estas filas de datos después de que se inserten en la tabla con particiones. El código de todo este proceso tendrá un aspecto parecido al que se muestra en la figura 2.
Figure 2 Colocación de filas de datos y visualización de la distribución
--Prepare database
IF OBJECT_ID('Partitioned_Table') IS NOT NULL
DROP TABLE Partitioned_Table
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
ALL TO ([PRIMARY])
CREATE TABLE Partitioned_Table
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
--Determine where values will be placed (this is not required)
--You should try to do this before executing the code
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (2)
SELECT $PARTITION.Left_Partition (3)
SELECT $PARTITION.Left_Partition (4)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (11)
SELECT $PARTITION.Left_Partition (12)
SELECT $PARTITION.Left_Partition (13)
SELECT $PARTITION.Left_Partition (14)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
SELECT $PARTITION.Left_Partition (102)
SELECT $PARTITION.Left_Partition (103)
SELECT $PARTITION.Left_Partition (104)
--Insert data into partitioned table
INSERT INTO Partitioned_Table VALUES (1,'Description')
INSERT INTO Partitioned_Table VALUES (2,'Description')
INSERT INTO Partitioned_Table VALUES (3,'Description')
INSERT INTO Partitioned_Table VALUES (4,'Description')
INSERT INTO Partitioned_Table VALUES (10,'Description')
INSERT INTO Partitioned_Table VALUES (11,'Description')
INSERT INTO Partitioned_Table VALUES (12,'Description')
INSERT INTO Partitioned_Table VALUES (13,'Description')
INSERT INTO Partitioned_Table VALUES (14,'Description')
INSERT INTO Partitioned_Table VALUES (100,'Description')
INSERT INTO Partitioned_Table VALUES (101,'Description')
INSERT INTO Partitioned_Table VALUES (102,'Description')
INSERT INTO Partitioned_Table VALUES (103,'Description')
INSERT INTO Partitioned_Table VALUES (104,'Description')
--View the distribution of data in the partitions
SELECT ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('Partitioned_Table')
Modificación de tablas con particiones
Aunque se haya realizado una planeación cuidadosa por adelantado, en ocasiones tendrá que ajustar las tablas con particiones una vez que se han creado y rellenado. Su esquema de partición puede funcionar tal y como esperaba, pero puede que, por ejemplo, necesite agregar nuevas particiones conforme se acumulan datos nuevos o quizás tenga que eliminar de golpe grandes cantidades de datos divididos. Afortunadamente, las tablas con particiones y las estructuras subyacentes de partición permiten que se realicen cambios una vez que se ha creado la tabla y rellenado con datos.
Adición de particiones Hay muchos planes de partición que incluyen la capacidad de agregar una partición nueva en una fecha futura. Este punto en el tiempo puede ser una fecha específica o puede depender de un valor en una columna de identidad incremental. Sin embargo, si no ha planeado esto por adelantado, puede llevar a cabo este procedimiento en una fecha posterior y agregar particiones nuevas a una tabla con particiones. Observe la tabla creada en la figura 2. Puede agregar una partición nueva a esta tabla que contenga los valores superiores a 500, de la siguiente forma:
--Determine where values live before new partition
SELECT $PARTITION.Left_Partition (501) --should return a value of 4
--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(500)
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (501) --should return a value of 5
La capacidad para agregar particiones proporciona una gran flexibilidad. En la figura 3 se muestra cómo puede agregar una partición al lado izquierdo de la función. En este caso, necesita indicar al esquema de partición el lugar en el que debe colocar la nueva partición ya que ha agotado todos los grupos de archivos que se crearon cuando generó por primera vez el esquema de partición. Incluso aunque utilice el grupo de archivos PRIMARY para todas las particiones, aún tiene que indicar al esquema de partición que vuelva a utilizar el grupo de archivos PRIMARY para la partición nueva.
Figure 3 Adición de una partición al lado izquierdo de la función
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5) --should return a value of 2
SELECT $PARTITION.Left_Partition (1) --should return a value of 1
SELECT $PARTITION.Left_Partition (10) --should return a value of 2
--Add new filegroups to the partitioning scheme
ALTER PARTITION SCHEME Primary_Left_Scheme
NEXT USED [PRIMARY]
--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(5)
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5) --should return a value of 2
SELECT $PARTITION.Left_Partition (1) --should return a value of 1
SELECT $PARTITION.Left_Partition (10) --should return a value of 3
Combinación de dos particiones SQL Server permite eliminar particiones únicas de una tabla al tiempo que mantiene los datos. De esta forma, puede combinar datos activos más antiguos con datos archivados, o reducir el número de particiones que tiene, y aliviar así la carga de administración de la tabla con particiones. También puede utilizar este procedimiento para combinar particiones, y mover los datos de un grupo de archivos a otro para liberar espacio en disco en algunos conjuntos de discos. El código de la figura 4 muestra cómo puede mover los datos de una partición a otra dentro del mismo grupo de archivos.
Figure 4 Traslado de los datos de una partición a otra
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
--Merge two partitions
ALTER PARTITION FUNCTION Left_Partition()
MERGE RANGE (10)
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
Traslado de una tabla de una sola partición a una tabla con varias particiones Durante las rutinas de carga, a menudo hay grandes cantidades de datos que deben cargarse en la base de datos y, a continuación, modificarse o agregarse antes de que se trasladen a la tabla de datos real. La creación de particiones de SQL Server 2005 permite desplazar una tabla con una sola partición a una tabla con varias particiones. Esto significa que puede cargar datos en una sola tabla de carga, modificar estos datos y, a continuación, desplazar la tabla entera a una tabla existente sin la carga que supone desplazar cada fila individual de datos. Esta capa de partición no implica la alteración de las estructuras de partición subyacentes. Lo que implica es la modificación de la tabla con particiones. El código de la figura 5 muestra cómo puede lograr esto.
Figure 5 Traslado de una tabla completa a una tabla existente
--Prepare database
IF OBJECT_ID(‘multiple_partition’) IS NOT NULL
DROP TABLE multiple_partition
GO
IF OBJECT_ID(‘single_partition’) IS NOT NULL
DROP TABLE single_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = ‘Primary_Left_Scheme’)
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = ‘Left_Partition’)
DROP PARTITION FUNCTION Left_Partition
GO
--Create single partition table
CREATE TABLE single_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)
--Table must have a CHECK Constraint
ALTER TABLE single_partition
WITH CHECK
ADD CONSTRAINT CK_single_partition
CHECK (col1 > 100)
INSERT INTO single_partition VALUES (101,’Description’)
INSERT INTO single_partition VALUES (102,’Description’)
INSERT INTO single_partition VALUES (103,’Description’)
INSERT INTO single_partition VALUES (104,’Description’)
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
INSERT INTO multiple_partition VALUES (1,’Description’)
INSERT INTO multiple_partition VALUES (2,’Description’)
INSERT INTO multiple_partition VALUES (3,’Description’)
INSERT INTO multiple_partition VALUES (4,’Description’)
INSERT INTO multiple_partition VALUES (10,’Description’)
INSERT INTO multiple_partition VALUES (11,’Description’)
INSERT INTO multiple_partition VALUES (12,’Description’)
INSERT INTO multiple_partition VALUES (13,’Description’)
INSERT INTO multiple_partition VALUES (14,’Description’)
INSERT INTO multiple_partition VALUES (100,’Description’)
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_
ID(‘single_partition’))
--Move the single table into the partitioned table
ALTER TABLE single_partition SWITCH TO multiple_partition PARTITION 4
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_ID(‘single_partition’))
Traslado de una partición de una tabla a otra Una tarea administrativa común es la de trasladar los datos más antiguos a tablas separadas de archivo. El proceso de archivado implica generalmente una serie de instrucciones que pueden generar un uso adicional de recursos en los registros de transacciones. No obstante, el cambio de propiedad de una partición de una tabla a otra es un método sencillo para archivar grandes cantidades de datos sin causar una carga en el registro de transacciones. Esta característica permite al administrador de bases de datos trasladar los segmentos de datos más antiguos de las tablas activas a las archivadas. Sin embargo, puesto que los datos no se mueven realmente, la cantidad de tiempo que consume este proceso puede ser notablemente inferior al que se emplea cuando se trasladan filas individuales de datos. En la figura 6 se muestra cómo puede realizar este procedimiento.
Figure 6 Traslado de los datos más antiguos a las tablas archivadas
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO
IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Archive_Scheme')
DROP PARTITION SCHEME Archive_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Archive_Partition')
DROP PARTITION FUNCTION Archive_Partition
GO
--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Create archive function
CREATE PARTITION FUNCTION Archive_Partition (int) AS RANGE LEFT
FOR VALUES (100,200,300)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Archive_Scheme
AS PARTITION Archive_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)
CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Archive_Scheme (col1)
INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')
INSERT INTO archive_data VALUES (200,'Description')
INSERT INTO archive_data VALUES (300,'Description')
INSERT INTO archive_data VALUES (400,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data PARTITION 1
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
Uso de una sola partición para crear una tabla nueva Puede mover una sola partición de una tabla con particiones existente a una tabla vacía sin particiones. Al hacerlo, un administrador de bases de datos puede realizar el mantenimiento de índices en la partición individual o eliminar fácilmente grandes cantidades de datos sin tener que registrar el proceso de eliminación. El ejemplo de la figura 7 muestra cómo trasladar una partición a una tabla vacía y, a continuación, utilizar esta tabla nueva para colocar los datos.
Figure 7 Traslado y eliminación de datos
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO
IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO
--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)
CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)
INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Drop all archive data without logging
DROP TABLE archive_data
GO
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
Índices con particiones
La capacidad para crear particiones de los datos de una tabla incluye también la capacidad para crear índices con particiones. Esta capacidad permite al administrador de bases de datos diseñar la estructura del índice en función de los datos divididos en lugar de basarse en los datos de la tabla completa. La creación de índices con particiones da como resultado árboles B individuales en los índices con particiones. La división de los índices tiene como efecto el crear índices más pequeños que el motor de almacenamiento puede mantener con mayor facilidad durante la modificación, la adición o la eliminación de datos. Estos índices más pequeños también los puede mantener individualmente el administrador de bases de datos, lo que facilita el mantenimiento en conjuntos de datos grandes.
Creación de índices con particiones Al crear índices con particiones, puede crear índices alineados o no alineados. Con los índices alineados, debe crear el índice con una relación directa con los datos divididos. Con los índices no alineados, tendrá que elegir un esquema diferente de partición.
El método preferido es el alineado y se realizará automáticamente si crea la tabla de partición y, a continuación, crea los índices sin especificar un esquema diferente de partición. El uso de índices alineados ofrece la flexibilidad de crear particiones adicionales en la tabla y la capacidad de cambiar la propiedad de una partición a otra tabla. Estas capacidades son a menudo el motivo por el que los administradores de bases de datos crean tablas con particiones en primer lugar, y el uso sencillo del esquema de partición de la tabla en sus índices le permitirá probablemente alcanzar sus objetivos de partición.
Puede crear índices en tablas en las que los datos del índice no estén alineados con los datos de la tabla. Si los datos se encuentran en una tabla con particiones, podrá unir los datos de diferentes formas (los datos divididos pueden unirse de manera eficaz con otros datos divididos mediante el optimizador de consultas). De manera alternativa, puede realizar este procedimiento con una tabla sin particiones, lo que permite crear un índice con particiones (en la tabla de una sola partición) para reducir el mantenimiento de los índices.
El código de la figura 8 creará un índice sin agrupar con particiones en una tabla con particiones. El índice no agrupado se alineará con la tabla y utilizará la columna de partición de la tabla como clave del índice no agrupado.
Figure 8 Índice con particiones sin agrupar en tabla con particiones
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
--Create partitioned non-clustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
partition(col1)
INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Verify index partitions
SELECT partition_id, index_id FROM sys.partitions pt
WHERE pt.[object_id] = OBJECT_ID('multiple_partition')
El código de la figura 9 creará un índice sin alinear y sin agrupar en una tabla con particiones. Este índice no agrupado utilizará columnas diferentes para su clave de índice, que se puede utilizar en combinaciones intercaladas en otras tablas con particiones.
Figure 9 Índice no alineado sin agrupar en tabla con particiones
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Index_primary_Left_Scheme')
DROP PARTITION SCHEME Index_primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Index_Left_Partition')
DROP PARTITION FUNCTION Index_Left_Partition
GO
--Create partitioned index function
CREATE PARTITION FUNCTION Index_Left_Partition (int) AS RANGE LEFT
FOR VALUES (10,50,100)
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all index partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Index_primary_Left_Scheme
AS PARTITION Index_Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT
,col2 INT
) ON Primary_Left_Scheme (col1)
--Create non-aligned partitioned nonclustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
partition(col2)
ON Index_primary_Left_Scheme (col2)
INSERT INTO multiple_partition VALUES (1,10)
INSERT INTO multiple_partition VALUES (2,10)
INSERT INTO multiple_partition VALUES (3,10)
INSERT INTO multiple_partition VALUES (4,10)
INSERT INTO multiple_partition VALUES (10,50)
INSERT INTO multiple_partition VALUES (11,50)
INSERT INTO multiple_partition VALUES (12,50)
INSERT INTO multiple_partition VALUES (13,50)
INSERT INTO multiple_partition VALUES (14,50)
INSERT INTO multiple_partition VALUES (100,100)
INSERT INTO multiple_partition VALUES (101,100)
INSERT INTO multiple_partition VALUES (102,100)
INSERT INTO multiple_partition VALUES (103,100)
INSERT INTO multiple_partition VALUES (104,100)
--Verify row count on partitioned data
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id = 0
--Verify row count on partitioned index
--Row counts will not match those found in the data partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id <> 0
Mantenimiento de los índices con particiones En el pasado, el mantenimiento de índices en tablas grandes que contenían millones o incluso miles de millones de filas de datos requería mucho más del tiempo del que tenían los administradores de bases de datos. Este mantenimiento solía omitirse debido a que los datos se bloqueaban mientras el índice se volvía a generar. Con SQL Server 2005, el administrador de bases de datos puede realizar el mantenimiento del índice en línea sin bloquear la tabla subyacente durante un largo período de tiempo. No obstante, incluso este enfoque (que requiere que se realice el mantenimiento de índices mientras los usuarios consultan los datos) puede ralentizar aún más el sistema debido al uso de recursos. Un enfoque más adecuado es dividir los índices en segmentos más pequeños y, a continuación, realizar el mantenimiento de índices en esas particiones más pequeñas. Por ejemplo, para realizar el mantenimiento de índices en una partición de índice, podría sencillamente anexar el fragmento de código siguiente al final del código mostrado en la figura 8.
ALTER INDEX cl_multiple_partition
ON multiple_partition
REBUILD Partition = 2
Tenga en cuenta que este mantenimiento de índices en particiones de índice individuales debe realizarse sin conexión y puede provocar el bloqueo de la tabla durante el proceso. Para que esto no ocurra, puede mover la partición única a una partición independiente, realizar el mantenimiento de índices y, a continuación, devolver la partición a la tabla principal. Este proceso causará algunos problemas de rendimiento cuando la partición se devuelva a la tabla y se actualice el índice agrupado, pero es menos problemático que bloquear toda la tabla y consume menos recursos del sistema.
Resumen
Como puede ver, la creación de particiones en tablas de SQL Server 2005 proporciona una flexibilidad mucho más mejorada para el almacenamiento y el mantenimiento de los datos en tablas de gran tamaño, sin tener que rehacer código de aplicación o procesos de SQL Server. Con estas capacidades, SQL Server ha demostrado ser una plataforma competente para las bases de datos críticas del ámbito empresarial.
Noah Gomez es administrador de bases de datos sénior del equipo de desarrollo de SQL Server para Verizon y está especializado en VLDB y en aplicaciones a gran escala. Es miembro de la Professional Association for SQL Server (PASS) y formó parte del equipo de administradores de bases de datos de Verizon que trabajó en las bases de datos de varios terabytes galardonadas con varios premios del Winter Corp. Top Ten Grand Prize en 2003.
© 2008 Microsoft Corporation and CMP Media, LLC. Reservados todos los derechos; queda prohibida la reproducción parcial o total sin previa autorización.