Prashanth Jayaram

Mejoras de SQL Server 2016 – Truncar tablas y particiones en tablas

June 6, 2017 by

La idea de este artículo es discutir la importancia y lo que implica la partición SQL y comprender las mejoras del comando truncate para el particionamiento en SQL 2016

Uno de los principales retos para un administrador de base de datos es identificar al candidato adecuado para realizar el particionamiento de tablas, ya que requiere experiencia en diseño e implementación.

Las siguientes son las varias opciones de truncamiento disponibles en SQL 2016

  • Truncar particiones de manera individual
  • Truncar varias particiones individuales
  • Truncar un rango de particiones
  • Truncar un intervalo con varias particiones individuales

Secciones importantes del artículo

  • Establecer la importancia y la implicación de la partición de tabla SQL
  • Identificar al candidato adecuado para particionar tablas
  • Proporcionar una comparación en línea de las características disponibles en las diferentes ediciones de SQL 2016
  • Discutir las mejoras de la partición truncada en SQL 2016
  • Demostrar los casos de uso de particiones de tablas truncadas

Partición SQL

Entendamos primeramente el objetivo del particionamiento en SQL, por qué es necesario particionamiento y los factores que son vitales para poder decidir una estrategia de partición de tablas.

Las particiones son una asignación lógica de los datos físicos. Una división con un buen diseño, nos permite escalar los datos. Optimiza el rendimiento y simplifica la gestión de datos mediante la partición de cada tabla utilizando múltiples particiones independientes. Sin embargo, no todas las tablas son buenas candidatas para poder realizar una partición. Si la respuesta es “sí” a todas o a la mayoría de las siguientes preguntas, la partición de tablas puede ser una estrategia de diseño de base de datos viable; Si la respuesta es “no” a la mayoría de las preguntas siguientes, el particionamiento de tablas puede no ser la solución correcta para esa tabla.

  • ¿Es la tabla suficientemente grande?
    Las tablas grandes de hechos son buenas candidatas para realizar la partición de tablas. Si tenemos millones o miles de millones de registros en una tabla, podemos ver los beneficios de rendimiento al partir los datos en particiones lógicamente más pequeños. Dado que las tablas más pequeñas son menos susceptibles a problemas de rendimiento, la sobrecarga administrativa de mantenimiento de las particiones será superior a cualquier beneficio de rendimiento que podríamos ver mediante el particionamiento.
  • ¿Su aplicación o sistema mantiene una ventana de datos históricos?
    Otra consideración para el diseño de particiones es la directiva de retención de datos de la organización. Como ejemplo, su almacén de datos puede requerir la retención datos de los últimos doce meses. Si los datos se dividen por mes, se puede eliminar de manera sencilla la partición mensual más antigua del almacén y cargar datos actuales en la partición mensual más nueva.
  • ¿Se está experimentando problemas de rendimiento en tareas de mantenimiento de bases de datos?
    Cuando trabajamos con tablas más grandes, es más complicado realizar las operaciones de reconstrucción. En estos casos, se puede confiar en el particionamiento de tablas, de modo que las operaciones de mantenimiento se puedan realizar sin problemas.
  • ¿Pueden dividirse los datos en partes iguales en base a ciertos criterios?
    Elija los criterios de partición que dividirán los datos de la manera más uniforme posible. Esto permitirá que el Optimizador de consultas SQL decida y seleccione el mejor plan para la ejecución de consultas. La partición mensual que se menciona en el segundo punto es un buen ejemplo de esto.
  • ¿Es la concurrencia un problema?
    ¿El sistema implica el manejo de un gran volumen de carga de datos e informes? ¿Se bloquean frecuentemente las consultas de los usuarios? Si es así, el particionamiento puede ser una opción para aliviar la situación, debido a que el optimizador de consultas sería capaz de manejar de mejor manera la ejecución.

Si alguien alguna persona realiza un reclamo respecto a la lentitud de una consulta, no es necesario que las tablas relacionadas sean particionadas. En muchos casos, el particionamiento no mejoraría el rendimiento. Una buena estrategia de indexación suele ser suficiente para la mayoría de los problemas de rendimiento de varios de estos casos de uso. Por ejemplo, si se realiza una consulta que utiliza una clave de partición, la ejecución de la consulta mejora; un deterioramiento en el rendimiento puede deberse a que no se utiliza la clave de partición en dicho escenario.

En las versiones anteriores de SQL Server, se requería de mucho esfuerzo adicional para poder restaurar la base de datos con tablas particionadas cuando se trataba de una edición no corporativa de SQL Server. Cualquier intento de restaurar resultaba en la caída de la función de partición y el esquema de partición antes del proceso de copia de seguridad y restauración de la base de datos. Esto no sucede en SQL 2016.

Las operaciones de truncamiento/supresión/reestructuración de datos tuvieron que basarse en el mecanismo de conmutación de particiones y de fusión. Sin embargo, con SQL Server 2016, se han introducido algunas características interesantes en el comando truncate, que sirven para poder eliminar los datos con un uso mínimo del log.

Soporte de funciones

La función de partición ha sido una característica de la edición Enterprise desde SQL 2005, pero por primera vez, se ha puesto a disposición en todas las ediciones desde SQL 2016.

Característica Empresa Estándar Web Express with servicios avanzados Express
Tabla y
Particionamiento de índices
Si Si Si  Si  Si 

Sintaxis para truncar una tabla y como utilizarla

TRUNCATE TABLE y la opción WITH PARTITIONS() permite truncar los datos dentro del (de los) número(s) de partición definido(s) o un dentro de un rango de particiones.

  • Proporcione el número de partición, por ejemplo:
  • Proporcione los números de partición para varias particiones individuales separadas por comas, por ejemplo:
  • Proporcione tantos rangos como múltiples particiones individuales, por ejemplo:
  • Utilizando la palabra TO, los números de partición pueden estar separados por la palabra TO, por ejemplo:

¿Cómo y cuándo utilizar el comando truncate table con cláusula de partición?

La siguiente sección describe en qué escenario utilizar la opción Truncate Table para la eliminación de datos de una tabla particionada

El proceso de archivado/purga rápido implica el realizar el cambio de las particiones. Aunque Switch Partition es una operación de actualización de metadatos y no implica movimiento de datos físicos entre los archivos de datos existen casos como bases de datos transaccionales de alto tráfico que requieren bloqueos exclusivos, lo que hace bloquear la tabla para cargar y eliminar los datos.

Realizar la purga de datos (supresión) de particiones o particiones específicas era una tarea tediosa en versiones anteriores de SQL Server. Los datos creaban retrasos y bloqueaban la tabla, lo que evitaba que los usuarios consultaran la tabla; También se terminaba usando mucho espacio en el registro de transacciones. Antes de SQL 2016, se realizaron los siguientes pasos para poder eliminar los datos o archivos de la partición

  • Crear una nueva tabla para modificar con la misma definición y el índice agrupado como de la tabla particionada
  • Cambiar la partición a la otra tabla
  • Modificar la función de partición y el esquema de particiones para eliminar el grupo de archivos
  • Fusionando de acuerdo a los límites
  • Eliminar el grupo de archivos

Nota: Hacer estos pasos es todo un reto cuando trabajamos con un gran número de filas

¿Qué tal introducir el nuevo comando TRUNCATE TABLE en este escenario?

Se trata de una forma muy sencilla y eficiente de eliminar las filas de la tabla particionada, ya que funciona como una operación normal para truncar una tabla.

  • Ejecutar el comando TRUNCATE TABLE con cláusula de partición
  • Ejecutar el comando MERGE
  • Eliminar el grupo de archivos vacío

Demostración

En SQL Server 2016 se tiene la nueva característica para truncar datos a nivel de partición. Es bastante sencillo y simple. Vamos a mostrar los sencillos pasos para ver cómo funciona.

Cree una base de datos de ejemplo llamada powerSQLPartitionTest y agregue nuevos grupos de archivos. Estos archivos son una representación física de datos SQL:

The below SQL gives SQL partition internal details

Ahora, filegroup_2017 está vacío y listo para ser eliminado. Asegúrese de que la dependencia han sido eliminadas en cada sección que las utilizaba. Una vez que se haga esto, unir en el punto de límite eliminará la entrada de la función de partición:

Conclusión

En el presente artículo, se detalla el uso de particiones en SQL Server y que factores son de vital importancia al considerar antes de poder particionar una tabla. También se describe el uso de las mejoras del comando truncate table con particiones. Con SQL Server 2016, se puede planificar un mejor mantenimiento de índices y estrategias para realizar la administración de datos.

Referencias


Prashanth Jayaram

Prashanth Jayaram

Soy un experto en tecnologías con más de 11 años de experiencia en tecnologías de base de datos. Soy Microsoft Certified Professional y tengo el respaldo de una Licenciatura en Master en aplicaciones de computadoras.

Mi especialidad es el diseño y la implementación de soluciones de alta disponibilidad y la migración de bases de datos multiplataforma. Las tecnologías en las que trabajo actualmente son SQL Server, PowerShell, Oracle y MongoDB.

Ver todas las publicaciones de Prashanth Jayaram
Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

217 Views