Partición de Tablas en SQL SERVER

Buenas noches queridos lectores, el día de hoy hablaremos sobre el particionado de tablas en SQL SERVER. Antes que nada, debo decirles que el particionamiento esta disponible a partir de SQL SERVER 2005, aunque su funcionalidad mejoró mucho en 2008.

A muchos de nosotros nos ha tocado, como DBA, o como encargado de sistemas que nos dicen cosas como "Esta muy lento el sistema", en realidad lo que esta lento es la Base de Datos, ya sea porque tienen consultas mal hechas o por la falta de índices, cuando ya optimizamos la consulta y también creamos índices, pero aún sigue estando lento, puede ser debido a la cantidad de información que existe y para eso sirve el particionado. Imaginemos, que en nuestro lugar de trabajo, hay una tabla llamada Facturas, y ahí hay millones de registros, cuando lanzamos una consulta a esa tabla, por más índices que ésta tenga, la cantidad de información hará que la consulta siga lenta, así que tendremos que buscar los criterios para particionar, podremos particionar por cliente, por ejemplo, así las facturas de cada clientes estarán en una partición y solamente el sistema de BD buscará en la partición correspondiente para cada cliente, también podemos particionar por rangos de fechas, o incluso por rangos de productos, todo esto dependiendo de que es lo que queremos hacer.

¿Qué es particionamiento de tablas en BD?

Particionar, es el proceso donde tablas muy grandes son divididas en múltiples partes más pequeñas. Al separar una tabla grande en tablas individuales más pequeñas, las consultas que acceden sólo a una fracción de los datos, pueden correr más rápido porque hay menos datos que escanear. El objetivo principal de particionar es ayudar en el mantenimiento de tablas grandes y reducir el tiempo de respuesta general para leer y cargar datos para operaciones SQL particulares.

Particionamiento vertical

El particionamiento vertical de tablas es principalmente usado para incrementar el desempeño de SQL Server especialmente en casos cuando una consulta retorna todas las columnas de una tabla que contiene un número de columnas de texto muy amplio o BLOB. En este caso, para reducir los tiempos de acceso, las columnas BLOB pueden ser divididas a su propia tabla. Otro ejemplo es restringir el acceso a datos sensibles, por ejemplo, contraseñas, información salarial, etc. La partición vertical divide una tabla en dos o más tablas que contienen diferentes columnas:

Particionamiento vertical

Particionamiento Horizontal

El particionamiento horizontal divide una tabla en múltiples tablas que contienen el mismo número de columnas, pero menos filas. Por ejemplo, si una tabla contiene un gran número de filas que representan reportes mensuales podría ser particionada horizontalmente en tablas por años, con cada tabla representando todos los reportes para un año específico. De esta manera las consultas que requieren datos para un año específico sólo referenciarán la tabla apropiada. Las tablas deberían ser particionadas en una manera que las consultas referencian tan pocas tablas como sea posible.

Particionamiento Horizontal

Las tablas son particionadas horizontalmente basadas en una columna que será usada para particionar y los rangos asociados a cada partición. La columna de particionamiento es usualmente una columna de fecha, pero todos los tipos de datos que son válidos para usarse como columnas de índice pueden ser usados como columna de partición, excepto columnas timestamp. Los siguientes tipos de datos no pueden ser especificados: ntext, text, image, xml, varchar(max), nvarchar(max), o varbinary(max), el tipo definido por el usuario Microsoft .NET Framework common language runtime (CLR), columnas de tipo de datos de alias. Hay dos enfoques diferentes que podríamos usar para lograr la partición de la tabla. El primero es crear una nueva tabla particionada y simplemente copiar los datos desde su tabla existente en la nueva tabla y renombrarla. El segundo enfoque es particionar una tabla existente reconstruyendo o creando un índice agrupado en la tabla.

Pero ya basta de teoría pasemos al ejemplo que es lo que nos gusta y nos interesa, el método que utilizaremos para el ejemplo será la partición horizontal.

Consideremos este script para nuestro ejemplo:

use pruebas

go

--Creamos una tabla de prueba

create table MyTable1 (

id int identity,

rnd float,

fecha date)

go

--Declaramos los valores

declare @num int

declare @cont bigint

declare @f as date

declare @cont1 bigint

set @cont= 1

--Creamos un ciclo para llenar la tabla

set @f = GETDATE()

while @cont <= 1000

begin

set @cont1= 1

       while @cont1 <= 1000

             begin

                    select @num =  RAND()*100

                    insert into MyTable1 (rnd, fecha) values (@num, @f)

                    set  @cont1 = @cont1 + 1

             end

set @cont = @cont +1

print @cont

set @f = GETDATE()+@cont

end

go

Con este script ya tenemos una tabla de 1 millón de registros que contiene un campo fecha, el rango de fecha va, desdel el primer registro hasta el último, son tres años distintos del 2020 al 2023.

Ejemplo: Particionar la tabla mytable1 por fecha, dividida en tres años

Para comenzar el ejemplo, primero veamos cuanto tiempo tarda la consulta en realizar una búsqueda, para aquellos registros que tengan el año 2021

select * from MyTable1 where fecha between '2021-01-01' and '2021-12-31'

Nos encontramos con 365 mil registros y con que la consulta tardó 3 seg

Vamos a comenzar a particionar esta tabla y comprobar que el rendimiento ha mejorado

Índices

Lo primero que tenemos que hacer es borrar los índices que tuviera la tabla, en este caso es una tabla que acabamos de crear, por lo cual no borraremos nada, pero si es importante tener en cuenta que para poder crear la partición necesitamos borrar los índices de la tabla y volverlos a crear, principalmente el índice del campo por el cual queremos particionar.

FileGroups

Se crearan los grupos de archivos que contendrán las particiones

ALTER DATABASE PRUEBAS ADD FILEGROUP FG_PRUEBAS1

GO

ALTER DATABASE PRUEBAS ADD FILEGROUP FG_PRUEBAS2

GO

Posteriormente crearemos los archivos físicos que contendrán los fileGroups

ALTER DATABASE pruebas ADD FILE (NAME=[Part_2020], FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL14.SERVERSQL\MSSQL\DATA\Pruebas1.ndf',SIZE = 131072 KB, MAXSIZE = 819200 KB, FILEGROWTH = 131072 KB ) TO FILEGROUP FG_PRUEBAS1

GO                                                                                                         

ALTER DATABASE pruebas ADD FILE (NAME=[Part_2021], FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL14.SERVERSQL\MSSQL\DATA\Pruebas2.ndf',SIZE = 131072 KB, MAXSIZE = 819200 KB, FILEGROWTH = 131072 KB ) TO FILEGROUP FG_PRUEBAS2

GO

Creamos la partición, que en este caso serán 3

CREATE PARTITION FUNCTION [Particionadofecha_Particion] (date)

AS RANGE RIGHT FOR VALUES ('2020-12-31', '2021-12-31')

GO

Creamos el esquema de partición

CREATE PARTITION SCHEME [ParticionFecha_Esquema]
AS PARTITION [Particionadofecha_Particion]
TO ('FG_PRUEBAS1', 'FG_PRUEBAS2', 'PRIMARY')
GO

El primer criterio de la partición caerá dentro del grupo FG_PRUEBAS1, el segundo en FG_PRUEBAS2 y el tercero que sería como un valor default, es decir el año 2022, caeeá en el grupo PRIMARY, contenido dentro del archivo mdf principal.

Cuando hemos definido el esquema de partición, basta con crear nuevamente el índice, añadiendo el esquema de partición.

create index idx_fecha on mytable1  (fecha) on [ParticionFecha_Esquema] (fecha)

Cuando el índice ya este creado, vamos a revisar como quedaron las particiones

SELECT

t.name AS TableName,

i.name AS field,

p.partition_number,

r.value AS BoundaryValue ,

rows

From

Sys.Tables AS t

Join Sys.Indexes AS i

On t.object_id = i.object_id

Join sys.partitions AS p

On i.object_id = p.object_id And

i.index_id = p.index_id

Join sys.partition_schemes AS s

On i.data_space_id = s.data_space_id

Join sys.partition_functions AS f

ON s.function_id = f.function_id

Left Join sys.partition_range_values AS r

On f.function_id = r.function_id and

r.boundary_id = p.partition_number

Como vemos, las particiones ya estan creadas correctamente.

Espero les haya sido útil el post, compartan, comenten y cualquier duda estoy a la orden