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:
{ 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:
USE AdventureWorks2008R2
GO
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(‘Person.Person’)
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)
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)
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)
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
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.
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
Aquí se pueden bajar el script de desfragmentación para la versión SQL 2000:
Script Desfragmentación SQL 2000