Particionamiento de tablas

Particionamiento de tablas de bases de datos en SQL Server

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.

Veamos el ejemplo de la tabla EmpleadosR :

---Primeramente creamos la base de datos BDParticion

CREATE DATABASE [BD_FP]

CONTAINMENT = NONE

ON PRIMARY

( NAME = N'BD_FP', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\BD_FP.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

LOG ON

( NAME = N'BD_FP_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\BD_FP_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

--Creamos la Tabla EmpleadosR en la base de datos BD_FP

use BD_FP

cREATE TABLE EmpleadosR

(

ReportID int IDENTITY (1,1) NOT NULL,

ReportName varchar (100),

ReportNumber varchar (20),

ReportDescription varchar (max)

CONSTRAINT EReport_PK PRIMARY KEY CLUSTERED (ReportID)

)

--Insertamos informacion en la tabla EmpleadosR

DECLARE @i int

SET @i = 1

BEGIN TRAN

WHILE @i<100000

BEGIN

INSERT INTO EmpleadosR

(

ReportName,

ReportNumber,

ReportDescription

)

VALUES

(

'ReportName',

CONVERT (varchar (20), @i),

REPLICATE ('Report', 1000)

)

SET @i=@i+1

END

COMMIT TRAN

--Si corremos una consulta SQL para recuperar los datos de ReportID, ReportName, ReportNumber de la tabla EmployeeReports

--l resultado establece que la cuenta del escaneo es 5

-- representa un número de veces que la tabla ha sido accedida durante la consulta,

-- que teníamos 113288 lecturas lógicas que representan el número total de accesos de páginas para procesar la consulta:

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT er.ReportID, er.ReportName, er.ReportNumber

FROM dbo.EmpleadosR er

WHERE er.ReportNumber LIKE '%33%'

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

A continuación crearemos la tabla ReportsDesc y moveremos la columna ReportDescription y la tablaReportsData y moveremos todos los datos de la tabla EmployeeReports excepto la columnaReportDescription.

CREATE TABLE ReportesD

( ReportID int FOREIGN KEY REFERENCES EmpleadosR (ReportID),

ReportDescription varchar(max)

CONSTRAINT PK_ReportDesc PRIMARY KEY CLUSTERED (ReportID)

)

CREATE TABLE ReportesData

(

ReportID int NOT NULL,

ReportName varchar (100),

ReportNumber varchar (20),

CONSTRAINT DReport_PK PRIMARY KEY CLUSTERED (ReportID)

)

INSERT INTO dbo.ReportesData

(

ReportID,

ReportName,

ReportNumber

)

SELECT er.ReportID,

er.ReportName,

er.ReportNumber

FROM dbo.EmpleadosR er

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT er.ReportID, er.ReportName, er.ReportNumber

FROM ReportesData er

WHERE er.ReportNumber LIKE '%33%'

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

La partición vertical en tablas SQL Server puede no ser el método correcto en cada caso. Sin embargo, si usted tiene, por ejemplo, una tabla con muchos datos que no se accede igualitariamente, tablas con datos a los que desea restringir el acceso, o escaneos que retornan muchos datos, la partición vertical puede ayudar.

Particionamiento Horizontal en tablas SQL Server

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.

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.

Ejemplo de particionamiento horizontal con la creación de una nueva tabla particionada

SQL Server 2005 introdujo una característica incorporada de particionamiento para particionar horizontalmente una tabla con hasta 1000 particiones en SQL Server 2008 y 15000 particiones en SQL Server 2012, y el emplazamiento de los datos es manejado automáticamente por SQL Server. Esta característica está disponible sólo en la Edición Enterprise de SQL Server.

Para crear una tabla particionada para almacenar reportes mensuales primero crearemos grupos de archivos adicionales. Un grupo de archivos es una unidad de almacenamiento lógica. Cada base de datos tiene un grupo de archivos que contiene el archivo de datos primario (.mdf). Un grupo de archivos adicional y definido por el usuario puede ser creado para contener archivos secundarios (.ndf). Nosotros crearemos 12 grupos de archivos por cada mes: Para verificar los grupos de archivos creados y disponibles en la base de datos actual ejecute la siguiente consulta:

ALTER DATABASE BD_FP

ADD FILEGROUP Enero

ALTER DATABASE BD_FP

ADD FILEGROUP Febrero

ALTER DATABASE BD_FP

ADD FILEGROUP Marzo

ALTER DATABASE BD_FP

ADD FILEGROUP Abril

ALTER DATABASE BD_FP

ADD FILEGROUP Mayo

ALTER DATABASE BD_FP

ADD FILEGROUP Junio

ALTER DATABASE BD_FP

ADD FILEGROUP Julio

ALTER DATABASE BD_FP

ADD FILEGROUP Agosto

ALTER DATABASE BD_FP

ADD FILEGROUP Septiembre

ALTER DATABASE BD_FP

ADD FILEGROUP Octubre

ALTER DATABASE BD_FP

ADD FILEGROUP Noviembre

ALTER DATABASE BD_FP

ADD FILEGROUP diciembre

SELECT name AS AvailableFilegroups

FROM sys.filegroups

WHERE type = 'FG'

Cuando son creados grupos de archivos añadiremos el archivo .ndf a cada grupo de archivos: De la misma manera los archivos a todos los grupos de archivos creados especificando el nombre lógico del archivo y el nombre del archivo del sistema operativo (físico) para cada grupo de archivos. Por ejemplo:

ALTER DATABASE [BD_FP]

ADD FILE

(

NAME = [PartEnero],

FILENAME = 'C:\SQL\BD_FP.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [Enero]

ALTER DATABASE [BD_FP]

ADD FILE

(

NAME = [PartFebrero],

FILENAME = 'C:\SQL\BD_FP2.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [Febrero]

ALTER DATABASE [BD_FP]

ADD FILE

(

NAME = [PartMarzo],

FILENAME = 'C:\SQL\BD_FP3.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [Marzo]

ALTER DATABASE [BD_FP]

ADD FILE

(

NAME = [PartAbril],

FILENAME = 'C:\SQL\BD_FP4.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [Abril]

ALTER DATABASE [BD_FP]

ADD FILE

(

NAME = [PartMayo],

FILENAME = 'C:\SQL\BD_FP5.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [Mayo]

ALTER DATABASE [BD_FP]

ADD FILE

(

NAME = [PartJunio],

FILENAME = 'C:\SQL\BD_FP6.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [Junio]

ALTER DATABASE [BD_FP]

ADD FILE

(

NAME = [PartJulio],

FILENAME = 'C:\SQL\BD_FP7.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [Julio]

ALTER DATABASE [BD_FP]

ADD FILE

(

NAME = [PartAgosto],

FILENAME = 'C:\SQL\BD_FP8.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [Agosto]

ALTER DATABASE [BD_FP]

ADD FILE

(

NAME = [PartSeptiembre],

FILENAME = 'C:\SQL\BD_FP9.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [Septiembre]

ALTER DATABASE [BD_FP]

ADD FILE

(

NAME = [PartOctubre],

FILENAME = 'C:\SQL\BD_FP10.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [Octubre]

ALTER DATABASE [BD_FP]

ADD FILE

(

NAME = [PartNoviembre],

FILENAME = 'C:\SQL\BD_FP11.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [Noviembre]

ALTER DATABASE [BD_FP]

ADD FILE

(

NAME = [PartDiciembre],

FILENAME = 'C:\SQL\BD_FP12.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [Diciembre]

Para verificar archivos creado añadidos a los grupos de archivos ejecute la siguiente consulta:

SELECT

name as [FileName],

physical_name as [FilePath]

FROM sys.database_files

where type_desc = 'ROWS'

Después de crear grupos de archivos adicionales para almacenar datos crearemos una función de partición. Una función de partición es una función que mapea las filas de una tabla particionada en particiones basada en los valores de una columna de partición. En este ejemplo crearemos una función de partición que particiona una tabla en 12 particiones, una por cada mes de los valores de un año en una columna de fecha:

CREATE PARTITION FUNCTION [PartitioningByMonth] (datetime)

AS RANGE RIGHT FOR VALUES ('20140201', '20140301', '20140401',

'20140501', '20140601', '20140701', '20140801',

'20140901', '20141001', '20141101', '20141201');

Para mapear las particiones de una tabla particionada a grupos de archivos y determinar el número y dominio de las particiones de una tabla crearemos un esquema de partición:

CREATE PARTITION SCHEME PartitionBymonth

AS PARTITION PartitioningBymonth

TO (Enero, Febrero, Marzo,

Abril, Mayo, Junio, Julio,

Agosto, Septiembre, Octubre,

Noviembre, Diciembre);

Ahora vamos a crear la tabla usando el esquema de partición PartitionBymonth, y la llenaremos con datos de prueba:

CREATE TABLE Reportes

(ReportDate datetime PRIMARY KEY,

MonthlyReport varchar(max))

ON PartitionBymonth (ReportDate);

INSERT INTO Reportes(ReportDate,MonthlyReport)

SELECT '20140105', 'ReportEnero' UNION ALL

SELECT '20140205', 'ReportFebryary' UNION ALL

SELECT '20140308', 'ReportMarzo' UNION ALL

SELECT '20140409', 'ReportAbril' UNION ALL

SELECT '20140509', 'ReportMayo' UNION ALL

SELECT '20140609', 'ReportJunio' UNION ALL

SELECT '20140709', 'ReportJulio' UNION ALL

SELECT '20140809', 'ReportAugust' UNION ALL

SELECT '20140909', 'ReportSeptiembre' UNION ALL

SELECT '20141009', 'ReportOctubre' UNION ALL

SELECT '20141109', 'ReportNoviembre' UNION ALL

SELECT '20141209', 'Reportdiciembre'

Ahora verificaremos las filas en las diferentes particiones:

SELECT

p.partition_number AS PartitionNumber,

f.name AS PartitionFilegroup,

p.rows AS NumberOfRows

FROM sys.partitions p

JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id

JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id

WHERE OBJECT_NAME(OBJECT_ID) = 'Reportes'

Ahora simplemente copie los datos desde su tabla y renombre una tabla particionada.

Buscar este blog

SQL Server