Free Press

El objetivo de la creación de índices SQL en las tablas de la base de datos es mejorar el rendimiento de las consultas que leen datos de la tabla base acelerando los procesos de recuperación de datos. Pero estas tablas transaccionales no son estáticas y cambian con mucha frecuencia en el tiempo. Estos cambios que se realizan en la tabla de la base de datos incluyen la adición de nuevos registros, la modificación o la eliminación de los registros existentes. Como estos cambios deben ser replicados a los índices de la tabla relacionados, el índice de la tabla se fragmentará con el tiempo.

Resumen de la fragmentación del índice de SQL Server

Hay dos tipos principales de fragmentación del índice de SQL; Fragmentación interna y externa. La Fragmentación Interna es causada por la inserción de un nuevo registro o la actualización de un registro existente con valores que no caben en el espacio libre actual en la página de datos y el resultado con la división de la página en dos páginas para encajar el nuevo valor. En este caso, el motor de SQL Server moverá alrededor del 50% de los datos de la página actual a la nueva página de datos que se genera a partir de la operación de división, con el fin de mantener el árbol de índices SQL equilibrado. La fragmentación interna puede ser causada también por las operaciones de borrado aleatorias que resultan en espacio vacío en las páginas de datos. Este tipo de operaciones deja las páginas de datos sin rellenar y da como resultado un mayor tamaño del índice de SQL Server y un rendimiento más lento debido a la necesidad de leer más páginas de datos para recuperar los datos solicitados.

La fragmentación externa se produce cuando las páginas de datos del índice de SQL Server están dispersas en el archivo físico de la base de datos, debido a que el orden lógico del índice de SQL Server no coincide con el orden físico en la unidad de disco subyacente. La fragmentación externa puede ser causada por operaciones de borrado aleatorias que dejan un número de páginas de extensión vacías mientras tienen la extensión misma reservada. Este tipo de fragmentación provoca una degradación del rendimiento, ya que tardará más tiempo y consumirá más recursos en saltar entre las páginas de datos dispersas para recuperar los datos solicitados de la unidad de disco subyacente.

La razón por la que nos preocupamos mucho por la fragmentación del índice SQL es que el índice que ha creado para mejorar el rendimiento del sistema, puede degradar el rendimiento de sus consultas después de estar muy fragmentado. Imagínese la diferencia de tiempo y número de lecturas de disco entre la lectura de un número específico de filas de un índice SQL saludable en el que los datos se almacenan en páginas de datos contiguas dentro del archivo de base de datos, y la lectura del mismo número de filas de un índice de SQL Server altamente fragmentado, en el que los datos se encuentran dispersos en páginas de datos no contiguas dentro del archivo de base de datos.

Si se encuentra que el índice SQL de la tabla está fragmentado, es necesario arreglar el problema de fragmentación mediante la realización de la Reorganización del Índice de SQL Server o la Reconstrucción del Índice, basado en el porcentaje de fragmentación del índice SQL. Por ejemplo, un índice de SQL Server con un porcentaje de fragmentación inferior al 30% se puede reorganizar, mientras que el índice de SQL con un nivel de fragmentación superior al 30% debe reconstruirse.

• Nota: Consulte Mantenimiento de los índices de SQL Server para obtener más información.

Puede superar de forma proactiva los problemas de fragmentación de índices de SQL Server y de división de páginas diseñando el índice de forma adecuada y estableciendo las opciones de creación de índices Fill Factor y pad_index con los valores propios.

• Nota: Consulte Operaciones de índices de SQL Server para obtener más información.

Informe de fragmentación de índices de SQL Server

No se considera una buena práctica crear simplemente un índice SQL en su tabla de base de datos para aprovechar la mejora de la operación de recuperación de datos y dejar ese índice de SQL Server para siempre sin supervisar continuamente su fragmentación y mantenerlo.

SQL Server nos proporciona varios métodos que se pueden utilizar para comprobar el porcentaje de fragmentación del índice SQL.

Propiedades del índice de SQL Server

El primer método es la página de fragmentación de la ventana de propiedades del índice de SQL Server. Haga clic con el botón derecho del ratón en el índice que necesita comprobar y elija la opción Propiedades. Desde la página de Propiedades del Índice de SQL Server, navegue por la página de Fragmentación, y verá el porcentaje de fragmentación del índice y cuánto se llenan las páginas del índice de SQL Server bajo la sección de Fragmentación, como se muestra a continuación:

En unos pocos pasos, puede comprobar fácilmente el porcentaje de fragmentación del índice de SQL Server del índice seleccionado desde la ventana de propiedades del índice. Pero, ¿y si necesita tener una visión general del porcentaje de fragmentación de todos los índices de la tabla o de todas las tablas de la base de datos? En este caso, hay que ir a comprobarlo uno por uno. Otro método que se puede utilizar para comprobar el porcentaje de fragmentación de los índices de la base de datos es consultar la función de gestión dinámica sys.dm_db_index_physical_stats, que se introdujo por primera vez en SQL Server 2005, en sustitución del comando obsoleto DBCC SHOWCONTIG. Proporciona información sobre el tamaño y el porcentaje de fragmentación de los índices de la base de datos.

Para obtener información significativa sobre el porcentaje de fragmentación de todos los índices bajo la base de datos especificada desde el DMF sys.dm_db_index_physical_stats, es necesario unirlo con el DMV sys.indexes DMV, como en el script T-SQL que se muestra a continuación:

SELECT OBJECT_NAME(Index_Info.OBJECT_ID) AS TableName ,Index_Info.name AS IndexName ,Index_Stat.index_type_desc AS IndexType ,Index_Stat.avg_fragmentation_in_percent IndexFragmPercentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Index_StatINNER JOIN sys.indexes Index_Info ON Index_Info.object_id = Index_Stat.object_id AND Index_Info.index_id = Index_Stat.index_idORDER BY IndexFragmPercent DESC

En función de tus conocimientos de desarrollo SQL, puedes realizar el filtrado que necesites para recuperar la fragmentación de los índices SQL en el ámbito requerido, sin opción de obtener un resumen para el conjunto de las bases de datos de la instancia, teniendo en cuenta que los datos mostrados son desde el último reinicio del servicio SQL Server. El resultado en nuestro caso será como:

Informe estándar de estadísticas físicas de índices de SQL Server

SQL Server nos proporciona varios informes estándar que contienen información estadística útil sobre la base de datos seleccionada. El informe Index Physical Statistics, es uno de los informes estándar de SQL Server que devuelve información estadística sobre las particiones de índice de SQL Server, el porcentaje de fragmentación y el número de páginas en cada partición de índice de SQL a nivel de base de datos, desde el último reinicio del servicio de SQL Server, con la recomendación de cómo solucionar el problema de fragmentación del índice, si existe, en función del porcentaje de fragmentación del índice de SQL Server.

Para ver el informe de estadísticas físicas de índices de SQL Server, haga clic con el botón derecho en su base de datos, elija Informes -> Informes estándar y, a continuación, seleccione el informe de estadísticas físicas de índices, como se muestra a continuación:

Y el informe generado a nivel de la base de datos, que no puede filtrarse y necesita expandir manualmente el resultado de cada índice de tabla, será como se muestra a continuación:

ApexSQL Defrag

La herramienta ApexSQL Defrag es una herramienta de desfragmentación de índices de SQL de terceros que se puede utilizar fácilmente para comprobar la fragmentación de índices de SQL Server y la información de uso y realizar o programar la corrección adecuada para el problema de fragmentación de índices.

Desde una ubicación central, puede analizar y revisar la información de fragmentación y uso a diferentes niveles, incluyendo la información de fragmentación y uso de índices SQL a nivel de índice de SQL Server, a nivel de tabla, a nivel de base de datos y finalmente a nivel de instancia general de SQL Server. Nos proporciona varios tipos de filtros que se pueden utilizar para mostrar y mantener sólo sus índices de destino.

ApexSQL Defrag nos permite también configurar el ajuste del Factor de Relleno para los índices de la tabla, crear alertas para todos los trabajos y políticas de desfragmentación y enviar notificaciones por correo electrónico para los trabajos exitosos o fallidos.

La información mostrada proporcionará una buena vista sobre el tamaño del índice de SQL y el porcentaje de fragmentación, que no se verá afectada por cualquier reinicio del servicio de SQL Server o reinicio del servidor. Esto se debe a que esta información se almacenará en una base de datos de repositorio central para la herramienta ApexSQL Defrag.

Instalación de ApexSQL Defrag

ApexSQL Defrag puede descargarse fácilmente desde el Centro de descargas de ApexSQL e instalarse en su servidor siguiendo el sencillo asistente de instalación, como se muestra a continuación:

En primer lugar, se le pedirá que especifique la cuenta de servicio que se utilizará como contexto de ejecución para el agente de ApexSQL Defrag y que transmite los comandos a la instancia de SQL Server, de la siguiente manera:

Después de especificar la cuenta de servicio de ApexSQL Defrag, se le pedirá que especifique dónde instalar la herramienta ApexSQL Defrag y si desea crear un icono de acceso directo para la herramienta en el escritorio, como se muestra a continuación:

Al comprobar que la ruta proporcionada contiene un mínimo de 127 MB de espacio libre, ApexSQL Defrag se instalará correctamente en su servidor. Y se mostrará la siguiente notificación, ofreciéndole la posibilidad de iniciar ApexSQL Defrag directamente:

Si ejecuta ApexSQL Defrag por primera vez, se le pedirá que confirme la creación de la base de datos del repositorio central, donde se almacenarán estos datos históricos y de configuración, como sigue:

Agregar nuevo servidor

Tras crear la base de datos del repositorio, ApexSQL Defrag se iniciará sin instancia SQL. Para comprobar el porcentaje de fragmentación del índice SQL en una instancia específica de SQL Server, es necesario añadir dicha instancia a la herramienta. Para ello, haga clic en el botón Añadir, bajo la pestaña Inicio, como se muestra a continuación:

En la ventana Conectar a SQL Server, se le pedirá que proporcione el nombre de la instancia de SQL Server que se utilizará y las credenciales que se requieren para conectarse a ese servidor, como sigue:

Análisis de Índices del Servidor SQL

Cuando se añade el servidor, se puede realizar una nueva comprobación de toda la información de fragmentación de los índices SQL desde la pestaña Fragmentación que aparece a continuación:

ApexSQL Defrag le ofrece la posibilidad de analizar todos los índices de esa instancia basándose en tres modos:

• DETALLADO: en el que se leerán todas las páginas de datos e índices SQL durante el proceso de análisis
• SAMPLIFICADO: en el que sólo se leerá el 1% de las páginas, teniendo en cuenta que el índice de SQL Server contiene más de 10.000 páginas
• LIMITADO: en el que sólo se leerán las páginas situadas en el nivel padre del árbol b

Para analizar la información del índice de SQL Server de la instancia de SQL Server conectada, pulsamos el botón Analizar, en la pestaña Fragmentación, y elegimos realizar un análisis Rápido o Profundo. Realizaremos un análisis profundo para obtener información completa, como se muestra a continuación:

Siga supervisando la barra de progreso en la parte inferior de la herramienta para comprobar el progreso del análisis, como se muestra a continuación:

Y se mostrará un informe completo, que contiene información completa sobre todos los índices SQL creados en cada base de datos de esta instancia de SQL Server, como se muestra a continuación:

Con la capacidad de bucear entre los diferentes niveles; instancia de SQL Server, base de datos, tabla o índice, con un solo clic bajo la misma ventana central, y filtrar el resultado recuperado en base a un nivel o umbral de fragmentación específico, como se muestra a continuación:

Además, podemos arrastrar cualquier columna de las columnas del informe para agrupar el informe global en base a los valores de esa columna, como el ejemplo siguiente, en el que agrupamos los datos del informe en base al nombre de la Tabla:

Informes de ApexSQL Defrag

ApexSQL Defrag nos proporciona una serie de informes que muestran información estadística sobre los índices de la base de datos. Estos informes pueden configurarse y comprobarse en la pestaña Informes que aparece a continuación:

El primer informe es el informe Total, que muestra una representación gráfica de la fragmentación de los índices SQL y del uso del espacio en disco en el nivel seleccionado. Por ejemplo, si hace clic en el nombre de la instancia, el informe dibujará el gráfico del porcentaje de fragmentación y el gráfico de uso del espacio a nivel de la instancia de SQL Server, como se muestra a continuación:

Si hace clic en cualquier base de datos de la ventana del Explorador de servidores, el informe cambiará automáticamente la vista para mostrar el gráfico a nivel de la base de datos, como se muestra a continuación:

El segundo informe, que también se puede ver a diferentes niveles, es el informe de estadísticas. Este informe muestra información estadística sobre el tamaño y la fragmentación total de los índices y la distribución de los tipos de índice en el nivel seleccionado, como se muestra a continuación:

El tercer informe es el informe Top 10, que muestra los 10 principales índices agrupados y no agrupados con el mayor tamaño y el mayor porcentaje de fragmentación de índices SQL, en el nivel especificado. Por ejemplo, el siguiente informe muestra los 10 índices más caros, según el tamaño y la fragmentación, en el nivel de instancia del servidor SQL:

Al bajar a una base de datos específica, el informe mostrará automáticamente los 10 índices más caros, según el tamaño y la fragmentación, en el nivel de base de datos seleccionado, como se muestra a continuación:

Bajando profundamente a una tabla específica bajo esa base de datos, el informe mostrará los 10 índices más caros, basados en el tamaño y la fragmentación, como se muestra a continuación:

Exportación de informes de fragmentación de ApexSQL

ApexSQL Defrag nos permite exportar los informes de fragmentación a diferentes formatos de archivos, incluye PDF, IMG, HTML, CS, y XML, con la posibilidad de personalizar la configuración del informe antes de ser generado. En la pestaña Informes, pulsamos el botón Opciones:

Desde la ventana Editar Opciones de Exportación, podemos configurar las diferentes opciones, empezando por la convención de nombres del archivo generado, la ruta donde se guardará el archivo, la orientación de la página y finalmente el contenido de cada informe, como se muestra a continuación:

Por ejemplo, para exportar el informe Total generado en el nivel especificado, pulse el botón Exportar, en la pestaña Informes, y elija el tipo de archivo, al que se exportará este informe Total. En unos segundos se generará el informe, preguntándole si desea abrir el informe generado. Haga clic en Sí para abrir ese informe directamente, como se muestra a continuación:

Además, puede exportar el informe Top 10 en el nivel especificado, haciendo clic en el botón Exportar, bajo la pestaña Informes, y especificando el tipo de archivo de exportación, y el informe se generará rápidamente, preguntándole si desea ver el informe generado directamente. Haga clic en Sí para abrir el generado, como se muestra a continuación:

De los ejemplos anteriores se desprende que podemos utilizar fácilmente la herramienta ApexSQL Defrag para revisar y analizar la información del porcentaje de fragmentación de los índices de SQL y bucear en los diferentes niveles empezando por el nivel de instancia de SQL Server hasta el nivel de índice de SQL Server y mostrarlo en diferentes formatos exportables, sin que la información mostrada se vea afectada por ningún reinicio de servidor o servicio, ya que se almacenarán en una base de datos repositorio.