Script para coleta de DBCC Showcontigs
Nesta última semana eu estava dedicado para fazer um trabalho de análise de performance em um servidor SQL Server. No fim das contas a máquina estava bem configurada e respondendo bem às requisições, assim as recomendações ficaram por conta da localização dos arquivos de dados e de logs, recomendações para o tempdb, utilização do /3GB no boot.ini, entre outros.
Dentre as ferramentas de coleta e análise estão o blocker script, perfmon, profiler, SQLDiag e o Read80Trace, porém senti a necessidade de fazer um levantamento rápido da fragmentação de algumas tabelas. Realmente não sei se já existe algo que pega automaticamente essa informação, então resolvi fazer o meu script:
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
USE Master
GO
DECLARE @NumeroRegistros INT
/*
!!! Este valor deve ser configurado de acordo com o tamanho dos bancos de dados que serão analisados !!!
Um valor de 0 irá checar todos os índices (ou heap) de todas as tabelas de todos os bancos de dados.
*/
SET @NumeroRegistros = 100000
DECLARE @Nome VARCHAR(200)
DECLARE @dbid INT
DECLARE cur_bancos CURSOR FOR
SELECT [Name], dbid FROM SysDatabases
WHERE [NAME] NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution', 'pubs', 'Northwind')
OPEN cur_bancos
FETCH NEXT FROM cur_bancos
INTO @Nome, @dbid
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ('/**************************************************************************')
PRINT (' BANCO DE DADOS: ' + @Nome)
PRINT ('***************************************************************************/')
PRINT ('')
PRINT ('USE ' + @Nome)
PRINT ('')
PRINT ('PRINT ('' '')')
PRINT ('PRINT (''Iniciando coleta de dados: '' + CONVERT(varchar(50), Getdate(), 120))')
PRINT ('PRINT ('' '')')
PRINT ('PRINT(''/**************************************************************************'')')
PRINT ('PRINT('' BANCO DE DADOS: ' + @Nome + ''')')
PRINT ('PRINT(''**************************************************************************/'')')
PRINT ('')
PRINT ('DECLARE @Tabela VARCHAR(300)')
PRINT ('DECLARE @TabelaID INT')
PRINT ('DECLARE @IndID INT')
PRINT ('DECLARE @Rowcnt INT')
PRINT ('')
PRINT ('DECLARE cur_NumRegistros CURSOR FOR')
PRINT ('SELECT DISTINCT SO.[Name], SI.Rowcnt ')
PRINT ('FROM SysObjects as SO INNER JOIN SysIndexes AS SI')
PRINT ('ON SO.[id] = SI.[id] WHERE SO.XType = ''U'' AND SI.Rowcnt >= ' + CAST(@NumeroRegistros AS VARCHAR(30)))
PRINT ('ORDER BY SI.Rowcnt DESC')
PRINT ('')
PRINT ('OPEN cur_NumRegistros')
PRINT ('')
PRINT ('FETCH NEXT FROM cur_NumRegistros ')
PRINT ('INTO @Tabela, @Rowcnt')
PRINT ('')
PRINT ('WHILE @@FETCH_STATUS = 0')
PRINT ('BEGIN')
PRINT ('')
PRINT ('PRINT (''/*** Tabela: '' + @Tabela + '' #Registros: '' + CAST(@Rowcnt AS VARCHAR(15)) + '' ****/'')')
PRINT ('FETCH NEXT FROM cur_NumRegistros ')
PRINT ('INTO @Tabela, @Rowcnt')
PRINT ('')
PRINT ('END')
PRINT ('')
PRINT ('CLOSE cur_NumRegistros')
PRINT ('DEALLOCATE cur_NumRegistros')
PRINT ('')
PRINT ('DECLARE cur_Tabelas CURSOR FOR')
PRINT ('SELECT SO.[Name], SO.[id], SI.IndID, SI.Rowcnt ')
PRINT ('FROM SysObjects as SO INNER JOIN SysIndexes AS SI')
PRINT ('ON SO.[id] = SI.[id] WHERE SO.XType = ''U'' AND SI.Rowcnt >= ' + CAST(@NumeroRegistros AS VARCHAR(30)))
PRINT ('ORDER BY SI.Rowcnt DESC')
PRINT ('')
PRINT ('OPEN cur_Tabelas')
PRINT ('')
PRINT ('FETCH NEXT FROM cur_Tabelas ')
PRINT ('INTO @Tabela, @TabelaID, @IndID, @Rowcnt')
PRINT ('')
PRINT ('WHILE @@FETCH_STATUS = 0')
PRINT ('BEGIN')
PRINT ('')
PRINT ('PRINT ('' '')')
PRINT ('PRINT (''/*** Tabela: '' + @Tabela + '' Índice: '' + CAST(@IndID AS CHAR(3)) + '' #Registros: '' + CAST(@Rowcnt AS VARCHAR(15)) + '' ****/'')')
PRINT ('PRINT ('' '')')
PRINT ('DBCC SHOWCONTIG( @Tabela, @IndId)')
PRINT ('')
PRINT ('FETCH NEXT FROM cur_Tabelas ')
PRINT ('INTO @Tabela, @TabelaID, @IndID, @Rowcnt')
PRINT ('')
PRINT ('END')
PRINT ('')
PRINT ('CLOSE cur_Tabelas')
PRINT ('DEALLOCATE cur_Tabelas')
PRINT ('PRINT ('' '')')
PRINT ('PRINT (''Finalizando coleta de dados: '' + CONVERT(varchar(50), Getdate(), 120))')
PRINT ('PRINT ('' '')')
PRINT ('')
PRINT ('GO')
FETCH NEXT FROM cur_bancos
INTO @Nome, @dbid
END
CLOSE cur_bancos
DEALLOCATE cur_bancos
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
A finalidade do script é gerar um outro script que também deve ser executado e contém todas as chamas ao DBCC ShowContig das tabelas que possuem um número maior ou igual a X registros, onde X é o valor da variável @NumeroRegistros configurada no início do script. Por padrão somente os bancos de dados de sistema e os exemplos Northwind e Pubs são ignorados.
Se o script for executado com o parâmetro @NumeroRegistros = 0, ele vai listar a fragmentação de todas as tabelas e seus índices para TODOS os bancos de dados de usuário, portanto cuidado. Depois alguém roda esse script e faz o servidor ficar trabalhando por x horas e ainda coloca a culpa em mim. J
Com o resultado desse script pude mostrar que algumas das maiores tabelas do sistema (com mais de 1.000.000 registros) estavam fragmentadas e era necessário incluir rotinas de defragmentação no plano de manutenção do servidor.
Um outro resultado curioso foi que uma das grandes tabelas apresentou os resultados do DBCC showcontig para seus índices, quase nada, só 28 resultados! Olhando a saída do Read80Trace podemos confirmara qual é a operação que consumia mais I/O de escrita, justamente um insert nesta tabela. Também pudera, pois cada inserção tem que alterar o índice clusterizado e mais 27 não-clusterizados, é assim que os índices passam de mocinhos para os vilões da história.
Espero que o script possa ajudar e qualquer sugestão é sempre bem vinda. Em anexo está o arquivo com o script completo.
[]s
Luti