Dicas para um DBA iniciante – Listar fragmentação de índices de todos os bancos de dados

By Vitor Fava

Fala pessoal, tudo bem com vocês?

Hoje quero compartilhar mais um script muito útil que precisei utilizar recentemente no meu dia a dia como DBA SQL Server.

Esse script utiliza a stored procedure de sistema SP_MSFOREACHDB para executar uma verificação na fragmentação de todos os índices de cada uma das bases de dados existentes em sua instância de banco de dados SQL Server.

IF EXISTS (SELECT * FROM TEMPDB.SYS.ALL_OBJECTS WHERE 
			NAME LIKE '#bbc%' )
	DROP TABLE #bbc

CREATE TABLE #bbc
(
	DatabaseName VARCHAR(100),
	ObjectName VARCHAR(100),
	Index_id INT, 
	indexName VARCHAR(100),
	avg_fragmentation_percent FLOAT,
	IndexType VARCHAR(100),
	Action_Required VARCHAR(100) DEFAULT 'NA'
)
GO

INSERT INTO #bbc (
					DatabaseName,
					ObjectName,
					Index_id, 
					indexName,
					avg_fragmentation_percent,
					IndexType
				 )

EXEC MASTER.SYS.SP_MSFOREACHDB ' USE [?]

SELECT 
	db_name() as DatabaseName, 
	OBJECT_NAME (a.object_id) as ObjectName, 
	a.index_id, 
	b.name as IndexName, 
	avg_fragmentation_in_percent, 
	index_type_desc
FROM 
	sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a
INNER JOIN 
	sys.indexes AS b 
ON 
	a.object_id = b.object_id 
AND 
	a.index_id = b.index_id
WHERE 
	b.index_id  0 
AND 
	avg_fragmentation_in_percent  0'
GO
 
UPDATE #bbc
SET Action_Required ='Rebuild'
WHERE avg_fragmentation_percent >30 
GO

UPDATE #bbc
SET Action_Required ='Reorganize'
WHERE avg_fragmentation_percent 5
GO

SELECT 
	* 
FROM 
	#bbc 
ORDER BY 
	#bbc.avg_fragmentation_percent 
DESC

Espero que gostem e não deixem de inscreverem-se no blog, no canal do youtube, no grupo de discussão SQLManiacs e em nosso grupo no TELEGRAM.

Grande abraço a todos.

Fonte: Vitor Fava