Fragmentación de Índices en SQL Server (Parte 1)

Los índices de una base de datos se fragmentan conforme se van modificando los datos en las tablas. El objetivo de este artículo no es entrar en detalle sobre el por qué o cómo de la fragmentación, eso lo veremos en otro artículo. Solo digamos que la fragmentación de índices reduce el desempeño de las consultas, es decir, las hace más lentas. En este artículo vamos a revisar un método rápido y simple para obtener información de la fragmentación de índices. En una segunda parte trataremos la forma de eliminar la fragmentación de los índices.

Para obtener información sobre la fragmentación de los índices utilizaremos la dunción del sitema dm_db_index_physical_stats, la cual retorna información del tamaño y fragmentación de los índices de una tabla o vista específica. Esta función está disponible desde la versión 2005 de SQL Server. La sintaxis es la siguiente: 

sys.dm_db_index_physical_stats (
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT } ) 
 
 

Los argumentos de la función se explican a continuación. Luego haremos algunos ejemplos:

database_id | NULL | 0 | DEFAULT
Es el id de la base de datos que deseamos analizar. Si a este parámetro se le asigna NULL, 0 ó DEFAULT mostrará información de todas las bases de datos de la instancia. Una manera simple de obtener información de una base de datos en particular, es ejecutar la funcion conectado a la base de datos y colocar en este parámetro la funcion DB_ID() la cual retorna el Id de la base de datos en la que estamos conectados.
 
object_id | NULL | 0 | DEFAULT
Es el id del objeto que se desea analizar, ya sea una tabla o una vista.  Si a este parámetro se le asigna NULL, 0 ó DEFAULT mostrará información de todas las tablas o vistas. Una manera simple de obtener información de una tabla o vista en particular, es colocar en este parámetro la funcion OBJECT_ID() la cual retorna el Id del objeto que deseamos analizar.

index_id | 0 | NULL | -1 | DEFAULT
Es el id del índice que se desea analizar.  Si a este parámetro se le asigna NULL, 0 ó DEFAULT mostrará información de todos los indices del objeto especificado en el parámetro anterior. Para obtener el Id de un índice, podemos consultar la vista del sistema sys.indexes.
 
partition_number | NULL | 0 | DEFAULT
Es el id de partición del objeto. Si a este parámetro se le asigna NULL, 0 ó DEFAULT mostrará todas las particiones del objeto a analizar.
 
mode | NULL | DEFAULT
El modo especifica el nivel de escaneo que se utiliza para obtener las estadísitca. Se puede utilizar los parámetros DEFAULT, NULL, LIMITED, SAMPLED, o DETAILED. Si indicamos NULL utiliza el parámetro LIMITED.

Para obtener información completa sobre esta finción haz clic aquí.
 
Ahora desarrollaremos algunos ejemplos:
 
La siguiente consulta nos muestra los indices de la tabla Person del esquema Person. Esta consulta es útil por que nos muestra el campo index_id. Podríamos usar el procedimiento almacenado del sistema sp_helpindex pero el resultado no muestra en index:id, el cual es útil cuan queremos usar la función sys.dm_db_index_physical_stats. 
 

USE AdventureWorks2008R2

GO

 

SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(‘Person.Person’)

 

image

 

Ahora si tenemos un ejemplo de la función sys.dm_db_index_physical_stats. Todas estas sentencias las debemos ejecutar conectados a la base de datos de demo AdventureWorks2008 o AdventureWorks2008R2 según la versión de SQL Server que se este usando. En el siguiente ejemplo, vamos a obtener las información de tamaño y fragmentación del indice IX_Person_LastName_FirstName_MiddleName (Index_Id = 2) de la tabla Person.Person:

SELECT object_id,index_id,partition_number,index_type_desc,index_depth,

        avg_fragmentation_in_percent,fragment_count,page_count

FROM sys.dm_db_index_physical_stats

    (DB_ID(),OBJECT_ID(‘Person.Person’),2,NULL,NULL)

 

image

  

Si queremos analizar todos los indices de la tabla Person.Person, simplemente indicamos NULL en el tercer parámetro. Observemos las columnas avg_fragmentation_in_percent y la columna page_count:

SELECT object_id,index_id,partition_number,index_type_desc,index_depth,

        avg_fragmentation_in_percent,fragment_count,page_count

FROM sys.dm_db_index_physical_stats

    (DB_ID(),OBJECT_ID(‘Person.Person’),NULL,NULL,NULL)

 

image

  

Si queremos analizar todos los indices de todas las tablas y vistas indizadas de la base de datos, simplemente indicamos NULL en el segundo parámetro. Observemos nuevamente las columnas avg_fragmentation_in_percent y la columna page_count:

SELECT object_id,index_id,partition_number,index_type_desc,index_depth,

       avg_fragmentation_in_percent,fragment_count,page_count

FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL)

 

image

 

 Pero si observaomos detenidamente, la consulta anterior no muestra el esquema y nombre de la tabla o vista, ni tampoco el nombre del índice. Además, muestra todos los índices con o sin fragmentación. Cuando hacemos este análisis, por lo general queremos ocuparnos de los índices que tienen fragmentación y un número considerable de páginas. No vale la pena dedicar tiempo a desfragmentar índices pequeños (con pocas páginas). La siguiente consulta combina la función sys.dm_db_index_physical_stats con vistas del sistema (sys.objects y sys.indexes) para completar información. Obsérvese que la función se incluye como un subquery para poder hacer los JOIN:

SELECT

  SCHEMA_NAME(o.schema_id) AS schema_name,

  OBJECT_NAME(l.object_id) AS object_name,

  l.index_id,

  i.name,

  l.index_type_desc,

  l.index_depth,

  l.avg_fragmentation_in_percent,

  l.page_count

FROM (

      SELECT object_id,index_id,partition_number,index_type_desc,

      index_depth,avg_fragmentation_in_percent,fragment_count,page_count

      FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL)

     ) AS l

 JOIN sys.indexes i

  ON l.object_id = i.object_id

  AND l.index_id = i.index_id

 JOIN sys.objects o

  ON l.object_id = o.object_id

WHERE

  l.avg_fragmentation_in_percent > 10

  AND page_count > 10

  AND l.index_id > 0

ORDER BY

  l.avg_fragmentation_in_percent DESC,

  l.page_count,

  l.object_id,

  l.index_id

 

image

 

El resultado de esta consulta muestra el esquema, el objeto (tabla o vista indizada), el nombre del índice y los índices agrupados y no agrupados con más de 10 páginas y con fragmentación mayor a 10% ordernada de mayor a menor. Solo serán estos índices a los que le debemos prestar atención.

 .

CONCLUSIÓN: La función del sistema sys.dm_db_index_physical_stats nos permite obtener información física de los índices, principalmente la fragmentación, de manera rápida y flexible de acuerdo a los parámetros que utilicemos. Es importante concentrarse en los índices voluminosos, de acuerdo al dato page_count. SQL Server cuenta con 2 opciones para desfragmentar los índices: REORGANIZE y REBUILD, las cuales serán tratadas en la parte 2 de este artíulo proximamente. 

2 comentarios en “Fragmentación de Índices en SQL Server (Parte 1)

  • Recrear todos los indices de una Base de Datos

    CREATE PROCEDURE pa_rebuild_index
    @BaseDatos VARCHAR(80)
    AS
    BEGIN
    declare tabs cursor for select name from sysobjects where xtype = ‘U’
    declare @NombreTabla varchar(255)
    declare @FullNombreTabla varchar(255)
    declare @crlf varchar (2)

    set @crlf = char(13) + char(10)
    –TIEMPO INICIO
    print getdate()
    open tabs
    fetch next from tabs
    into @NombreTabla
    while @@FETCH_STATUS = 0
    begin
    set @FullNombreTabla = @BaseDatos + ‘.dbo.’ + @NombreTabla
    print ‘dbreindex (‘+ @FullNombreTabla+ ‘,””,90) with no_infomsgs’
    dbcc dbreindex (@FullNombreTabla,”,90) with no_infomsgs
    if @@error 0
    begin
    RAISERROR(‘error rebuild index’,11,1)
    end
    else print ‘finalizado re-build’
    print @crlf
    fetch next from tabs
    into @NombreTabla
    end
    close tabs
    deallocate tabs
    –TIEMPO FIN
    print getdate()
    END

Deja un comentario

Tu dirección de correo electrónico no será publicada.