Diseño de Particiones

A continuación, se revisan las distintas estrategias de particionamiento propuestas por SQL Server: horizontal, vertical y funcional. En este post, nos centraremos en la definición conceptual de cada estrategia para sentar las bases que nos permitan, en próximos posts, desarrollar los aspectos que hemos de tener en cuenta para decidir, de forma efectiva y eficiente, la estructura de particionamiento para distintos casos de uso.

Las estrategias que se describen a continuación, pueden ser combinadas entre sí, es decir, podemos dividir los datos en particiones (particionamiento horizontal) y, a continuación, usar particiones verticales para subdividir todavía más los datos de cada partición.

Particionamiento Horizontal

En esta estrategia, cada partición es un almacén de datos independiente, pero todas las particiones tienen el mismo esquema. Cada división se conoce como partición y contiene un subconjunto específico de los datos. Un ejemplo de este tipo de particionamiento podría ser el conjunto de pedidos de un conjunto específico de clientes.

En la siguiente ilustración, se muestra un ejemplo particionamiento en el que los datos del inventario de productos se dividen en particiones según la clave del producto. Cada partición contiene los datos de un intervalo contiguo de claves de partición (A-G y H-Z), organizadas alfabéticamente.

¿Cuándo, cómo y por qué? Gestión y buenas prácticas de particionamiento en SQL Server (I)

El factor más importante es la elección de una clave de particionamiento. Puede resultar difícil cambiar la clave después de que el sistema está en funcionamiento. La clave debe garantizar la creación de particiones uniformes para asegurar la eficiencia de la estructura creada. 

Particionamiento Vertical

En esta estrategia, cada partición contiene un subconjunto de los campos que forman la tabla. Los campos se dividen según su patrón de uso, de esta forma, los campos a los que se accede con más frecuencia pueden colocarse en una partición vertical y los campos que se utilizan de forma más ocasional, en otra.

El objetivo principal del particionamiento vertical es reducir los costes de E/S y de rendimiento asociados a la recopilación de elementos a los que se accede con más frecuencia. En la siguiente ilustración, se muestra un ejemplo de creación de particiones verticales en el que, las diferentes propiedades de un elemento, se almacenan en particiones distintas. Una partición contiene los datos a los que se accede con mayor frecuencia como el nombre, la descripción y el precio de los productos, y en otra, los datos de inventario: la cantidad en stock y la fecha del último pedido.

particionamiento vertical

Particionamiento Funcional

En esta estrategia, los datos se agregan en función del uso de cada contexto de negocio. Por ejemplo, un sistema de comercio electrónico puede almacenar los datos de facturas en una partición y los del inventario de productos en otra.

Cuando sea posible identificar un contexto enlazado para cada área de negocio independiente de una aplicación, la creación de particiones funcionales, es una forma de mejorar tanto el rendimiento del acceso a los datos como el aislamiento. Otro uso común de la creación de particiones funcionales, es separar los datos de lectura y escritura de los datos de solo lectura. En la siguiente ilustración, se muestra una visión general de la creación de particiones donde se separan los datos de inventario de los datos del cliente.

¿Quieres modernizar tu infraestructura para facilitar la explotación de tus datos?

Mejora la eficiencia de tu plataforma, con seguridad y cumpliendo las normativas vigentes. Nuestros expertos cuentan con gran experiencia en el desarrollo, optimización de procesos y migraciones de SQL Server.

Quiero modernizar mi plataforma ahora

Pasamos a la acción: Creación de una Tabla Particionada

A continuación, se describen los pasos para la creación de una tabla con particionamiento horizontal. Previa a la creación de dicha tabla se describen dos conceptos: Función de particionamiento y esquema de partición.

Función de Particionamiento

La función de particionamiento es el objeto que define cómo se asignan las filas de una tabla o índice a un conjunto de particiones.

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ boundary_value [ ,…n ] ] ) [ ; ]

Range Left

range left particionamiento

En el caso del ejemplo que se muestra en la ilustración anterior, la función de partición pfnTens, con la opción RANGE LEFT, posibilitará 4 particiones de tipo entero con los siguientes intervalos:

  • Partición 1: [-∞, 10]
  • Partición 2: [11, 30]
  • Partición 3: [31, 50]
  • Partición 4: [51, +∞]

Range Right

range right particionamiento

En el caso de ejemplo que se muestra en la ilustración anterior, la función de partición pfnTens, con la opción RANGE RIGHT, posibilitará 4 particiones de tipo entero con los siguientes intervalos:

  • Partición 1: [-∞, 9]
  • Partición 2: [10, 29]
  • Partición 3: [30, 49]
  • Partición 4: [50, +∞]

Esquema de Partición

El esquema de partición es el objeto de base de datos que asigna particiones de una función de partición a grupos de archivos. Dicha asignación puede hacerse a varios o al mismo al mismo grupo de archivos.

En el ejemplo que se muestra a continuación, se crea un esquema de particionamiento haciendo uso de la función ‘pfnTens’ del apartado anterior, y cada partición se alojará en grupos de archivos distintos (test1fg, test2fg, test3fg y test4fg).

— Creamos Grupos de Archivos para ubicar particiones
USE [master]
GO
ALTER DATABASE [TSQLV4] ADD FILEGROUP [test1fg]
ALTER DATABASE [TSQLV4] ADD FILEGROUP [test2fg]
ALTER DATABASE [TSQLV4] ADD FILEGROUP [test3fg]
ALTER DATABASE [TSQLV4] ADD FILEGROUP [test4fg]
GO
 
— Añadimos archivos de datos a cada grupo de archivos
USE [master]
GO
ALTER DATABASE [TSQLV4] ADD FILE ( NAME = N‘test1fgFile’, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL14.STANDALONE\MSSQL\DATA\test1fgFile.ndf’ , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [test1fg]
ALTER DATABASE [TSQLV4] ADD FILE ( NAME = N‘test2fgFile’, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL14.STANDALONE\MSSQL\DATA\test2fgFile.ndf’ , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [test2fg]
ALTER DATABASE [TSQLV4] ADD FILE ( NAME = N‘test3fgFile’, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL14.STANDALONE\MSSQL\DATA\test3fgFile.ndf’ , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [test3fg]
ALTER DATABASE [TSQLV4] ADD FILE ( NAME = N‘test4fgFile’, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL14.STANDALONE\MSSQL\DATA\test4fgFile.ndf’ , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [test4fg]
GO
— Creamos esquema de partición
CREATE PARTITION SCHEME myRangePS2
AS PARTITION pfnTens TO (test1fg, test2fg, test3fg, test4fg)

Creación de Tabla Particionada

Una vez definidos el particionamiento y el esquema de partición, se crea la tabla ‘Empleados’ con los objetos de particionamiento generados en los apartados anteriores.

CREATE TABLE Empleados (EmpId int, EmpName varchar(50)) on myRangePS2(EmpID);
 

En base a la estructura de particionamiento propuesta, la tabla ‘Empleados’ queda organizada de la siguiente forma:

  • Partición 1. Empleados con ID menor o igual a 9 en el grupo de archivos test1fg
  • Partición 2. Empleados con ID en el intervalo [10, 29] en el grupo de archivos test2fg
  • Partición 3. Empleados con ID en el intervalo [30, 49] en el grupo de archivos test3fg
  • Partición 4. Empleados con ID mayor o igual a 50 en el grupo de archivos test4fg

Nota: Tomamos como función de particionamiento pfnTens con RANGE RIGHT

CREATE PARTITION FUNCTION pfnTens (int) as RANGE RIGHT FOR VALUES (10, 30, 50)
 

Consulta de Datos Particionados

Cargamos la tabla ‘Empleados’ con 100 registros que se distribuirán en función del valor del campo ‘EmpId’, en la partición correspondiente.

— Cargamos la tabla ‘Empleados’ con 100 registros de ejemplo
DECLARE @contador int
 
Set @contador = 1
 
WHILE @contador <= 100
BEGIN
INSERT [dbo].[Empleados] ([EmpId], [EmpName]) VALUES (@contador, ‘Empleado_’+convert(varchar(5),@contador))
 
SET @contador = @contador+1;
END
GO
 

Consultamos distribución de datos en las distintas particiones.

select distinct OBJECT_NAME(p.OBJECT_ID) as TableName, ps.Name AS PartitionScheme, pf.name AS PartitionFunction,p.partition_number,fg.name AS FileGroupName, rv.value AS PartitionFunctionValue, p.rows AS NumberOfRows
from sys.indexes i
join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
join sys.partition_functions pf on pf.function_id = ps.function_id
left join sys.partition_range_values rv on rv.function_id = pf.function_id AND rv.boundary_id = p.partition_number
join sys.allocation_units au ON au.container_id = p.hobt_id
join sys.filegroups fg ON fg.data_space_id = au.data_space_id
WHERE OBJECT_NAME(p.object_id) = ’empleados’
 
partition scheme empleados

Consultamos datos por partición:

select *
from [TSQLV4].[dbo].[Empleados]
where $partition.[pfnTens]([EmpId]) = 1
 
select *
from [TSQLV4].[dbo].[Empleados]
where $partition.[pfnTens]([EmpId]) = 2
 
select *
from [TSQLV4].[dbo].[Empleados]
where $partition.[pfnTens]([EmpId]) = 3
 
select *
from [TSQLV4].[dbo].[Empleados]
where $partition.[pfnTens]([EmpId]) = 4
 
datos en particion

Operaciones para la Gestión de Particiones (Merge, Split y Switch)

A continuación se analizan las principales operaciones sobre particiones en SQL Server: MERGE, SPLIT y SWITCH.

Merge

La operación MERGE permite combinar dos particiones contiguas en una única partición. Dicha operación consiste en eliminar uno de los valores límite.

Como ejemplo, combinaremos las particiones P2 y P3 de la estructura creada en apartados anteriores.

merge operaciones particiones

A continuación, se muestra cómo quedaría la distribución de datos del particionamiento del ejemplo, tras aplicar la operación de MERGE.

select distinct OBJECT_NAME(p.OBJECT_ID) as TableName, ps.Name AS PartitionScheme, pf.name AS PartitionFunction,p.partition_number,fg.name AS FileGroupName, rv.value AS PartitionFunctionValue, p.rows AS NumberOfRows
from sys.indexes i
join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
join sys.partition_functions pf on pf.function_id = ps.function_id
left join sys.partition_range_values rv on rv.function_id = pf.function_id AND rv.boundary_id = p.partition_number
join sys.allocation_units au ON au.container_id = p.hobt_id
join sys.filegroups fg ON fg.data_space_id = au.data_space_id
WHERE OBJECT_NAME(p.object_id) = ’empleados’
¿Cuándo, cómo y por qué? Gestión y buenas prácticas de particionamiento en SQL Server (I)

Al aplicar merge, se mantendrían 3 particiones en la que, el contenido de la partición 2, es el resultado de combinar los registros de las “antiguas” partición 2 y 3 en una sola partición.

select *
from [TSQLV4].[dbo].[Empleados]
where $partition.[pfnTens]([EmpId]) = 2
empleadoid

Split

Con la operación SPLIT se separa una partición en dos. Para ello, se ha de indicar qué grupo de archivos será el que se utilizará para los valores contenidos en la nueva partición. La aplicación de la operación SPLIT se ha de tener en cuenta los siguientes aspectos:

  • El nuevo valor límite debe pertenecer al rango de valores de la función de particionado
  • El nuevo valor límite no puede ser un valor límite asignado actualmente en la función de particionado
  • Si al dividir una partición con SPLIT, se producen dos particiones con datos, SQL Server distribuirá respectivamente los datos a sus particiones que lleva asociado crecimiento en el log de transacciones para albergar las operaciones INSERT y DELETE que internamente se producen.

A continuación se muestra como ejemplo el SPLIT de la actual partición 2, tal y como se muestra en la siguiente ilustración:

¿Cuándo, cómo y por qué? Gestión y buenas prácticas de particionamiento en SQL Server (I)
ALTER PARTITION SCHEME [myRangePS2] NEXT USED [test3fg];
ALTER PARTITION FUNCTION pfnTens() SPLIT RANGE(30);
select distinct OBJECT_NAME(p.OBJECT_ID) as TableName, ps.Name AS PartitionScheme, pf.name AS PartitionFunction,p.partition_number,fg.name AS FileGroupName, rv.value AS PartitionFunctionValue, p.rows AS NumberOfRows from sys.indexes i join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id join sys.partition_schemes ps on ps.data_space_id = i.data_space_id join sys.partition_functions pf on pf.function_id = ps.function_id left join sys.partition_range_values rv on rv.function_id = pf.function_id AND rv.boundary_id = p.partition_number join sys.allocation_units au ON au.container_id = p.hobt_id join sys.filegroups fg ON fg.data_space_id = au.data_space_id WHERE OBJECT_NAME(p.object_id) = ’empleados’
split partition scheme function

Tras la aplicación del SPLIT, volveríamos a tener las particiones 2 y 3, tal y como se muestran a continuación.

select * from [TSQLV4].[dbo].[Empleados] where $partition.[pfnTens]([EmpId]) = 2
select * from [TSQLV4].[dbo].[Empleados] where $partition.[pfnTens]([EmpId]) = 3
¿Cuándo, cómo y por qué? Gestión y buenas prácticas de particionamiento en SQL Server (I)

Switch

La operación SWITCH es una operación interesante para mover datos de forma rápida entre objetos. Es una operación que solo incurre en modificación de metadatos y que, por tanto, se realiza de forma instantánea (independientemente de cuantos datos existan en la partición).

En siguientes posts veremos la utilidad de la operación SWITCH para el traslado de datos a tablas de históricos. No obstante, a continuación se muestra un ejemplo con la operación SWITCH para ilustrar el funcionamiento de dicha operación. En dicho ejemplo, se traslada la partición 2 de ’empleados’ a la tabla, de nueva creación, ‘EmpleadosStaging’.

empleados staging

Para tener una visión más clara de la operación switch, creamos una tabla con la misma estructura de particionmiento de la tabla ‘Empleados’. En el siguiente ejemplo se analiza el resultado de aplicar la operación switch de una de las particiones de la tabla ‘Empleados’ sobre la nueva tabla creada ‘EmpleadosStaging’.

CREATE TABLE EmpleadosStaging (EmpId int, EmpName varchar(50))
on myRangePS2(EmpID);

Haciendo uso de la operación switch, pasamos la partición 2 de la tabla ‘Empleados’ a ‘EmpleadosStaging’.

ALTER TABLE [dbo].[Empleados] SWITCH PARTITION 2 TO [dbo].[EmpleadosStaging] PARTITION 2;

Tras la ejecutar la operación SWITCH mostrada anteriormente, se obtienen los siguientes resultados de distribución de datos en ambas tablas.

select distinct OBJECT_NAME(p.OBJECT_ID) as TableName
, ps.Name AS PartitionScheme
, pf.name AS PartitionFunction
, p.partition_number
, fg.name AS FileGroupName
, rv.value AS PartitionFunctionValue
, p.rows AS NumberOfRows
from sys.indexes i join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
join sys.partition_functions pf on pf.function_id = ps.function_id
left join sys.partition_range_values rv on rv.function_id = pf.function_id AND rv.boundary_id = p.partition_number
join sys.allocation_units au ON au.container_id = p.hobt_id
join sys.filegroups fg ON fg.data_space_id = au.data_space_id
WHERE OBJECT_NAME(p.object_id) in (’empleados’, ‘EmpleadosStaging’)
partition scheme function number

Resumen y Conclusiones

En este post se sientan las bases de conocimiento necesarias para acometer el particionamiento de tablas en SQL Server:

  • Estrategias de particionamiento: horizontal, vertical y funcional.
  • Objetos relacionados: funciones particionamiento y esquemas de partición.
  • Operaciones básicas de particionamiento: SPLIT, MERGE y SWITCH.

En próximos artículos, se hará uso de estos conceptos para el desarrollo de escenarios reales en los que se analizarán las mejores prácticas para cada caso:

  • Elección de claves de particionamiento.
  • Indexación y alineación con el esquema de particionamiento.
  • Como, en algunas ocasiones, hemos de modificar la codificación de queries para un uso eficiente del esquema de particionamiento creado.
  •  Aplicación y casos de uso de operaciones sobre particionamiento

¡Has llegado al final! Parece que te ha gustado nuestro post sobre SQL Server

Mejora la eficiencia de tu plataforma, con seguridad y cumpliendo las normativas vigentes. Nuestros expertos cuentan con gran experiencia en el desarrollo, optimización de procesos en SQL Server, así como en migraciones a Azure o Amazon Web Services. Moderniza tu plataforma de datos ahora y mantente competitivo.

Voy a Echar un vistazo
0 Shares:
Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

You May Also Like

Depurar expresiones DAX con DAX Studio

Como en todos los procesos de desarrollo, la depuración de código puede ser necesaria cuando no se consigue un resultado esperado y se desconoce el motivo. Lo mismo ocurre con las expresiones DAX y por ello, una forma fácil de depurar código en este lenguaje, es mediante la herramienta DAX Studio.
Leer más

SQL Server 2017 en Linux

Vale, SQL Server 2017 corre en Linux, ¿me interesa? Sí, ¿por qué? Porque no hablamos simplemente de que corra un nuevo sistema operativo...sino que se pueden utilizar para despliegues rápidos en entornos escalables basados en docker, kubernetes, etc. Daremos un repaso a cómo aprovecharnos de los nuevos escenarios de despliegue en nuestras empresas, aunque sean tradicionalmente entornos Microsoft.