GuilleSQL :: Microsoft SQL Server, SSIS, y más !!

Particionamiento de tablas e índices en SQL Server (Partitioning)


El particionamiento de tablas e índices es una importante técnica de base de datos, existente en SQL Server desde hace varios años. En el presente artículo, se explica a un nivel básico la forma de crear tablas e índices particionados (utilizando CREATE PARTITION FUNCTION y CREATE PARTITION SCHEME), cómo mover particiones de forma rápida entre tablas (conocido como Archivado, ejecutando ALTER TABLE SWITCH PARTITION), cómo añadir y eliminar particiones a una tabla o índice existente (SPLIT y MERGE), y otros detalles de interés, con ejemplos y vídeo demo incluido.

La Función de Particionamiento, el primer paso

Lo primero que deberemos hacer antes de poder particionar tablas e índices en SQL Server, es crear una Función de Particionamiento, la cual, tomará como entrada un único campo de la tabla a particionar (ojo, que puede valer también una columna calculada - computed column), e identificará los valores frontera que definirán el conjunto de particiones deseado. Esto se ve muy claro con un ejemplo:

CREATE PARTITION FUNCTION [pfDatos] (datetime)
AS RANGE RIGHT FOR VALUES
('20100101','20100201','20100301');
GO

La anterior Función de Particionamiento define tres valores frontera para un valor de entrada de tipo fecha, por lo tanto, tenemos las siguientes cuatro particiones:

  • Partición 1: Valores < 20100101 (Diciembre 2010 y meses anteriores)
  • Partición 2: 20100101 <= Valores < 20100201 (Enero 2010)
  • Partición 3: 20100201 <= Valores < 20100301 (Febrero 2010)
  • Partición 4: 20100301 <= Valores (Abril 2010 y meses posteriores)

A nivel de sintaxis, el siguiente ejemplo es bastante representativo. Podemos cambiar la palabra reservada RIGHT por LEFT, lo cual sirve para indicar qué partición deseamos que contenga cada valor frontera (la partición que queda a su izquierda o la que queda a su derecha). No hay mayor secreto.

Una vez creada una Función de Particionamiento, es posible modificarla para añadir o quitar particiones, utilizando ALTER PARTITION FUNCTION SPLIT o ALTER PARTITION FUNCTION MERGE (añadiremos o quitaremos particiones de una en una, no hay sintaxis para afectar a varias particiones en un único ALTER). Es importante entender las funciones de particionamiento como algo dinámico, que podemos modificar con el paso del tiempo, para ajustar a las necesidades actuales de cada momento.

Deberemos tener en cuenta, que la ejecución de estas operaciones (SPLIT y MERGE) está especialmente recomendado sobre particiones vacías, aprovechando en dicho caso una mejora de rendimiento, propia de poder evitar escanear las páginas de datos de las particiones para mover los datos correspondientes. Pero ahora no es el mejor momento de contar mayores detalles de esto.

El Esquema de Particionamiento, el segundo paso

Ahora es necesario crear el Esquema de Particionamiento, el cual, nos permitirá asignar a cada partición, el Grupo de Ficheros (FileGroup) que deseamos que utilice para almacenar sus datos. Téngase en cuenta, que al crear el Esquema de Particionamiento, tenemos que asociarlo a una Función de Particionamiento. No es necesario que cada partición utilice un Grupo de Ficheros (FileGroup) exclusivo para ella. De hecho, en muchos casos, se utiliza un único FileGroup para todas las particiones. La decisión, es una cuestión de Diseño, que habrá que analizar en cada caso.

A continuación, se muestra un ejemplo sencillo, a través del cual se asignan todas las particiones a un mismo Grupo de Ficheros (FileGroup).

CREATE PARTITION SCHEME [psDatos]
AS PARTITION [pfDatos] ALL TO ([PRIMARY]);

Sin embargo, también es posible asignar cada partición a un Grupo de Ficheros (FileGroup) específico, sean todos los Grupos de Ficheros distintos, o incluso repitiendo algunos Grupo de Ficheros (FileGroup). Hasta es posible crear el Esquema de Particionamiento especificando Grupos de Ficheros de sobra (ej: la Función de Particionamiento define cuatro particiones, y el Esquema de Particionamiento define cinco o más Grupos de Ficheros). Veamos un ejemplo:

CREATE PARTITION SCHEME [psDatos]
AS PARTITION [pfDatos] TO (FG1, FG1, FG1, FG2, FG3);

En el anterior ejemplo, hemos repetido varias veces el Grupo de Ficheros FG1, y además hemos indicado cinco Grupos de Ficheros, cuando la Función de Particionamiento tan sólo define cuatro particiones. Esto podría servir, si en un futuro creamos una nueva partición (con ALTER PARTITION FUNCTION SPLIT), de tal modo, que dicha nueva partición utilizará el Grupo de Ficheros que sobra.

Si tenemos varios Grupos de Ficheros de sobra, sólo uno estará marcado como NEXT USED. Si no tenemos ningún Grupo de Ficheros de sobra, podemos añadir un Grupo de Ficheros adicional a nuestro Esquema de Particionamiento utilizando la sentencia ALTER PARTITION SCHEME, o si lo deseamos, podemos reutilizar un Grupo de Ficheros ya utilizado en el Esquema de Particionamiento, y con la sentencia ALTER PARTITION SCHEME marcarlo como NEXT USED.

El detalle del Grupo de Ficheros (FileGroup) marcado como NEXT USED tiene gran importancia, ya que antes de poder ejecutar un SPLIT, es necesario que un Grupo de Ficheros esté marcado como NEXT USED. Ejemplo: Creamos un Función de Particionamiento, y seguidamente, creamos un Esquema de Particionamiento para la misma, mapeando todas las particiones al mismo Grupo de Ficheros. En la creación de este Esquema de Particionamiento, se marcará como NEXT USED el Grupo de Fichero especificado en su creación, por lo que podremos realizar un SPLIT sin problemas. Sin embargo, si intentamos realizar un segundo SPLIT, ningún Grupo de Ficheros estará marcado como NEXT USED, por lo que fracasará, debiendo ejecutar un ALTER PARTITION SCHEME antes de poder realizar el SPLIT. Moraleja: Al utilizar un Esquema de Particionamiento que mapea todas las particiones al mismo Grupo de Ficheros, después de cada SPLIT se debe realizar un ALTER PARTITION SCHEME para marcar el NEXT USED.

Crear las tablas e índices particionados

Ahora ya estamos en situación de poder crear tablas e índices particionados, indicando en su creación qué Esquema de Particionamiento deseamos utilizar y el campo empleado para particionar. Existen multitud de posibilidades. Por ejemplo, podemos tener una tabla particionada con un Esquema de Particionamiento, y cada uno de sus índices, particionados con otros Esquemas de Particionamiento diferentes, por el mismo, o sin particionar. De nuevo es una cuestión de diseño, cuyo detalle se escapa del alcance del presente artículo.

Como ejemplo, vamos a incluir la creación de una tabla con un índice, de tal modo que ambos objetos utilizan el mismo Esquema de Particionamiento. Esto es uno de los requisitos para poder realizar un movimiento rápido de particiones entre tablas particionadas con ALTER TABLE SWITCH PARTITION (hay muchos más requisitos, como que las tablas origen y destino deben mantener exactamente el mismo esquema, los mismos campos y en el mismo orden, nulidades de campos, etc.).

CREATE TABLE dbo.tblDatos
(
ID UNIQUEIDENTIFIER NOT NULL
,DESCRIPCION VARCHAR(200) NOT NULL
,FECHA DATETIME NOT NULL
,CONSTRAINT PK_tblDatos PRIMARY KEY NONCLUSTERED (ID, FECHA) ON psDatos(FECHA)
) ON psDatos(FECHA);

Ahora que ya tenemos una tabla particionada, podemos insertar datos en ella. Si deseamos poder conocer en qué partición se almacena cada fila, podemos utilizar la función $PARTITION, como se muestra en el siguiente ejemplo.

SELECT *, $PARTITION.pfDatos(FECHA) [Partition] FROM dbo.tblDatos

Si deseamos mover una partición de una tabla particionada a otra, podemos utilizar la sentencia ALTER TABLE SWITCH PARTITION, pero antes de nada, deberemos revisarnos bien la documentación, ya que como comentamos antes esto conlleva varios requisitos (ej: las tablas origen y destino deben tener exactamente el mismo esquema, la partición destino debe estar vacía, la partición origen debe ser un subconjunto de la partición origen, etc). Así, si cumplimos todos los requisitos, podremos utilizar la sentencia ALTER TABLE SWITCH PARTITION indicando cuál es la tabla y partición origen, y también la tabla y partición destino. A continuación se muestra un ejemplo.

ALTER TABLE dbo.tblDatos SWITCH PARTITION 2 TO dbo.tblDatosHistoricos PARTITION 2

Tenemos más posibilidades, como por ejemplo, es posible realizar un ALTER TABLE SWITCH PARTITION para mover una tabla existente no particionada, a una partición de una tabla particionada existente. Esto por ejemplo, resulta de gran utilidad para realizar cargas rápidas de datos en entornos de Data Warehouse con SELECT INTO y ALTER TABLE SWITCH PARTITION (ej: tenemos una tabla de hechos particionada, y para cargar los datos de un nuevo periodo de tiempo, realizamos un SELECT INTO para crear una tabla con los datos que deseamos cargar, y seguidamente, hacemos el ALTER TABLE SWITCH PARTITION para asignar la tabla recién creada como una partición más de la tabla de hechos: la ventaja, la mejora de rendimiento de la operación de registro mínimo propia del SELECT INTO, y la inmediatez del ALTER TABLE SWITCH PARTITION).

Código de ejemplo y Vídeo DEMO

Para finalizar, aprovecho para colgar tanto una presentación como un código de ejemplo, el cual incluye comentarios autoexplicativos. Dicho código, crea un par de tablas particionadas, con sus correspondientes Funciones de Particionamiento y Esquemas de Particionamiento, inserta datos de ejemplo, mueve una partición de una tabla a otra con ALTER TABLE SWITCH PARTITION, y reajusta las particiones haciendo los SPLIT y MERGE necesarios sobre las Funciones de Particionamiento. Tanto la presentación como el código de ejemplo, están disponibles para descarga a través del siguiente ZIP.

Descargar Script y Presentación sobre Particionamiento y Archivado de Tablas e Indices en SQL Server

El video es una demostración en vivo del código de ejemplo adjunto, ejecutado paso a paso sobre una base de datos recién creada. Este es el primer Video que se cuelga en Portal GuilleSQL, y de momento, la verdad que no hemos conseguido la calidad de video que queríamos, pero al intentar generarlo con mayor calidad, el portátil no daba de sí... Sorrys, para el próximo video, intentaremos que quede mejor !!

Poco más. Como siempre, espero que os guste.



Comentarios

magicart - 08/09/2012 (UTC)
Hola Guille. Gracias por tan excelente artículo. De hecho ya he aplicado todos los pasos, pero tengo una duda. Ya tengo una tabla (X) particionada y funcionando. Ahora voy a una segunda tabla (Y), creo la función y el esquema pertinente. Casualmente el campo de entrada en Y es el mismo que X (mes), por error asigné a la tabla Y el esquema de la tabla X. Esto no me generó ningún error, pero ¿esto es eficiente?, es decir puedo tener un esquema de partición y asignárselo a todas las tablas a particionar?. Claro tomando en cuenta el campo de la función (asumo esto)... ¿Qué opinas?


GuilleSQL - 08/09/2012 (UTC)
Hola,

Yo creo que sí, aunque no he realizado pruebas de rendimiento. En principio, no veo inconveniente.

Saludos,
Guille


Norma - 08/09/2019 (UTC)
Hola, utilizando dos filegroups (primary, historico) particione en dos partes una tabla, todo bien, realice el backup de uno de los filegroup (primary), y se me presenta el siguiente problema, al restaurar el backup que realice, sobre el mismo equipo, la tabla queda no disponible por lo que los índices quedan referenciando a la partición a la que no le hice el backup y que no esta.
Error: Msg 679, Level 16, State 1, Line 1
One of the partitions of index 'PK__FBC015__245BEA' for table 'dbo.TablaA'(partition ID 7205759497) resides on a filegroup ("Historico") that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.

Solo necesito trabajar con los datos de un filegroup (primary), que solución se recomienda. Saludos!



Miembros de
Miembros de GITCA (Global IT Community Association)

Menu de Usuario
  Iniciar Sesión
  Registrarse
  Restablecer Contraseña
  Ventajas de Registrarse

Acerca de
  Contigo desde Oct 2007
  771 usuarios registrados
  86146 pageloads/mes
  Ranking Alexa 498160

Social Networks
Sigue a Portal GuilleSQL en Linkedin !!
Sigue a Portal GuilleSQL en Twitter !!



Archivo

Marzo de 2019 (1)
Octubre de 2018 (1)
Julio de 2018 (1)
Junio de 2018 (4)
Mayo de 2018 (5)
Abril de 2018 (3)
Marzo de 2018 (2)
Febrero de 2018 (7)
Enero de 2018 (1)
Diciembre de 2017 (15)
Noviembre de 2017 (7)
Junio de 2017 (3)
Mayo de 2017 (1)
Marzo de 2017 (3)
Enero de 2017 (4)
Junio de 2016 (1)
Mayo de 2016 (2)
Abril de 2016 (2)
Septiembre de 2015 (2)
Agosto de 2015 (2)
Junio de 2015 (10)
Mayo de 2015 (4)
Abril de 2015 (8)
Marzo de 2015 (11)
Octubre de 2014 (3)
Septiembre de 2014 (7)
Agosto de 2014 (5)
Julio de 2014 (2)
Mayo de 2014 (4)
Abril de 2014 (4)
Marzo de 2014 (4)
Febrero de 2014 (1)
Enero de 2014 (5)
Diciembre de 2013 (8)
Noviembre de 2013 (2)
Octubre de 2013 (7)
Septiembre de 2013 (6)
Agosto de 2013 (1)
Julio de 2013 (6)
Junio de 2013 (11)
Mayo de 2013 (7)
Abril de 2013 (6)
Febrero de 2013 (5)
Enero de 2013 (7)
Diciembre de 2012 (12)
Noviembre de 2012 (13)
Octubre de 2012 (5)
Septiembre de 2012 (3)
Agosto de 2012 (6)
Julio de 2012 (4)
Junio de 2012 (1)
Mayo de 2012 (2)
Abril de 2012 (7)
Marzo de 2012 (16)
Febrero de 2012 (9)
Enero de 2012 (5)
Diciembre de 2011 (10)
Noviembre de 2011 (10)
Octubre de 2011 (4)
Septiembre de 2011 (5)
Agosto de 2011 (2)
Julio de 2011 (2)
Junio de 2011 (4)
Mayo de 2011 (2)
Abril de 2011 (6)
Marzo de 2011 (4)
Febrero de 2011 (10)
Enero de 2011 (5)
Diciembre de 2010 (6)
Noviembre de 2010 (4)
Octubre de 2010 (8)
Septiembre de 2010 (4)
Agosto de 2010 (1)
Julio de 2010 (3)
Mayo de 2010 (5)
Abril de 2010 (6)
Marzo de 2010 (8)
Febrero de 2010 (3)
Enero de 2010 (1)
Diciembre de 2009 (9)
Noviembre de 2009 (14)
Octubre de 2009 (2)
Septiembre de 2009 (8)
Agosto de 2009 (2)
Julio de 2009 (10)
Junio de 2009 (9)
Mayo de 2009 (10)
Abril de 2009 (9)
Marzo de 2009 (3)
Febrero de 2009 (2)
Enero de 2009 (3)
Noviembre de 2008 (2)
Octubre de 2008 (2)
Septiembre de 2008 (2)
Agosto de 2008 (5)
Julio de 2008 (5)
Junio de 2008 (1)
Mayo de 2008 (3)
Abril de 2008 (2)
Marzo de 2008 (2)
Febrero de 2008 (2)
Enero de 2008 (5)
Noviembre de 2007 (2)
Octubre de 2007 (2)






Copyright © 2007 GuilleSQL, todos los derechos reservados.