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

Informacao de fragmentacao.sql

Comments (0)

Skip to main content