Gestion de Tablas Particionadas

Un desafío para un diseñador de base de datos es diseñar sistemas que sean mantenibles, escalables y que tengan un buen desempeño.

Aunque el objetivo suena obvio,  algunas veces es difícil de lograr.

Una técnica  usada para intentar lograr un balance razonable entre  mantenimiento y escalabilidad vs. Desempeño  es las Tablas Particionadas.

 Esto no es nuevo, fue un cambio significante en SQL Server 2005 y continua en SQL Server 2008 proveyendo herramientas para ayudar a los desarrolladores a lograr esta tarea.

¿Por qué particionar?

El particionamiento divide grandes cantidades de datos en pequeños y más manejables sets.

¿Como ayuda?

 Modificar y buscar datos en un conjunto más pequeño es más fácil y rápido que trabajar con un gran set de datos.

Por ejemplo: Buscar  una factura por una fecha dada es más eficiente cuando buscamos en un archivador organizado.  Lo mismo sucede cuando buscamos en las tablas.

Beneficios:

Más rápido y eficiente acceso a datos.

El desempeño incrementa para operaciones en paralelo con servidores multiprocesadores,  cada procesador puede acceder a multiples particiones simultáneamente.

Nota:

Particionamiento de tablas e índices solo está disponible en SQL Server 2008 Enterprise, Developer y Ediciones de Evaluacion.

Terminos a Considerar

Partition Key:  Una columna en una Tabla determina a cual partición los datos residirán

Partition Function :  Una función que especifica que datos iran a que partición definidos por el Partition Key.

Partition Schema: Mapea una partición a un Filegroups.

Limites LEFT y RIGHT

Una función para 4 particiones:

 

Aquí lo que significa:

 

-- Crear una funcion de particion

CREATE PARTITION FUNCTION VentasxQuarterPF(datetime)

AS RANGE RIGHT

FOR VALUES ('19960101', '19960401', '19960701', '19961001')

GO

-- Filegroup para ventas < 01/01/1996

ALTER DATABASE Demo

ADD FILEGROUP Ventas1995Q4FG

GO

-- -- Filegroup para ventas >= 01/01/1996 AND <= 03/31/1996

ALTER DATABASE Demo

ADD FILEGROUP Ventas1996Q1FG

GO

-- Filegroup para ventas >= 04/01/1996 AND <= 06/30/1996

ALTER DATABASE Demo

ADD FILEGROUP Ventas1996Q2FG

GO

-- Filegroup para ventas >= 07/01/1996 AND <= 09/30/1996

ALTER DATABASE Demo

ADD FILEGROUP Ventas1996Q3FG

GO

-- Filegroup para ventas >= 10/01/1996 AND <= 12/31/1996

ALTER DATABASE Demo

ADD FILEGROUP Ventas1996Q4FG

GO

-- Para futuro crecimiento, adicionar un Filegroup

ALTER DATABASE Demo

ADD FILEGROUP Ventas1997Q1FG

GO

Creando archivos para los FileGroup

-- Ventas2002Q4FG filegroup

ALTER DATABASE Demo

ADD FILE(NAME = Ventas1995Q4,

    FILENAME = 'C:\SQL Server\Ventas1995Q4.ndf')

TO FILEGROUP Ventas1995Q4FG

GO

-- Ventas1996Q1FG filegroup

ALTER DATABASE Demo

ADD FILE(NAME = Ventas1996Q1,

    FILENAME = 'C:\SQL Server\Ventas1996Q1.ndf')

TO FILEGROUP Ventas1996Q1FG

GO

-- Ventas1996Q2FG filegroup

ALTER DATABASE Demo

ADD FILE(NAME = Ventas1996Q2,

    FILENAME = 'C:\SQL Server\Ventas1996Q2.ndf')

TO FILEGROUP Ventas1996Q2FG

GO

-- Ventas1996Q3FG filegroup

ALTER DATABASE Demo

ADD FILE(NAME = Ventas1996Q3,

    FILENAME = 'C:\SQL Server\Ventas1996Q3.ndf')

TO FILEGROUP Ventas1996Q3FG

GO

-- Ventas1996Q4FG filegroup

ALTER DATABASE Demo

ADD FILE(NAME = Ventas1996Q4,

    FILENAME = 'C:\SQL Server\Ventas1996Q4.ndf')

TO FILEGROUP Ventas1996Q4FG

GO

--  filegroup adicional

ALTER DATABASE Demo

ADD FILE(NAME = Ventas1997Q1,

    FILENAME = 'C:\SQL Server\Ventas2004Q1.ndf')

TO FILEGROUP Ventas1997Q1FG

GO

-- Crear un partition scheme usando un file group diferente para cada particion

-- NOTE: Ventas1997Q1FG es una particion extra.  SQL la marcara como NEXT used

CREATE PARTITION SCHEME VentasxQuarterPS

AS PARTITION VentasxQuarterPF

TO (Ventas1995Q4FG, Ventas1996Q1FG, Ventas1996Q2FG,

    Ventas1996Q3FG, Ventas1996Q4FG, Ventas1997Q1FG)

GO

Crear la tabla donde se utilizara las particiones

CREATE TABLE FacturasPT(

       [Codigo_Pedido] [int] NOT NULL,

       [Codigo_Cliente] [varchar](20) NULL,

       [Fecha] [datetime] NULL,

       [Origen] [varchar](50) NULL,

       [Descripcion] [varchar](max) NULL,

       [Total] [money] NOT NULL)

       ON VentasxQuarterPS(Fecha)

      

      

            

       INSERT INTO FacturasPT

       SELECT p.Codigo_Pedido, p.Codigo_Cliente, p.Fecha, p.Origen, p.Descripcion, p.Total

         FROM Pedidos p

      

SET STATISTICS IO ON

SELECT * FROM FacturasPT p

SELECT p.Codigo_Cliente, p.Total, p.Descripcion FROM Pedidos p

----  INFORMACION DE LA PARTICION

SELECT

  $PARTITION.VentasxQuarterPF(Fecha) AS Partition,

  COUNT(*) AS NumeroVentas

FROM FacturasPT

GROUP BY $PARTITION.VentasxQuarterPF(Fecha)

ORDER BY PARTITION

SELECT DISTINCT Fecha,

    $PARTITION.VentasxQuarterPF(Fecha) AS Partition

FROM FacturasPT

WHERE Fecha IN('19960115', '19960627',

    '19960817', '19971105')

ORDER BY Fecha

SELECT name, data_space_id, type, function_id

FROM sys.partition_schemes

-- Diplay information about partition filegroups

SELECT name, data_space_id, type

FROM sys.data_spaces