Particionado de tablas en SQL Server (Introducción)

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.