MASANASA

Para reorganizar y reconstruir los indices mediante está consulta se pueden obtener cuales están fragmentados

Según las especificaciones de Microsoft (url Microsoft) se recomienda

avg_fragmentation_in_percent > 5% y < = 30% ALTER INDEX REORGANIZE avg_fragmentation_in_percent > 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

Y que el número de páginas (page_count) sea al menos de 1000

Con esta consulta se obtienen los indices que están fragmentados.
(hay que reemplazar nombredetubasededatos por la base de datos vuestra)

select b.name as NameIndex, Schema_id, o.name as NameTable,s.avg_fragmentation_in_percent  from sys.dm_db_index_physical_stats (DB_ID(N'nombredetubasededatos'), Null, NULL, NULL, NULL) AS s  
JOIN sys.indexes AS b ON s.object_id = b.object_id AND s.index_id = b.index_id 
JOIN sys.objects o ON o.[object_id] =s.[object_id]
Where  s.index_id>0 --Para quitar los HEAP
and page_count>1000
and avg_fragmentation_in_percent>5

select b.name as NameIndex, Schema_id, o.name as NameTable,s.avg_fragmentation_in_percent from sys.dm_db_index_physical_stats (DB_ID(N'nombredetubasededatos'), Null, NULL, NULL, NULL) AS s

JOIN sys.indexes AS b ON s.object_id = b.object_id AND s.index_id = b.index_id

JOIN sys.objects o ON o.[object_id] =s.[object_id]

Where s.index_id>0 --Para quitar los HEAP

and page_count>1000

and avg_fragmentation_in_percent>5

Basándome en la fuente sqlauthority este script se puede insertar en un job y programar para que se ejecute en la periodicidad que se desee.

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
SELECT '
ALTER INDEX [' + NameIndex + N'] ON [' + SCHEMA_NAME(schema_id) + '].[' + NameTable + '] ' +
CASE WHEN avg_fragmentation_in_percent > 30
THEN 'REBUILD'
ELSE 'REORGANIZE'
END + ';
'
FROM (
-- Consulta para obtener los indices.
select b.name as NameIndex, Schema_id, o.name as NameTable,s.avg_fragmentation_in_percent  from sys.dm_db_index_physical_stats (DB_ID(N'nombredetubasededatos'), Null, NULL, NULL, NULL) AS s  
JOIN sys.indexes AS b ON s.object_id = b.object_id AND s.index_id = b.index_id 
JOIN sys.objects o ON o.[object_id] =s.[object_id]
Where  s.index_id>0 --Para quitar los HEAP
and page_count>1000
and avg_fragmentation_in_percent>5) as sub	
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
PRINT @SQL
EXEC sys.sp_executesql @SQL;

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = (

SELECT '

ALTER INDEX [' + NameIndex + N'] ON [' + SCHEMA_NAME(schema_id) + '].[' + NameTable + '] ' +

CASE WHEN avg_fragmentation_in_percent > 30

THEN 'REBUILD'

ELSE 'REORGANIZE'

END + ';

FROM (

-- Consulta para obtener los indices.

select b.name as NameIndex, Schema_id, o.name as NameTable,s.avg_fragmentation_in_percent from sys.dm_db_index_physical_stats (DB_ID(N'nombredetubasededatos'), Null, NULL, NULL, NULL) AS s

JOIN sys.indexes AS b ON s.object_id = b.object_id AND s.index_id = b.index_id

JOIN sys.objects o ON o.[object_id] =s.[object_id]

Where s.index_id>0 --Para quitar los HEAP

and page_count>1000

and avg_fragmentation_in_percent>5) as sub

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

PRINT @SQL

EXEC sys.sp_executesql @SQL;

Por otra parte respecto al page_count en este artículo que es bastante interesante, indica que se debe revisar también, el tanto por cien del espacio aprovechado por página, para decir si es conveniente reorganizar el indice. Os pego la consulta:

SELECT	OBJECT_NAME(I.object_id) Tablename,
I.name,
I.type_desc,
P.partition_number,
P.page_count,
P.avg_page_space_used_in_percent,
P.index_depth,
P.index_level,
P.avg_fragmentation_in_percent,
P.fragment_count,
P.avg_fragment_size_in_pages,
P.avg_record_size_in_bytes,
P.record_count
FROM	SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,'DETAILED') AS P
INNER JOIN sys.indexes AS I ON P.object_id = I.object_id AND P.index_id = I.index_id
WHERE	P.index_level < P.index_depth -1 /*Ignore all root level pages*/
AND	(P.avg_page_space_used_in_percent <(ISNULL(NULLIF(I.fill_factor, 0), 100) * 0.85)
/*Where space used is less 85% of total after fill factor*/
OR avg_fragmentation_in_percent > 20) /*Where fragmentation is greater than 20%*/

SELECT OBJECT_NAME(I.object_id) Tablename,

I.name,

I.type_desc,

P.partition_number,

P.page_count,

P.avg_page_space_used_in_percent,

P.index_depth,

P.index_level,

P.avg_fragmentation_in_percent,

P.fragment_count,

P.avg_fragment_size_in_pages,

P.avg_record_size_in_bytes,

P.record_count

FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,'DETAILED') AS P

INNER JOIN sys.indexes AS I ON P.object_id = I.object_id AND P.index_id = I.index_id

WHERE P.index_level < P.index_depth -1 /*Ignore all root level pages*/

AND (P.avg_page_space_used_in_percent <(ISNULL(NULLIF(I.fill_factor, 0), 100) * 0.85)

/*Where space used is less 85% of total after fill factor*/

OR avg_fragmentation_in_percent > 20) /*Where fragmentation is greater than 20%*/

Os puede ser útil el siguiente script que te crea los jobs del mantenimiento de la base de datos Sql server Script Mantenimiento Sql

Mis apuntes de programación

Indices SQL Server (Reorganize and Rebuild Indexes)

Relacionado

Deja un comentario Cancelar respuesta