Una de las variables más críticas para el rendimiento de la base de datos es la fragmentación de los índices SQL. Podemos enfrentarnos a bloqueos, deadlocks, problemas de IO, problemas de Disk spill si el nivel de fragmentación no se gestiona adecuadamente. También puede conducir a un plan de ejecución inadecuado.
DMVs
En SQL Server, podemos utilizar el DMV sys.dm_db_index_physical_stats para obtener la información sobre el nivel de fragmentación del índice, los recuentos de páginas, y los tipos de índice utilizando esto.
SSMS
También podemos utilizar los informes incorporados de SQL Server ‘Index Physical Statistics’ en SSMS
Obtenemos las estadísticas físicas del índice para la base de datos en el siguiente formato.
Estamos limitados a ejecutar este informe a nivel de base de datos, y si tenemos múltiples bases de datos en la instancia y muchas instancias para gestionar, se convierte en tiempo y recursos que consumen la tarea de recoger los detalles, analizar el estado basado en nuestro estado de umbral y luego decidir sobre la reconstrucción o reorganizar el índice SQL.
Scripts personalizados
También podemos utilizar scripts personalizados para detectar y solucionar los problemas de fragmentación, pero también es un proceso complicado establecer y analizar los resultados manualmente. Es posible que queramos tener toda esta información en un lugar para centralizarla y luego decidir la política de mantenimiento del índice. Podemos utilizar los comandos Alter Index para eliminar el nivel de fragmentación. Para las bases de datos pequeñas, no nos importan mucho los recuentos de páginas mientras reconstruimos el índice, sin embargo, para las bases de datos grandes también debemos tenerlos en cuenta.
Herramientas de terceros
Podemos supervisar, analizar y eliminar fácilmente la fragmentación del índice SQL existente utilizando ApexSQL Defrag. Podemos gestionar múltiples instancias y bases de datos en una herramienta gráfica. Obtenemos los siguientes beneficios de esta herramienta.
- Proporciona una interfaz gráfica de usuario para supervisar y analizar la fragmentación del índice
- Podemos hacer el mantenimiento del índice para todas las instancias y bases de datos de SQL Server
- Proporciona la forma de configurar los informes personalizados basados en nuestro requisito
- Podemos configurar las políticas para comprobar y realizar las tareas de mantenimiento del índice según las métricas personalizadas
- Podemos analizar (rápido o profundo) la fragmentación del índice, establecer el factor de llenado, el mantenimiento del índice en línea
- Podemos preparar los informes en los formatos deseados (CSV o HTML)
Una vez que instale la herramienta ApexSQL Defrag y configurar una instancia para monitorear, muestra los detalles SQL índice estado de fragmentación rápidamente.
Debemos evitar las operaciones de reconstrucción o reorganización de índices SQL si no son necesarias. Si tenemos índices muy grandes, entonces podría tomar mucho tiempo y más recursos del sistema para analizar. ApexSQL Defrag da la opción de comprobar la fragmentación.
- Limitada: En este modo de análisis, SQL Server recoge información limitada sobre la fragmentación. No causa ningún problema de rendimiento al sistema. Es el modo por defecto en ApexSQL Defrag
- Muestreado: En este modo, toma una porción de muestra del índice SQL y recoge la información de fragmentación
- Detallada: Obtenemos el conocimiento detallado de todas las páginas del índice; podría causar cierta sobrecarga a los recursos del sistema sin embargo, podemos utilizarlo si observamos la alta fragmentación con los modos de escaneo anteriores
Del mismo modo, podemos establecer el nivel de fragmentación en la categoría Alta, Media y Baja.
También puede establecer su umbral de fragmentación ganado para analizar los índices.
Una vez analizado el conjunto de resultados, haga clic con el botón derecho del ratón en el índice SQL particular y puede tener múltiples opciones para elegir.
- Analizar (rápido)
- Analizar (profundo)
- Reorganizar\Reconstruir\Reconstruir en línea
- Establecer factor de llenado
- Podemos pausar, detener o cancelar si hay algún trabajo en ejecución para ese índice. Es útil si vemos algún problema de rendimiento o bloqueo debido a un índice en particular, podemos cancelar ese trabajo en sí
ApexSQL Defrag permite crear las políticas desde las plantillas predefinidas o por los parámetros personalizados. La plantilla también es adecuada para la mayoría de los requisitos de desfragmentación de índices de SQL.
Utilizando estas plantillas, podemos decidir desfragmentar todos los índices o el índice basado en el nivel de umbral en modo offline u online. Por ejemplo, quiero crear una política para reconstruir en línea el 50% de los índices más fragmentados por encima del 30% o reorganizarlos si están fragmentados por encima del 10%.
También podemos configurar el umbral de recursos con respecto a la CPU, la carga de memoria, el uso del registro de transacciones y las transacciones activas. No tenemos este nivel de control en el mantenimiento de índices utilizando los trabajos del asistente de mantenimiento o los scripts personalizados.
Si el recurso está ocupado, podemos configurar el tiempo de reintento también. He visto los requisitos en algunos usuarios que no quieren ejecutar el mantenimiento después de su período de uso de la base de datos magra es más. Si estamos haciendo el mantenimiento, tenemos que detener el trabajo manualmente, o tenemos que crear otro trabajo de agente de SQL para detener el trabajo de mantenimiento de índice en un momento determinado.
ApexSQL Defrag proporciona la sobrecarga para configurar y supervisar múltiples trabajos. Podemos personalizar para cancelar el trabajo de mantenimiento del índice después de ‘N’ número de horas.
En la captura de pantalla anterior, se puede ver que podemos personalizar aún más la operación de mantenimiento del índice de SQL por el nivel de fragmentación, Tamaño del índice y los recuentos de página también. Se puede definir el retraso del objeto bloqueado por un tiempo determinado. A veces queremos excluir el índice donde tenemos recuentos de páginas de índice más pequeños o más grandes que los recuentos especificados.
Puedes hacer los cambios y configurarlos como una plantilla para reutilizarlos. Una vez que hemos realizado el mantenimiento del índice, en la pestaña Actividades, se obtiene una excelente visión general de las estadísticas de fragmentación del índice antes y después del mantenimiento. Es esencial saber si el nivel de fragmentación del índice está bajo control o no después del mantenimiento. Normalmente hay que volver a capturar el nivel de fragmentación, pero nos ahorra la sobrecarga con esta información.
En la sección Informes, podemos ver los informes a nivel de servidor, informe global.
También podemos obtener una buena vista de los 10 principales índices clusterizados y no clusterizados.
También nos interesa conocer el comportamiento de la CPU y de la Memoria durante el mantenimiento de los índices en ejecución. Podemos obtener esta útil información y vigilar también los recursos del sistema.
Podemos exportar todos los informes y configurar la opción de exportación. Es posible que queramos que se extraiga información limitada mientras se explora. ApexSQL Defrag le da la opción de elegir qué información para mostrar como gráficos, estadísticas, gráficos de estadísticas de índice de SQL que queremos incluir en el informe.
Conclusión:
ApexSQL Defrag herramienta hace que sea fácil para los DBA para hacer la tarea administrativa con un mejor índice de SQL monitoreo y mantenimiento. Usted obtiene toda la información es una herramienta gráfica con visuales personalizados, políticas junto con varios informes informativos.
