PARTICIONAR TABLAS EN 5 PASOS

Amigos,

Hoy quiero hablarles de un tema vigente desde el SQL Server 2005, especialmente útil cuando necesitamos mejorar los tiempos de respuesta en tablas de varios millones de registros en ambientes de reporting productivos.

Hasta el SQL Server 2000 no existía la posibilidad de particionar tablas. Toda la info de una tabla debía recaer en un mismo datafile provocando esto enormes problemas a la hora de consultar o insertar información en la tabla cuya manipulación, (por su tamaño), se hacía casi imposible.

A partir del SQL Server 2005 Microsoft introdujo el concepto de "Partición de Tablas", lo cual nos permite "dividir" nuestras tablas en porciones, las cuales serán ubicadas en varios archivos (tantos como determinemos) y alocados en más de una unidad de disco.

De este modo, podremos sacarle el máximo provecho a una de las grandes ventajas que tiene el motor de base de datos de Microsoft, la posibilidad de realizar lecto escrituras paralelas (con toda la ganancia de tiempo y performance que ello signifca).

El particionar una tabla no es difícil y es especialmente útil cuando ud dispone de una tabla de > de 15 millones de registros y al menos más de una unidad de disco disponible para alocar las futuras particiones.

Para ello debemos seguir los siguientes 5  pasos (vamos a la práctica).

Supongamos que tenemos una tabla llamada ventas la cual tiene 20 millones de registros correspondientes a los años 2011 y 2012, con la siguiente estructura:


CREATE TABLE [dbo].[ventas](
[id] [int] NULL,
[fecha] [datetime] NULL, ---> idx_fecha (clustered)
[idproducto] [int] NULL,
[cantidad] [int] NULL)


Pues bien, llegó el momento de determinar un criterio de partición. Yo creo que en este caso, por la característica de la data y la cantidad de registros, sería conveniente generar 4 datafiles de 5 millones de registros aprox. cada uno. Es decir,  generar cuatro archivos con un semestre cada uno (de los dos años que hoy contiene la tabla)

Primer Paso : Agregar Filegroups Para Contener los Files Semestrales


Alter Database Prueba
Add FileGroup [Fg_Ventas_20011A]
Alter Database Prueba
Add FileGroup [Fg_Ventas_20011B]
Alter Database Prueba
Add FileGroup [Fg_Ventas_20012A]
Alter Database Prueba
Add FileGroup [Fg_Ventas_20012B]

Segundo Paso : Agregar Files Para  los Filegroups Creados


Alter Database Prueba
Add File
(Name = 'ventas_2011A',
 Filename = 'D:\Data\ventas2011A.ndf',
 Size = 25000MB,
 Maxsize = 100000MB)
 To Filegroup Fg_Ventas_20011A]


Alter Database Prueba
Add File
(Name = 'ventas_2011B,
 Filename = 'E:\Data\ventas2011B.ndf',
 Size = 25000MB,
 Maxsize = 100000MB)
 To Filegroup Fg_Ventas_20011B]


Alter Database Prueba
Add File
(Name = 'ventas_2012A',
 Filename = 'F:\Data\ventas2012A.ndf',
 Size = 25000MB,
 Maxsize = 100000MB)
 To Filegroup Fg_Ventas_20012A]




Alter Database Prueba
Add File
(Name = 'ventas_2012B',
 Filename = 'G:\Data\ventas2012B.ndf',
 Size = 25000MB,
 Maxsize = 100000MB)
 To Filegroup Fg_Ventas_20012B]


Tercer Paso :  Generar una Partition Function (la cual determinará los rangos a partir de los cuales particionaremos la tabla, - en nuestro caso por fecha -)


Create Partition Function [PF_Ventas](datetime)
As Range Right For Values
 ('2011-06-30 23:59:59', '2011-12-31 23:59:59','2012-06-30 23:59:59' )


Cuarto Paso :  Generar una Partition Scheme (el cual mapeará las particiones a los filegroups creados en el primer paso)


Create Partition Scheme [PS_Ventas] As Partition [PF_Ventas]
To ([Fg_Ventas_20011A], [Fg_Ventas_20011B] , [Fg_Ventas_20012A], [Fg_Ventas_20012B] )


Quinto Paso:  Distribuir Físicamente los Registros de la Tabla Ventas en los Files Creados Mediante el Dropeado y la Creación de Indice Cluster Sobre el Partiton Scheme

5.1    Drop Index [ix_fecha] On [dbo].[ventas]
         With( Online = Off )


5.2    Create Clustered Index [ix_fecha] On [dbo].[Ventas] (fecha)
          on [PS_Ventas] (fecha)

Listo! Ya tenemos nuestra tabla particionada y con una mejora en la performance que los invito a probarla

(*) IMPORTANTE: Si nuestra tabla tenía índices nonclustered adicionales, los mismos deben ser recreados sobre la partición, tal cual lo hecho en el paso 5, para lograr un óptima performance.

Les dejo por último un script hecho en base a metadata que nos provee el SQL Server que tiene como objetivo el verificar que la particiones creadas hayan sido efectivamente bien "pobladas" de datos, acorde a los trabajos realizados.

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 Joing sys.partition_range_values AS r 

On  f.function_id = r.function_id and 

      r.boundary_id = p.partition_number

Where

t.name = 'Ventas' and

i.type <= 1

Order By p.partition_number;

(**) En un próximo post abordaré la manutención de las particiones, algo que no es problemático, pero que si requiere de un trabajo que debe ser tenido en cuenta a la hora de determinar si vale la pena o no particionar una tabla (costo/beneficio). 

Eso es todo amigos, como siempre estoy para ayudarles. No dejen de escribirme que siempre estoy dispuesto a darles una mano.

GUSTAVO HERRERA para "SQL SERVER PARA TODOS"

Otro Post recomendado por el Autor

Mantenimiento de Estadísticas Para una Performance Optima de Nuestra BD:

https://www.blogger.com/blogger.g?blogID=4841087034568585749#editor/target=post;postID=5281491474180357583;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=0;src=postname