Particionado en SQL Server (I/II)

Pese a que el particionado de datos apareció en SQL Server 2005, no he visto que los escenarios habituales de entornos OLTP incluyan el particionado de datos. Unas veces por desconocimiento, otras por miedo, el caso es que no es una característica que personalmente haya observado en las instalaciones como algo a tener muy en cuenta. Pese a que no se trate de una característica en la que haya que pensar habitualmente, es cierto que en bastantes ocasiones ha encajado como solución.El particionado consiste en realizar una separación física de los datos de un objeto, para generalmente obtener rendimientos superiores en determinados tipos de consultas, como más adelante veremos (en siguiente post). Típicamente es una característica que se suele aprovechar en escenarios analíticos de BI al trabajar con Datawarehouses, pero también es factible su utilización en entornos OLTP para mejorar escenarios mediante la distribución física de los datos entre nuestros subsistemas de disco, por ejemplo.

Beneficios del particionado de datos

Los índices y tablas particionadas poseen una serie de beneficios cuando se comparan con vistas particionadas manuales y otras formas de particionado manual:

• SQL Server maneja por nosotros donde colocar los datos
• Los objetos particionados se ven como objetos normales a todos los efectos, independientemente del numero de particiones
• Podemos trabajar a nivel de partición
• Se puede elegir una estrategia de particionado independiente para cada partición de un objeto
• Podemos reconstruir una partición de un índice, sin tocar el resto de particiones
• Existen optimizaciones específicas del motor relacional para trabajar de forma eficiente con particiones.

• Operadores específicos optimizados para operaciones con tablas particionadas
• Podemos configurar nivel de escalado de bloqueos a nivel de partición en lugar de a toda la tabla

¿Qué objetos se pueden particionar?

Se pueden particionar los siguientes objetos:

• Tablas
• Vistas indexadas
• Índices

¿Como podemos crear una tabla particionada?

El concepto es sencillo y consta de tres pasos:

1. Crear la función de particionado
2. Crear el esquema de particionado
3. Crear el objeto, asociándolo al esquema de particionado

El proceso se puede ver en el siguiente ejemplo de código.

Lo cual, distribuye los datos de la siguiente manera:

NOTA: Para entender el por qué continuad leyendo

Crear la función de particionado

Para crear la función de particionado, debemos recurrir a la cláusula “CREATE PARTITION FUNCTION”.

En ella vamos a especificar 3 cosas:

1. El tipo de datos de entrada
2. Los valores del umbral
3. Hacia donde caen los valores del umbral en los valores límite(RIGHT o LEFT)

NOTA: Los tipos de datos de entrada pueden ser cualquiera, excepto: text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), alias data types, o CLR user-defined data types.

Por tanto, en la declaración anterior “CREATE PARTITION FUNCTION pfn (int) AS RANGE LEFT FOR VALUES(10,30,50)”, vemos que:

1. El tipo de datos será int
2. Los valores de corte serán 10, 30 y 50
3. Los valores de corte quedarán en la partición de la izquierda

Esto quiere decir, que esta función de particionado, genera 4 “salidas”, que podemos ver representadas en los 4 colores de la imagen anterior.

De momento tenemos una función de particionado, pero que no aplica a nada. Lo siguiente por tanto, será crear el esquema de particionado al que asignar dicha función de particionado

Crear el esquema de particionado

Un esquema de particionado, sirve para mapear las particiones a los Filegroups que deseemos. Para crear el esquema de particionado, debemos recurrir a la cláusula “CREATE PARTITION SCHEME”.

En dicha cláusula vamos a especificar 2 cosas únicamente:

1. La función de particionado que va a direccionar su salida a cada partición
2. Los nombres de los filegroups a los que cada partición irá mapeada.

Esto quiere decir, que si nos fijamos en la cláusula del ejemplo anterior “CREATE PARTITION SCHEME p_schema AS PARTITION pfn TO ([FG1], [FG2], [FG3], [FG4])”, podemos ver que este esquema está mapeado a 4 filegroups llamados FG1, FG2,FG3 y FG4, que obviamente deben existir y que además, están vinculados a la salida de la función “pfn”. Obviamente, el nº de particiones de pfn y del esquema de particionado deben ser idénticos para que funcione.

Información de interés: Desde SQL Server 2008 SP2 se soportan más de 15.000 particiones por objeto. Para más información: http://technet.microsoft.com/en-us/library/gg981694.aspx

Crear el objeto particionado

Por último ya solo queda crear el objeto particionado. Para ello se utiliza la sintaxis habitual, pero añadiendo al final la referencia a qué funcion de particionado (a la que le proporcionaremos la columna por la que particionar) por la que se particionará.

Pero ¿y si la el objeto ya existe?

En el caso de que el objeto ya exista, podemos optar por dos opciones:

1. Recrear su índice clustered, pero esta vez de forma particionada
2. Utilizar ALTER TABLE SWITCH para modificar los datos de la tabla para que solo tenga una única partición, a la que posteriormente iremos añadiendo otras.

En el siguiente post, realizaré un ejemplo de como conseguir esto.

Operaciones sobre particiones

Una de las ventajas de utilizar particionado es que existen operaciones exclusivamente optimizadas para estos escenarios, pensadas para mejorar la manejabilidad de objetos particionados. Esto quiere decir por tanto, que las particiones no son estáticas y que por tanto, podremos modificar el esquema de particionado amoldándolo a nuestras necesidades

Las operaciones mas comunes que podemos realizar con objetos particionados son:

• MERGE

• Unir dos particiones en una sola (obviamente las dos particiones deben estar juntas)
• SPLIT

• Separar una partición en 2 particiones
• SWITCH

• Mover una partición de un objeto a otro
• Útil para gestionar eficientemente el escenario de datos históricos, donde movemos datos de una tabla a otra

Operación MERGE

La operación MERGE nos va a permitir mezclar dos particiones solapadas en una única partición. La forma de realizar dicha acción consiste en eliminar uno de los valores límite. Por ejemplo, en esta secuencia de particiones:

{min … -1}, {0 … 9}, {10 … 19}, {20 … max}

Se trataría de eliminar los valores límite 0, 10 o 20. De esta forma, si decidimos mezclar las particiones 2 y 3, lo podríamos realizar de esta sencilla forma

ALTER PARTITION FUNCTION tu_pf MERGE RANGE(10);

Con lo que el resultado seria:

{min … -1}, {0 … 19}, {20 … max}

Operación SPLIT

La operación SPLIT sirve para separar una partición en 2. Al hacerlo, hay que indicar qué Filegroup será el que se utilizará para los valores que caigan en la nueva partición. Al utilizar la operación SPLIT debes recordar los siguientes aspectos:

• El nuevo valor límite debe pertenecer al rango de valores de la función de particionado
• El nuevo valor límite no puede ser un valor límite asignado actualmente en la función de particionado
• Si al dividir una partición con SPLIT, se producen dos particiones con datos, SQL Server distribuirá respectivamente los datos a sus particiones, con lo que ocurrirá un crecimiento en el log de transacciones para albergar las operaciones INSERT y DELETE que internamente se producirán. Aproximadamente el crecimiento del log de transacciones será del tamaño de los datos a moverse a la nueva partición.

Teniendo este rango de particiones:

{min … -1}, {0 … 19}, {20 … max}

Un ejemplo de utilización de la cláusula SPLIT es la siguiente:

ALTER PARTITION FUNCTION tu_pf () SPLIT RANGE (17);

Dará como resultado:

{min … -1}, {0 … 16}, {17 … 19}, {20 … max}

Consideraciones sobre SPLIT y MERGE

Como consideración importante, cabe destacar que las operaciones SPLIT y MERGE, interesa que sean realizadas sobre particiones vacías, ya que de lo contrario incurren en movimiento de datos, para albergar las filas a las particiones. Debido a ello, las operaciones de SPLIT y MERGE pueden suponer un aumento de espacio en el log de transacciones (donde las operaciones quedan reflejadas como DELETE e INSERT) y un aumento del tiempo de operación intrínseco al propio movimiento de los datos.

Siempre que sea posible, intentad que las operaciones SPLIT y MERGE se realicen sobre particiones vacías, ya que de esta forma se incurre solo en una modificación de esquema.

Operación SWITCH

La operación SWITCH es una operación interesante para mover datos de forma rápida entre objetos. Es una operación que solo incurre en modificación de metadatos y que por tanto se realiza de forma instantánea (independientemente de cuantos datos existan en la partición).

Esta operación, que veremos en el siguiente post, nos será muy útil a la hora de extraer información de tablas a tablas históricos, puesto que de una forma rápida y sencilla podremos extraer el contenido de una partición de forma inmediata.

El problema que resuelve esta operación es el que queda ilustrado en estas 2 imágenes:

1. Tenemos una tabla P, con datos en la partición 2 que queremos mover a una tabla S
2. Realizamos la operación SWITCH y el resultado es que la tabla S se queda con los datos, mientras que la partición 2 de la tabla P se queda vacía.

En el siguiente post, veremos todo lo que hemos aprendido en esta introducción sobre particionado de datos en SQL Server.