SQL Server

En estos últimos días hemos hablado del particionado de tablas como una de las medidas más eficaces para reducir la lectura de páginas de disco y reducir así el consumo de E/S, RAM y CPU. También vimos casos prácticos del particionado vertical de tablas y medimos su impacto en nuestras consultas. Hoy le toca el turno al particionado horizontal. El particionado horizontal consiste en dividir una tabla grande en varias partes más pequeñas, llamadas particiones, que se almacenan en diferentes grupos de archivos. De esta forma, podemos mejorar el rendimiento, la disponibilidad y la administración de los datos.

Elegir una buena columna de particionado horizontal

Lo primero que tenemos que decidir es por qué columna vamos a particionar. Esta será una columna por la que siempre se filtre y que no tenga o tenga muy poca variación. Lo más común es que sean columnas de fecha pero no es ninguna norma. Podremos crear particiones por cualquier columna que no sea de tipo timestamp o datos LOB (ntext, text, image, xml, varchar(max), nvarchar(max) y varbinary(max)). Lo que sí podemos usar son columnas calculadas siempre y cuando se creen con la condición PERSISTED. Esto nos es especialmente útil cuando queremos particionar por una concatenación de varias columnas.

Estas reglas se aplican tanto para particionar tablas como índices. Normalmente la partición de los índices Nonclustered será la misma que la de la tabla subyacente. Esto se llama índice alineado y permite al motor de base de datos cambiar las particiones de la tabla de forma rápida y eficaz al mismo tiempo que mantiene la estructura de la partición tanto en la tabla como en sus índices. 

Elegir una buena función de particionado horizontal

La función de partición es clave para el éxito del particionado horizontal. Lo que hace este objeto es especificar cómo se van a repartir los datos en función de las particiones que hemos creado. Una buena función de partición debe cumplir dos requisitos:

• Debe distribuir los datos de forma equilibrada entre las particiones, evitando que haya particiones muy grandes o muy pequeñas.
• Debe facilitar el acceso a los datos según el patrón de uso, evitando que haya que consultar varias particiones para obtener la información deseada.

Para elegir una buena función de partición, debemos analizar las características de los datos y las consultas que se realizan sobre ellos. Algunos factores a tener en cuenta son:

1. El tipo y el rango de valores de la columna de partición.
2. La frecuencia y el volumen de inserción, modificación y eliminación de los datos.
3. La frecuencia y el tipo de consultas que se realizan sobre la tabla.
4. El nivel de detalle o agregación que se requiere en las consultas.

Aunque en otros motores de bases de datos existen dos tipos de funciones de partición: por rango y por hash. En SQL Server solo vamos a poder particionar por rango. Las funciones por rango asignan los datos a las particiones según un intervalo de valores definido para cada una. Las funciones por hash asignan los datos a las particiones según un algoritmo matemático que calcula un valor numérico para cada fila. Cada tipo tiene sus ventajas e inconvenientes, dependiendo del caso de uso.

Diseñar un buen esquema de particionado horizontal

El esquema de particionado define en qué grupo de archivos se almacena cada partición. Un grupo de archivos es una colección lógica de archivos físicos que contienen los datos y los índices de una base de datos. SQL Server permite crear varios grupos de archivos para una misma base de datos, y asignar cada uno a una unidad diferente. Un buen esquema de particionado debe aprovechar esta característica para mejorar el rendimiento y la disponibilidad de los datos. Algunas recomendaciones son:

• Crear un grupo de archivos por cada partición, y asignar cada grupo a una unidad diferente. De esta forma, se evita la contención de recursos y se aumenta el paralelismo en las operaciones de lectura y escritura. Esto no siempre podremos hacerlo, por ejemplo el particionado es una característica compatible con Azure Databases pero no los distintos grupos de archivos por lo que todos los archivos pertenecerán al mismo Filegroup.
• Crear un grupo de archivos adicional, llamado grupo de archivos primario, que contenga los metadatos de la base de datos y las tablas no particionadas. Este grupo debe estar en una unidad diferente a los demás grupos de archivos.
• Crear un grupo de archivos vacío, llamado grupo de archivos de reserva, que se pueda usar para añadir o mover particiones en caso de necesidad. Este grupo debe estar en una unidad con suficiente espacio libre.

Prueba de concepto

Para esta prueba vamos a usar la tabla [Sales].[SalesOrderHeaderSalesReason] de la base de datos AdventureWorks, esta tabla tiene aproximadamente 31.000 registros que van del año 2011 al 2014. Creando un particionado por la columna OrderDate para tener particiones por año y haciendo una consulta simple a la tabla podemos observar como la reducción en el número de páginas leídas es apreciable.

Os dejo el script que he usado para particionar esta tabla:

CREATE PARTITION FUNCTION [ParticionadoAnual_SalesOrderHeader](datetime) AS RANGE RIGHT FOR VALUES (N'2011-01-01T00:00:00', N'2012-01-01T00:00:00', N'2013-01-01T00:00:00', N'2014-01-01T00:00:00', N'2015-01-01T00:00:00')

CREATE PARTITION SCHEME [EsquemaAnual_SalesOrderHeader] AS PARTITION [ParticionadoAnual_SalesOrderHeader] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

CREATE CLUSTERED INDEX [ClusteredIndex_on_EsquemaAnual_SalesOrderHeader_638450888550526845] ON [Sales].[SalesOrderHeader]
([OrderDate]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [EsquemaAnual_SalesOrderHeader]([OrderDate])

Conclusión

El particionado horizontal de tablas en SQL Server es una técnica muy poderosa para optimizar el manejo de grandes volúmenes de datos. Sin embargo, requiere un análisis previo y un diseño cuidadoso para obtener los mejores resultados. 

Espero que este artículo te haya sido útil y que te ayude a optimizar el rendimiento de tus consultas en SQL Server. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir. ¡Hasta la próxima!

Hoy tenemos un artículo completamente práctico poniendo en práctica y comparando las distintas soluciones de particionado vertical de tablas en SQL Server que vimos ayer en la teoría. ¿Será la mejora de rendimiento tal como la pintan? ¿Merece la pena el esfuerzo? Espero que al concluir la lectura de este artículo puedas sacar tus propias conclusiones y estés capacitado para hacer tus propias pruebas en tu entorno de test para decidir si te interesa o no.

Particionado vertical con vistas

Vamos a empezar con el escalado vertical con vistas indexadas. Para ello tenemos una tabla Personas que es una copia de la tabla Person.Person de AdventureWorks a la que le he añadido una columna varchar(max). He cargado la tabla con 15 veces los registros de la tabla Persons y para la columna varchar(max) he replicado un texto relativamente largo 100 veces por cada registro.

CREATE TABLE [Person].[Personas](
[BusinessEntityID] [int] NOT NULL IDENTITY (1,1),
[PersonType] [nchar](2) NOT NULL,
[NameStyle] [dbo].[NameStyle] NOT NULL,
[Title] [nvarchar](8) NULL,
[FirstName] [dbo].[Name] NOT NULL,
[MiddleName] [dbo].[Name] NULL,
[LastName] [dbo].[Name] NOT NULL,
[Suffix] [nvarchar](10) NULL,
[EmailPromotion] [int] NOT NULL,
[AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
[Demographics] [xml](CONTENT [Person].[IndividualSurveySchemaCollection]) NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[Description] [VARCHAR] (MAX), 
 CONSTRAINT [PK_Personas_BusinessEntityID] PRIMARY KEY CLUSTERED 
([BusinessEntityID] ASC)
)

INSERT INTO [Person].PERSONAS (PersonType,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,EmailPromotion,AdditionalContactInfo,Demographics,rowguid,ModifiedDate,[Description])

SELECT PersonType,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,EmailPromotion,AdditionalContactInfo,Demographics,rowguid,ModifiedDate,REPLICATE ('Humano Supuestamente Inteligente',100) FROM [Person].[Person]

GO 15

Esta es una lectura sobre la tabla:  

Para nuestra prueba de lectura vamos a renunciar a la lectura del campo Description que es el varchar(max), sin embargo el número de páginas de disco leídas sigue siendo el mismo. Probemos a crear una vista indexada sin ese campo y otra solo con ese campo y el ID.

CREATE VIEW VPERSONAS 
WITH SCHEMABINDING AS
SELECT BusinessEntityID,PersonType,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,
EmailPromotion,AdditionalContactInfo,Demographics,rowguid,ModifiedDate
FROM Person.PERSONAS
GO

CREATE UNIQUE CLUSTERED INDEX CI_Personas ON VPERSONAS ( BusinessEntityID,PersonType,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,
EmailPromotion,rowguid,ModifiedDate);
GO

CREATE VIEW VPERSONAS_Descipcion 
WITH SCHEMABINDING AS
SELECT BusinessEntityID,Description
FROM Person.PERSONAS
GO

CREATE UNIQUE CLUSTERED INDEX CI_Personas_Descripcion ON VPERSONAS_Descipcion ( BusinessEntityID);

Y ahora leamos la  primera vista:

Conclusiones:

Como veis, hemos conseguido reducir el número de lecturas para la misma consulta de 295.539 páginas a 57.716, parece que el resultado es bueno. Pero no solo eso, cuando hablamos de las vistas indexadas, comentamos que en las versiones Enterprise de SQL Server, el motor de base de datos era capaz de usar el índice de la vista aunque lo que estamos consultando fuera la tabla así que el resultado para una lectura sobre la tabla será el mismo si tenemos esta edición de SQL Server.

Particionado vertical con tablas

Para este ejemplo vamos a crear una tabla que contenga el ID y Description de la tabla Personas. Luego borraremos Description de la tabla personas y veremos el resultado:

CREATE TABLE [Person].[PersonasDes](
[BusinessEntityID] [int] NOT NULL ,
[Description] [VARCHAR] (MAX), 
 CONSTRAINT [PK_PersonasDes_BusinessEntityID] PRIMARY KEY CLUSTERED 
([BusinessEntityID] ASC)
)
GO
INSERT INTO [Person].[PersonasDes]
SELECT [BusinessEntityID], [Description] FROM Person.Personas
GO
ALTER TABLE  Person.Personas DROP COLUMN [Description] 

Hagamos ahora una lectura de la tabla personas:

¿Qué ha pasado? El número de páginas no se ha reducido. Esto tiene una explicación, el texto de la columna Description no era tan grande como para estar en páginas LOB por lo que al borrarlos simplemente hemos dejado libre el espacio en las páginas de datos pero los datos que permanecen siguen distribuidos de la misma manera. Tenemos una fragmentación muy alta de los datos que solucionaremos con un mantenimiento del índice clustered.

ALTER INDEX PK_Personas_BusinessEntityID ON Person.PERSONAS REBUILD

Y ahora sí una lectura nos mostrará una cantidad muy inferior de páginas leídas:

Conclusiones

En esta ocasión el resultado es incluso mejor que en la opción anterior de particionado vertical con vistas. Esto se debe a que los campos XML de la tabla personas no podemos añadirlos al índice de la vista indexada por lo que SQL tenía que hacer un Key Lookup sobre las páginas de datos de la tabla para recuperar esa información. Además, los datos no están almacenados por duplicado (en el índice clustered de la tabla y en el de la vista). Tiene el inconveniente de que tendremos que modificar el código de las consultas de la aplicación al haber cambiado el modelo de datos pero, si podemos afrontar eso, a cambio ganamos en menor consumo de recursos.

Conclusión final

El particionado vertical es una gran solución para paliar problemas de cuello de botella de E/S de disco así como de RAM y CPU. La opción de particionar con vistas es sencilla de implementar si no tenemos problemas de capacidad de almacenamiento y obtendremos resultados de una manera transparente para los usuarios de la base de datos si tenemos una edición Enterprise. El particionado vertical con tablas es más completo y nos da un mejor rendimiento en caso de tipos de datos que no se admiten en índices pero, por contra, requiere una modificación en el código de nuestras consultas. Nos ha quedado por ver el particionamiento horizontal del que también hablamos en el pasado artículo. Este tipo de particionamiento tiene más cosas que valorar y requiere de un artículo específico. Permanece atento al blog que el próximo día hablaremos de particionado horizontal.

Espero que este artículo te haya sido útil y que te ayude a optimizar el rendimiento de tus consultas en SQL Server. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir. ¡Hasta la próxima!

En este artículo vamos a hablar de un tema muy interesante y útil para los DBAs: el particionado de tablas en SQL Server. El particionado de tablas es una técnica que nos permite dividir una tabla grande en varias partes más pequeñas, llamadas particiones, que se almacenan en diferentes grupos de archivos. De esta forma, podemos mejorar el rendimiento de las consultas, las operaciones de mantenimiento y la administración de los datos.

¿Qué es el particionado de tablas?

El particionado de tablas en SQL Server es una técnica a la que podemos recurrir para dividir nuestras tablas grandes en particiones más pequeñas. Esto normalmente está asociado a un menor coste de recursos al tener que recuperar menos información, además nos abre un poco más el abanico de opciones a la hora de administrar pudiendo jugar con distintos grupos de archivos separados en discos, creando copias de sólo algunos grupos de archivos y haciendo planes de mantenimiento específicos para las particiones.

Tipos de particionado

Podemos encontrarnos con particionados de tipo horizontal y vertical en función de si separamos la tabla por filas o columnas respectivamente. 

Particionado vertical 

Este tipo de particionamiento consiste en dividir una tabla en varias particiones según las columnas que se quieren incluir o excluir. Cada partición contiene solo algunas columnas de la tabla original, pero todas las filas. El particionado vertical se suele usar para separar las columnas que se consultan con frecuencia de las que no, o para almacenar las columnas con tipos de datos especiales (como imágenes, documentos o textos grandes) en grupos de archivos diferentes. Dentro de este tipo de particionamiento podemos encontrar varias soluciones, desde dos tablas independientes con un identificador común para enlazar las columnas hasta una misma tabla con varias vistas indexadas sobre ella cada una con distintas columnas. 

Particionado horizontal

Este tipo de particionamiento es en el que todo el mundo piensa cuando se habla simplemente de particionamiento, sin especificar nada más. Consiste en partir una tabla en varias particiones con exactamente las mismas columnas y repartir las filas entre las distintas particiones en función del valor de una de las columnas. Así por ejemplo una tabla con datos de un log podría estar dividida en particiones por año. 

Ventajas de particionar las tablas

Como ya hemos comentado, si nos metemos en este lio del particionamiento no es por gusto, es porque tiene muchas ventajas frente a las tablas “normales”. Entre estas ventajas podemos destacar:

• Reduce el tiempo de respuesta de las consultas, ya que podemos acceder solo a la partición o particiones que contienen los datos que necesitamos, en lugar de tener que escanear toda la tabla. Esto se conoce como eliminación de particiones o partition pruning.
• Facilita el mantenimiento de los datos, ya que podemos realizar operaciones como reconstruir índices, actualizar estadísticas o realizar copias de seguridad por partición, en lugar de tener que hacerlo sobre toda la tabla. Esto reduce el tiempo y los recursos necesarios para estas tareas.
• Mejora la disponibilidad de los datos, ya que podemos aislar las particiones que contienen datos críticos o de uso frecuente en grupos de archivos dedicados, con un nivel de redundancia y recuperación más alto. También podemos mover o restaurar particiones individuales sin afectar al resto de la tabla.
• Permite una gestión más eficiente del espacio en disco, ya que podemos asignar diferentes niveles de compresión a cada partición, según la frecuencia y el tipo de acceso a los datos. También podemos liberar espacio eliminando o archivando particiones antiguas o poco usadas.

Implementando el particionamiento de tablas 

Los pasos para implementar el particionado de tablas variarán en función del tipo de particionado que queramos usar.

Implementar el particionado horizontal 

1. Crear una función de partición, que define cómo se van a distribuir los datos entre las distintas particiones, según el valor de una columna o expresión. Por ejemplo, podemos crear una función de partición por año, mes o día, según la fecha de registro de los datos.
2. Crear un esquema de partición, que asocia la función de partición con uno o varios grupos de archivos, donde se van a almacenar las particiones. Podemos especificar un grupo de archivos diferente para cada partición, o usar el mismo para todas.
3. Crear o modificar la tabla que queremos particionar, indicando el esquema de partición y la columna o expresión que va a determinar el valor de la partición. Podemos crear una tabla nueva ya particionada, o modificar una tabla existente para aplicarle el particionado.
4. Crear o modificar los índices sobre la tabla particionada, indicando también el esquema de partición y la columna o expresión que va a determinar el valor de la partición. Podemos crear índices alineados con la tabla, es decir, que siguen el mismo esquema y función de partición, o índices no alineados, que tienen su propio esquema y función de partición.

Implementar el particionado vertical 

Como hemos comentado antes para el particionado vertical podríamos optar por crear varias tablas con el mismo ID y distintas columnas en cada una de ellas. Otra opción será sobre una misma tabla con todas las columnas crear vistas indexadas, para ello los pasos serán:

1. Crear vistas sobre la tabla original, seleccionando solo las columnas que queremos incluir en cada partición. Por ejemplo, podemos crear una vista con las columnas básicas y otra con las columnas avanzadas.
2. Crear un índice agrupado sobre cada vista, indicando el grupo de archivos donde se va a almacenar la partición. De esta forma, se crea una copia física de los datos de la vista en el grupo de archivos especificado, y se mantiene sincronizada con la tabla original.
3. Opcionalmente podremos crear índices no agrupados sobre cada vista, indicando también el grupo de archivos donde se va a almacenar la partición. De esta forma, se crea un índice que facilita el acceso a los datos de la vista, y se mantiene sincronizado con la tabla original.

Planificando el particionado de tablas

Ya hemos visto que el particionado de tablas es una herramienta muy poderosa. Sin embargo no hay que tomárselo a la ligera, requiere una planificación y un diseño adecuados para obtener los mejores resultados. Algunas consideraciones importantes son:

• Elegir bien la columna y expresión que va a determinar el valor de la partición, ya que debe ser relevante para las consultas y operaciones más habituales sobre la tabla. Por ejemplo, si queremos consultar los datos por fecha, tiene sentido usar una columna que contenga la fecha como criterio de particionado.
• Elegir bien el número y el tamaño de las particiones, ya que deben ser equilibradas y coherentes con el volumen y la distribución de los datos. Por ejemplo, si tenemos una tabla con muchos años de datos históricos, pero solo consultamos los datos del último año, no tiene sentido crear una partición por cada año, sino quizás una por cada mes o trimestre.
• Elegir bien los grupos de archivos donde se van a almacenar las particiones, ya que deben tener suficiente espacio y capacidad para soportar la carga de trabajo. Por ejemplo, si tenemos una partición con datos muy consultados o críticos, podemos ubicarla en un grupo de archivos con discos más rápidos o con mayor redundancia.
• Monitorizar y ajustar el particionado de tablas periódicamente, ya que los datos y las necesidades pueden cambiar con el tiempo. Por ejemplo, podemos usar herramientas como el asistente para el particionado de tablas o query store para analizar el rendimiento y la eficacia del particionado, y realizar cambios como agregar, eliminar, fusionar o dividir particiones, según sea necesario.

Conclusión

El particionado de tablas es una técnica muy útil para mejorar el rendimiento, el mantenimiento y la administración de las bases de datos en SQL Server. Sin embargo, requiere un diseño cuidadoso y un seguimiento constante para obtener los mejores resultados. En este artículo hemos visto los conceptos básicos del particionado horizontal y vertical de tablas, cómo se implementan en SQL Server y qué consideraciones hay que tener en cuenta al usarlos. Si quieres profundizar más en este tema, te recomendamos que leas nuestro anterior artículo sobre cuellos de botella de E/S en SQL Server, donde explicamos cómo el particionado de tablas puede ayudar a resolver este problema común.

Esperamos que te haya gustado este artículo y que te haya servido para aprender algo nuevo. Si tienes alguna duda o comentario, no dudes en dejarnos un mensaje en Twitter, por mail o dejarnos en los comentarios. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir.