Exploración de las comprobaciones de mantenimiento de la base de datos
El optimizador de consultas usa información estadística de los índices para intentar crear el plan de ejecución más óptimo.
Dentro de Azure SQL, usted controla las tareas de mantenimiento, como las copias de seguridad y las comprobaciones de integridad, y aunque puede haber actualizaciones automáticas que mantienen sus estadísticas actualizadas, a veces no es suficiente.
Tener índices y estadísticas correctas garantizará que cualquier plan determinado tenga una eficacia óptima. El mantenimiento de un índice debe realizarse con regularidad a medida que los datos de las bases de datos cambian, con el paso de tiempo. Puede cambiar la estrategia de mantenimiento de índices en función de la frecuencia de las modificaciones realizadas en los datos.
Recompilación y reorganización
La fragmentación de un índice se produce cuando la ordenación lógica dentro de las páginas del índice no coincide con la ordenación física. Las páginas pueden no estar ordenadas durante las instrucciones de modificación de datos de rutina, como UPDATE, DELETE y INSERT. La fragmentación puede producir problemas de rendimiento debido a la E/S adicional necesaria para localizar los datos a los que hacen referencia los punteros dentro de las páginas de índice.
A medida que los datos se insertan, actualizan y eliminan de los índices, la ordenación lógica de los índices ya no coincidirá con la ordenación física dentro de las páginas, y entre las páginas, que conforman los índices. Además, con el tiempo, las modificaciones de datos pueden hacer que los datos se dispersen o fragmenten en la base de datos. La fragmentación puede reducir el rendimiento de las consultas cuando el motor de la base de datos tenga que leer páginas adicionales para buscar los datos necesarios.
Una reorganización de un índice es una operación en línea que desfragmentará el nivel de hoja del índice (agrupado y no agrupado). Este proceso de desfragmentación reordenará físicamente las páginas de nivel de hoja para que coincidan con el orden lógico de los nodos, de izquierda a derecha. Durante este proceso, las páginas de índice también se compactan en función del valor de fillfactor configurado.
Una recompilación puede realizarse en línea o sin conexión, según el comando ejecutado o la edición de SQL Server que se está usando. Un proceso de regeneración sin conexión eliminará y volverá a crear el propio índice. Si puede hacerlo en línea, se creará un nuevo índice en paralelo al índice existente. Una vez creado el nuevo índice, se eliminará el existente y, después, se cambiará el nombre del nuevo para que coincida con el nombre del índice anterior. Tenga en cuenta que la versión en línea requerirá espacio adicional, ya que el nuevo índice se crea en paralelo al índice existente.
La guía común para el mantenimiento de índices es la siguiente:
> 5 % pero < 30 %: reorganizar el índice
>30 %: recompilar el índice
Use estos números como recomendaciones generales. En función de la carga de trabajo y los datos, es posible que deba ser más autoritario, en algunos casos, puede aplazar el mantenimiento de índices para las bases de datos que realizan principalmente consultas que buscan páginas específicas.
Las plataformas SQL Server y Azure SQL ofrecen DMV que le permiten detectar fragmentación en sus objetos. Los DMV más usados para este propósito son sys.dm_db_index_physical_stats para índices de árbol b y sys.dm_db_column_store_row_group_physical_stats para índices de almacén de columnas.
Otro aspecto que hay que tener en cuenta es que las reconstrucciones de índices provocan que se actualicen las estadísticas del índice, lo que puede ayudar a mejorar el rendimiento. La reorganización del índice no actualiza las estadísticas.
Microsoft introdujo operaciones de índice de recompilación reanudables con SQL Server 2017. La opción de operaciones de índice de recompilación reanudable proporciona más flexibilidad para controlar cuánto tiempo puede imponer una operación de recompilación en una instancia determinada. Con SQL Server 2019, se incorporó la capacidad de controlar un grado máximo de paralelismo asociado, proporcionando a la vez un control más detallado a los administradores de bases de datos.
Estadísticas
Al optimizar el rendimiento en Azure SQL, es fundamental comprender la importancia de las estadísticas.
Las estadísticas se almacenan en la base de datos de usuario como objetos binarios grandes (blobs). Estos blobs contienen información estadística acerca de la distribución de valores de datos en una o más columnas de una tabla o vista indizada.
Las estadísticas contienen información sobre la distribución de valores de datos dentro de una columna. El optimizador de consultas utiliza estadísticas de columnas e índices para determinar la cardinalidad, que es el número de filas que se espera que devuelva una consulta.
Posteriormente, el optimizador de consultas utiliza las estimaciones de cardinalidad para generar el plan de ejecución. Las estimaciones de cardinalidad también ayudan al optimizador a determinar qué tipo de operación (por ejemplo, la exploración o búsqueda de índices) debe usarse para recuperar los datos solicitados.
Para ver la lista de estadísticas definidas por el usuario con la última fecha actualizada, ejecute la consulta siguiente:
SELECT sp.stats_id,
name,
last_updated,
rows,
rows_sampled
FROM sys.stats
CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id) AS sp
WHERE user_created = 1
Creación de estadísticas
Si tiene la opción AUTO_CREATE_STATISTICS de ON, el optimizador de consultas crea estadísticas en la columna indizada de manera predeterminada. El optimizador de consultas también crea estadísticas para columnas individuales en predicados de consulta.
Estos métodos proporcionan planes de consulta de alta calidad para la mayoría de las consultas. En ocasiones, es posible que tenga que crear más estadísticas mediante la instrucción CREATE STATISTICS para mejorar planes de consulta específicos.
Se recomienda mantener habilitada la opción AUTO_CREATE_STATISTICS, ya que permitirá al optimizador de consultas crear estadísticas para las columnas de predicado de consulta automáticamente.
Siempre que encuentre las siguientes situaciones, considere la posibilidad de crear estadísticas:
- El Asistente para la optimización de motor de base de datos sugiere crear las estadísticas
- El predicado de consulta contiene varias columnas que aún no están en el mismo índice
- La consulta realiza la selección entre un subconjunto de datos
- La consulta ha perdido estadísticas
Automatización de tareas de mantenimiento
Azure SQL proporciona herramientas nativas para realizar tareas de mantenimiento de bases de datos con fines de automatización. Hay diferentes herramientas disponibles en función de la plataforma en la que se ejecuta la base de datos.
SQL Server en una máquina virtual de Azure
Tiene acceso a servicios de programación como el Agente SQL o el Programador de tareas de Windows. Estas herramientas de automatización pueden ayudar a mantener la cantidad de fragmentación dentro de los índices a un nivel mínimo. Con las bases de datos de mayor tamaño, debe encontrarse un equilibrio entre recompilar y una reorganización de índices para garantizar un rendimiento óptimo. La flexibilidad proporcionada por el Agente SQL o el Programador de tareas permite ejecutar trabajos personalizados.
Azure SQL Database
Debido a la naturaleza de Azure SQL Database, no tiene acceso a Agente SQL Server ni al Programador de tareas de Windows. Sin estos servicios, se debe crear el mantenimiento de índices mediante otros métodos. Hay tres maneras de administrar las operaciones de mantenimiento para SQL Database:
Runbooks de Azure Automation
Trabajo del Agente SQL desde SQL Server en una máquina virtual de Azure (llamada remota)
Trabajos elásticos de Azure SQL
Instancia administrada de Azure SQL
Al igual que con SQL Server en una máquina virtual de Azure, puede programar trabajos en Azure SQL Managed Instance mediante el Agente SQL Server. El uso del Agente SQL Server proporciona flexibilidad para ejecutar el código diseñado para reducir la fragmentación dentro de los índices de la base de datos.