Tradicionalmente el particionado de datos no ha sido muy de mi agrado por las implicaciones de mantenimiento que se tenian asociadas. Tareas como reindexar, mover particiones entre tablas, actualizar estadísticas,…no eran tarea sencilla en entornos con carga 24×7 en el momento en el que particionabas una tabla. Ni que decir tiene que en SQL Server 2005 particionar llevaba aparejado un grave problema de rendimiento en el momento en que una query “tocaba” más de una partición…

Cada edición de SQL Server mejoraba algun aspecto (mejoras en nº de hilos entre particiones, operadores optimizados para descartar particiones,…) pero siempre quedaba en la balanza de “contras”, el mantenimiento de las tablas particionadas.

Con SQL Server 2014 la cosa ha mejorado bastante, hasta el punto de que ya no me da tanto “reparo” el promover el particionado de datos en grandes volúmenes de información, dado que ahora los inconvenientes ya están bastante diluidos como para que se puedan poner en la balanza y evitar particionar.

¿Qué es el particionado de datos?

De forma sencilla, podemos ver el particionado de datos como la división de una tabla física en tablas individuales mas pequeñas. Esto es una configuración administrativa del objeto, que se ve de forma transparente para las aplicaciones, que no tienen por qué saber que la tabla está particionada.

¿Por qué querríamos particionar una tabla físicamente?

Las razones son diversas, pero principalmente podemos entenderla como la necesidad de gestionar una tabla con cientos de millones de filas de forma eficiente. Un típico escenario de uso es el de una tabla con un volumen de inserciones-consultas elevado claramente diferenciado por conjuntos de su información almacenada-consultada:

• -Datos catalogados por fecha, de forma que quede claro cómo y cuando historificar
• -Datos catalogados por identificador en rangos de forma que cuando se consulta información, siempre acaban tocándose filas asociadas a los id entre rangos 10-100, o entre 101-200,…independientes entre si
• -Tabla altamente accedida de forma que tengamos una zona “caliente” altamente concurrente, y una zona “fria” con menor necesidad de “velocidad” (siempre pensemos en grándes volúmenes de datos)
• …

Si se aprovecha el particionado los beneficios son interesantes:

• -Si la query solo toca una partición, el escalado de operación puede que acabe solamente leyendo la partición en el peor caso (y no la tabla completa)
• -Podemos mover datos de particiones completas entre tablas (generalmente STAGING) instantáneamente independientemente del tamaño de la partición
• -Podemos tener parte de la tabla en un volumen de alto rendimiento (FusionIO) y parte de la tabla en discos modestos (RAID1 de discos mecánicos) pero mas baratos y de gran volumen para almacenar historicos
  • -La partición “caliente” va a ir muy rápido y la “fria” con baja probabilidad de acceso será mas lenta
• …

¿Qué mejoras tenemos en SQL Server 2014?

Las mejoras en particionado que aparecen en SQL Server 2014 son:

1. -Reindexar ONLINE las particiones de una tabla
2. -Indicar qué hacemos en caso de existir procesos consumiento una tabla afectada por operación de mantenimiento
3. -Actualizar las estadísticas por particion
4. -Actualizaciones automáticas y manuales de estadísticas a nivel de partición
5. -Particionado de datos en tablas almacenadas con columnar storage

En este post vamos a centrarnos de momento en las dos primeras, esperando a siguientes posts donde tratemos mejoras en estadisticas y columnar storage.

Crear nuestra tabla particionada para la demo

Creamos nuestra tabla particionada

--create partition function

CREATE PARTITION FUNCTION partition_function (int)

AS RANGE RIGHT FOR VALUES (1,2,3,4,5,6,7,8,9,10)

--Create partition schema

CREATE PARTITION SCHEME partition_scheme

AS PARTITION partition_function

ALL TO ([PRIMARY]);

--Create table

create table dbo.PartitionedTable

(

    id int identity(1,1) not null  ,

    [date] datetime not null default getdate(),

    classification_id int NOT NULL,

    varchar_column varchar(100) null

) on partition_scheme(classification_id)

create clustered index idx_id_partitioned_by_classification_id

on dbo.PartitionedTable

(

    id

)

on partition_scheme(classification_id);

Inserción de datos

Insertamos 1000 filas a nuestra tabla particionada. La única cosa “especial” es que estoy usando RAND() para que se genere un valor aleatorio en la columna classification_id y así la fila caiga aleatoriamente en una particion distinta y sea una distribución mas o menos estable. Recuerda que esto es únicamente por hacer una “demo”, obviamente la distribución de los datos, así como la función de partición es algo muy dependiente de la tabla, datos y uso que tengas en tu entorno concreto.

--Inser 1000 rows

Insert into dbo.PartitionedTable (classification_id,varchar_column)

VALUES (CAST((RAND()*100) AS int)%10,

        'varchar_column' + CAST(CAST((RAND()*100) AS int)%10 AS varchar(2)))

GO 1000

Ver la distribución de los datos insertados por particiones

Con esta consulta puedes ver el nº de filas que caen dentro de las particiones de tu tabla. En este caso a ti puede que te dén valores distintos porque dependerá de tu ejecución, pero mas o menos la distribución de filas por particiones será similar, siendo la partición 11 = 0 filas

SELECT

    object_name(p.object_id) as TableName,

    ix.Name as IndexName,

    p.partition_number,

    p.rows,

    p.data_compression_desc

FROM sys.partitions as p

INNER JOIN sys.indexes as ix

    on ix.object_id=p.object_id

    and ix.index_id=p.index_id

where object_name(p.object_id)='PartitionedTable'

        and ix.name='idx_id_partitioned_by_classification_id'

Reindexado ONLINE de particiones de la tabla

Como novedad en SQL Server 2014, ahora podemos marcar la operación de REBUILD como ONLINE = ON, con lo que los bloqueos durante dicha operación quedan minimizados a la mínima expresión.

Esto que parece una trivialidad a simple vista, es uno de los mejores cambios en 2014, puesto que cuando uno particiona, generalmente lo hace en escenarios donde hay una partición “caliente”, pequeña y que sufre muchas modificaciones e inserciones…ser capaces de actualizar estadísticas de dicha partición ONLINE sin tocar el resto de la tabla (que pueden ser decenas o centenas de GB de información) es un cambio muy positivo.

ALTER INDEX idx_id_partitioned_by_classification_id

on dbo.PartitionedTable

REBUILD PARTITION=10

WITH ( ONLINE=ON );

Especificar qué ocurre con procesos que estan modificando una partición

Otra de las mejoras interesantes reside en la opción de especificar qué hacer con los procesos que están afectando a la partición sobre la que queremos realizar una operación de mantenimiento (SWITCH, REBUILD,…). En este caso, la cláusula WAIT_AT_LOW_PRIORITY nos permite indicar qué queremos realizar en el caso de que existan procesos tocando datos de la partición. Por ejemplo el siguiente comando está especificando que se reindexe la partición nº 10 de forma ONLINE y que si existe algún proceso que tenga bloqueada la partición por estár realizando algun tipo de modificación en la misma, se espere 1 minuto. Tras dicho minuto de espera, queremos que se mate la propia operación de REINDEXADO (especificamos SELF)

ALTER TABLE dbo.PartitionedTable
REBUILD PARTITION=10 
WITH 
(
    ONLINE = ON 
    (WAIT_AT_LOW_PRIORITY 
        (
            MAX_DURATION = 1 MINUTES, 
            ABORT_AFTER_WAIT = SELF -- NONE | SELF | BLOCKERS
        ) 
    )
);

de esta forma, podemos especificar:

• -MAX_DURATION: Tiempo de espera antes de realizar acción
• -ABORT_AFTER_WAIT: Qué proceso abortar superado el tiempo de MAX_DURATION

Si especificamos BLOCKERS, estaremos indicando que se “maten” las operaciones que están impidiendo al comando REBUILD que se realice.

Si especificamos NONE, tendremos el comportamiento por defecto, esperar indefinidamente hasta que se pueda realizar la operación.

Para probar el REBUILD anterior, puedes abrir una nueva pestaña en tu SSMS y lanzar el siguiente código

Para probarlo, puedes lanzar este comando en otra ventana y de nuevo el ALTER TABLE … REBUILD…

-- vamos a borrar una fila de la partición 10, eso implica que borraremos una fila con valor 9
--
Begin tran

delete top(1) from dbo.PartitionedTable
WHERE classification_id = 9

Al no haber cerrado la transacción, si intentamos lanzar el REBUILD no vamos a poder reindexar a menos que especifiquemos WAIT_AT_LOW_PRIORITY. Puedes jugar con sus 3 parametros SELF, NONE y BLOCKERS para que veas qué ocurre.

Conclusiones

Las tareas de mantenimiento más frecuentes que afectan a tablas particionadas quedan prácticamente resueltas con estas mejoras. Recuerda que el que ahora particionar ya no tenga efectos “negativos” en administración (sobre todo en la parte estadísticas y reindexación) no quiere decir que sea buena idea ir directamente a particionar tus tablas. En SolidQ somos expertos en realizar análisis de rendimiento, donde entre otras cosas podemos decirte si tu escenario es idóneo o no (puedes salir perdiendo) para utilizar características como el particionado en SQL Server.