Autor: Allan Mori
En mi primer post, voy hablar sobre el particionamiento de tablas e índices en SQL Server. Una característica que siempre me ha traído buenos resultados.
El particionamiento de tablas apareció en SQL Server 2005, habilitado solamente en la edición Entreprise; sin embargo, a partir de SQL Server 2016 SP1, esta característica esta disponible en todas las ediciones de SQL Server, con lo cual, ya no hay excusas para no aplicar esta funcionalidad.
En la mayoría de los casos, las consultas realizadas a tablas con grandes volúmenes de información, nos pueden ocasionar altos consumos de CPU, I/O y aumentando el tiempo de respuesta , sobre todo si el objeto es una tabla “normal”, creada de manera tradicional, es decir, no particionada.
Para poder aplicar el particionamiento se necesitan elegir un campo de la tabla por el cual se desea realizar “partir” la tabla, En la mayoría de los casos, se elige un campo de tipo DATE / DATETIME para poder aplicar la división de los datos por fechas. También se necesitan objetos adicionales a la tabla, los cuales son: PARTITION FUNCTION y PARTITION SCHEMA.
-
PARTITION FUNCTION: Objeto en el cual se va a determinar los segmentos de datos por los cuales se realizará las particiones. Utiliza el campo elegido para el particionamiento.
-
PARTITION SCHEMA: Objeto en el cual se va a determinar en qué grupos de archivos se van a almacenar los segmentos de los datos definidos en PARTITION FUNCTION.
En esta demo, se crearán dos tablas que tendrán la misma data, pero estarán particionadas de distinta manera. El script tiene 7 secciones:
Sección 1.- nos permite crear la base de datos, que se utilizará para el particionamiento, en la cual se crean los grupos de archivo necesarios para acomodar las partes de la tabla:
|
1 2 3 4 5 6 7 8 9 |
--SECCIÒN 1: --CREAR LA BASE DE DATOS----- CREATE DATABASE [BD_DEMO] CONTAINMENT = NONE ON PRIMARY ( NAME = N'BD_DEMO', FILENAME = N'E:\BD_DATA\BD_DEMO.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) LOG ON ( NAME = N'BD_DEMO_log', FILENAME = N'E:\BD_LOG\BD_DEMO_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB ); GO |
Sección 2.- Se crean los objetos PARTITION FUNCTION, PARTITION SCHEME Y la primera TABLA PARTICIONADA con rangos a la derecha.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
--SECCIÒN 2:--CREAR LA TABLA PARTICIONADA CON RANGOS A LA DERECHA----- --CREAR PARTITION FUNCTION PARA LA TABLA TABLA_PARTICIONADA_RIGHT CREATE PARTITION FUNCTION PF_RANGOS_RIGHT (DATE) AS RANGE RIGHT FOR VALUES ('20100101','20120101', '20140101','20160101'); GO --CREAR EL OBJETO PARTITION SCHEME PARA LA TABLA TABLA_PARTICIONADA_RIGHT CREATE PARTITION SCHEME PS_SEGMENTOS_RIGHT AS PARTITION PF_RANGOS_RIGHT ALL TO ([PRIMARY]); GO --CREAR TABLA TABLA_PARTICIONADA_RIGHT CREATE TABLE [dbo].TABLA_PARTICIONADA_RIGHT ([DATAPK] [int] NOT NULL, [DATA1] [int] NULL, [DATA2] [int] NULL, [DATA3] [varchar](50) NULL, [FECHA] DATE) ON PS_SEGMENTOS_RIGHT(FECHA); GO |
-
El objeto PARTITION FUNCTION PF_RANGOS_RIGHT indica que habrá 5 divisiones por un campo de tipo DATE. las divisiones a la DERECHA se aplicarán por los valores ‘20100101’,’20120101′, ‘20140101’,’20160101′
-
El objeto PS_SEGMENTOS_RIGHT indica que las 5 divisiones del PARTITION FUNCTION PF_RANGOS_RIGHT se almacenarán en el grupo de archivos PRIMARY.
-
La tabla TABLA_PARTICIONADA_ RIGHT tendrá su almacenamiento según el objeto PARTITION SCHEME PS_SEGMENTOS_RIGHT que utilizará el campo FECHA para realizar las particiones
Sección 3.- nos permite ingresar data a la tabla creada en la Sección 2.
|
1 2 3 4 5 6 7 8 9 10 |
--SECCIÒN 3:--POBLAR TABLA TABLA_PARTICIONADA_RIGHT DECLARE @columnas INT SELECT @columnas=1 WHILE @columnas < 3650 BEGIN INSERT INTO dbo.TABLA_PARTICIONADA_RIGHT(DATAPK, DATA1, DATA2, DATA3, FECHA) VALUES (@columnas,@columnas,@columnas,'DATA NUEVA',getdate()-@columnas) SELECT @columnas=@columnas+1 END; GO |
Sección 4.- nos permite visualizar de cada una de las particiones.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
--SECCIÒN 4--VER DETALLE DE LAS PARTICIONES DE LA TABLA PARTICIONADA SELECT i.name AS IndexName, ps.name AS PartitionScheme, pf.name AS PartitionFunction, p.partition_number AS PartitionNumber, fg.name AS Filegroup, prv_left.value AS StartKey, prv_right.value AS EndKey, p.row_count Rows FROM sys.dm_db_partition_stats p INNER JOIN sys.indexes i ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id LEFT OUTER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id LEFT OUTER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id LEFT OUTER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number LEFT OUTER JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id LEFT OUTER JOIN sys.partition_range_values prv_right ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number LEFT OUTER JOIN sys.partition_range_values prv_left ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1 WHERE OBJECT_NAME(p.object_id) = 'TABLA_PARTICIONADA_RIGHT' AND i.index_id = 0; GO |
El resultado del script nos permite visualizar cada partición de la tabla con sus respectivas almacenamientos, rangos y cantidad de registros por partición
Sección 5.- nos permite crear la PARTITION FUNCTION, PARTITION SCHEMA Y la segunda TABLA PARTICIONADA con rangos a la izquierda.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
--SECCIÒN 5: CREAR LA TABLA PARTICIONADA CON RANGOS A LA IZQUIERDA----- --CREAR EL OBJETO PARTITION FUNCTION PARA LA TABLA TABLA_PARTICIONADA_LEFT CREATE PARTITION FUNCTION PF_RANGOS_LEFT (DATE) AS RANGE LEFT FOR VALUES ('20100101','20120101', '20140101','20160101'); --CREAR EL OBJETO PARTITION SCHEME PARA LA TABLA TABLA_PARTICIONADA_LEFT CREATE PARTITION SCHEME PS_SEGMENTOS_LEFT AS PARTITION PF_RANGOS_LEFT ALL TO ([PRIMARY]); --CREAR TABLA TABLA_PARTICIONADA_LEFT CREATE TABLE [dbo].TABLA_PARTICIONADA_LEFT ([DATAPK] [int] NOT NULL, [DATA1] [int] NULL, [DATA2] [int] NULL, [DATA3] [varchar](50) NULL, [FECHA] DATE) ON PS_SEGMENTOS_LEFT(FECHA); GO |
-
El objeto PARTITION FUNCTION PF_RANGOS_LEFT indica que habrá 5 divisiones por un campo de tipo DATE: las divisiones a la IZQUIERDA se aplicarán por los valores ‘20100101’,’20120101′, ‘20140101’,’20160101′
-
El objeto PARTITION SCHEME PS_SEGMENTOS_ LEFT indica que las 5 divisiones del PARTITION FUNCTION PF_RANGOS_ LEFT se almacenarán en el Filegroup PRIMARY.
-
LA TABLA TABLA_PARTICIONADA_LEFT tendrá su almacenamiento según el objeto PARTITION SCHEME PS_SEGMENTOS_ LEFT que utilizará el campo FECHA para realizar las particiones
Sección 6.- nos permite ingresar data a la tabla creada en la Sección 5.
|
1 2 3 4 5 6 7 8 9 10 |
--SECCIÒN 6: POBLAR TABLA TABLA_PARTICIONADA_LEFT DECLARE @columnas INT SELECT @columnas=1 WHILE @columnas < 3650 BEGIN INSERT INTO dbo.TABLA_PARTICIONADA_LEFT(DATAPK, DATA1, DATA2, DATA3, FECHA) VALUES (@columnas,@columnas,@columnas,'DATA NUEVA',getdate()-@columnas) SELECT @columnas=@columnas+1 END; GO |
Sección 7.- nos permite visualizar de detalle de los tipos de particiones implementadas en cada tabla.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
--SECCIÒN 7: VER DETALLE DE LAS PARTICIONES SELECT OBJECT_SCHEMA_NAME(PSTATS.OBJECT_ID) AS SCHEMANAME ,OBJECT_NAME(PSTATS.OBJECT_ID) AS TABLENAME ,PS.NAME AS PARTITIONSCHEMENAME ,DS.NAME AS PARTITIONFILEGROUPNAME ,PF.NAME AS PARTITIONFUNCTIONNAME ,PRV.VALUE AS PARTITIONBOUNDARYVALUE ,C.NAME AS PARTITIONKEY ,CASE WHEN PF.BOUNDARY_VALUE_ON_RIGHT = 0 THEN C.NAME + ' > ' + CAST(ISNULL(LAG(PRV.VALUE) OVER(PARTITION BY PSTATS.OBJECT_ID ORDER BY PSTATS.OBJECT_ID, PSTATS.PARTITION_NUMBER), 'INFINITO') AS VARCHAR(100)) + ' AND ' + C.NAME + ' <= ' + CAST(ISNULL(PRV.VALUE, 'INFINITO') AS VARCHAR(100)) ELSE C.NAME + ' >= ' + CAST(ISNULL(PRV.VALUE, 'INFINITO') AS VARCHAR(100)) + ' AND ' + C.NAME + ' < ' + CAST(ISNULL(LEAD(PRV.VALUE) OVER(PARTITION BY PSTATS.OBJECT_ID ORDER BY PSTATS.OBJECT_ID, PSTATS.PARTITION_NUMBER), 'INFINITO') AS VARCHAR(100)) END AS PARTITIONRANGE ,PSTATS.PARTITION_NUMBER AS PARTITIONNUMBER ,PSTATS.ROW_COUNT AS PARTITIONROWCOUNT FROM SYS.DM_DB_PARTITION_STATS AS PSTATS INNER JOIN SYS.PARTITIONS AS P ON PSTATS.PARTITION_ID = P.PARTITION_ID INNER JOIN SYS.DESTINATION_DATA_SPACES AS DDS ON PSTATS.PARTITION_NUMBER = DDS.DESTINATION_ID INNER JOIN SYS.DATA_SPACES AS DS ON DDS.DATA_SPACE_ID = DS.DATA_SPACE_ID INNER JOIN SYS.PARTITION_SCHEMES AS PS ON DDS.PARTITION_SCHEME_ID = PS.DATA_SPACE_ID INNER JOIN SYS.PARTITION_FUNCTIONS AS PF ON PS.FUNCTION_ID = PF.FUNCTION_ID INNER JOIN SYS.INDEXES AS I ON PSTATS.OBJECT_ID = I.OBJECT_ID AND PSTATS.INDEX_ID = I.INDEX_ID AND DDS.PARTITION_SCHEME_ID = I.DATA_SPACE_ID AND I.TYPE <= 1 INNER JOIN SYS.INDEX_COLUMNS AS IC ON I.INDEX_ID = IC.INDEX_ID AND I.OBJECT_ID = IC.OBJECT_ID AND IC.PARTITION_ORDINAL > 0 INNER JOIN SYS.COLUMNS AS C ON PSTATS.OBJECT_ID = C.OBJECT_ID AND IC.COLUMN_ID = C.COLUMN_ID LEFT JOIN SYS.PARTITION_RANGE_VALUES AS PRV ON PF.FUNCTION_ID = PRV.FUNCTION_ID AND PSTATS.PARTITION_NUMBER = (CASE PF.BOUNDARY_VALUE_ON_RIGHT WHEN 0 THEN PRV.BOUNDARY_ID ELSE (PRV.BOUNDARY_ID+1) END) ORDER BY TABLENAME, PARTITIONNUMBER; GO |
-
El resultado del script nos permite visualizar a detalle cada partición de las dos tablas con sus respectivas PARTITION SCHEME, PARTITION FUCNTION, FILEGROUPS, rangos y cantidad de registros por partición.
-
Observar la diferencia de registros que existe, esta aparece al utilizar “LEFT” o “RIGHT” al crear el objeto PARTITION FUNCTION.
Esperamos que les sea útil en la implementación de esta funcionalidad.
Nos vemos en el próximo post
Buena voz si sabia que solo en versión Enterprise se puede particionar no tendras una propuesta de migración o requerimientos para pedir un servidor de datawarehouse en mi trabajo, porque el servidor actual colapsa en algunas franjas horarias por muchos aplicativos
Luis, escríbeme a alberto.derossi@dblearner.com para poder contestarte con algo de lo que necesitas. Hay unas plantillas para determinar el tamaño de un servidor de DW
Saludos
Alberto