Fala, pessoal!

Há um tempo eu publiquei uma procedure chamada SP_SHOWINDEX. Essa procedure facilita a visualização de índices no SQL Server e é uma das procedures que mais utilizo no meu dia a dia em tarefas de tuning de queries ou até mesmo para monitorar a utilização dos índices. Hoje mostrarei uma solução completa de monitoramento baseada na SP_SHOWINDEX.

Para refrescar sua memória tem os artigos anteriores sobre a evolução da SP_SHOWINDEX:

• https://blogdojamal.wordpress.com/2016/11/28/sp_showindex/
• https://blogdojamal.wordpress.com/2017/01/06/sp_showindex-v2/
• https://blogdojamal.wordpress.com/2017/02/03/sp_showindex-no-sql-azure-e-outras-novidades/
• https://blogdojamal.wordpress.com/2017/09/08/sp_showindex-2-0-0-3/

Se você ainda não conhece a SP_SHOWINDEX, mostrarei alguns exemplos antes de entrar na automação da rotina.

SP_SHOWINDEX @HELP = 1

A partir da versão 2.0.0.4 foram incluídos dois novos parâmetros. Esses parâmetros foram incluídos justamente para auxiliar nossa automação.

A SHOWINDEX possui diversas combinações de parâmetros que podem auxiliar na visualização dos seus índices. Abaixo uma chamada com alguns parâmetros comuns:

SP_SHOWINDEX @TABLE_NAME = ‘TB_JOGADORES’,@INDEX_DETAILS = 1,@LIMITED = 1,@INDEX_FRAGMENTATION = 1

Temos um ResultSet maravilhoso com a combinação de parâmetro. Você pode adicionar mais informações ou resumir mais a visualização.

SP_SHOWINDEX @INDEX_NAME = ‘%APELIDO%’, @LIMITED = 1

Trazendo apenas índices que contêm em seu nome a palavra ‘APELIDO’, utilizando o parâmetro @LIMITED para ter uma visualização mais limpa.

Quer um ResultSet customizado?

SP_SHOWINDEX
@INDEX_NAME = ‘%IDX%’,
@OUTPUT_COLUMNS = ‘TABLE_NAME,INDEX_NAME,SEEKS,SCANS,UPDATES,CREATE_COMMAND’,
@FILTER = ‘SEEKS > 0’

Bom, deixarei a missão de explorar SHOWINDEX com vocês. Quero mostrar agora uma rotina para coletar uma foto diária de como estão seus índices e também receber relatórios diários ou semanais da saúde dos seus índices.

A solução será composta por uma tabela, duas procedures e um Job.

Você já deverá estar operando com a versão mais recente da SHOWINDEX:

• https://github.com/Jamal27/SQLServer_Scripts/blob/master/SP_SHOWINDEX

Primeiro passo: crie a tabela do script abaixo em um banco de dados – pode ser um já existente ou crie um específico para o seu monitoramento. No meu caso chamarei o banco de dados de DBA_MONITOR.

RESULTADO_SHOWINDEX.

• https://github.com/Jamal27/SQLServer_Scripts/blob/master/TABELA%20-%20RESULTADO_SHOWINDEX.sql

Depois de escolhre o banco de dados e criar a tabela, criaremos as duas procedures abaixo:

SP_DBA_INDEXMONITOR

• https://github.com/Jamal27/SQLServer_Scripts/blob/master/SP_DBA_INDEXMONITOR.sql

SP_SEND_INDEXREPORT

• https://github.com/Jamal27/SQLServer_Scripts/blob/master/SP_SEND_INDEXREPORT.sql

E por último, o Job no SQL Server para chamar as procedures:

JOB – DBA – INDEX MONITOR

• https://github.com/Jamal27/SQLServer_Scripts/blob/master/JOB%20-%20DBA%20-%20INDEX%20MONITOR.sql

O Job inicialmente está com agendamento para rodar diariamente à meia noite. Dentro do Job há dois passos. O primeiro coletará as informações sobre seus índices; o segundo enviará um relatório para o seu e-mail. Caso não queira receber um relatório diário, você pode criar um novo Job ou até mesmo remover este step.

Dentro do passo 1 chamado LOAD, está sendo executado o comando abaixo:

EXEC SP_DBA_INDEXMONITOR
@DATABASES = ‘USER_DATABASES’, –Todas as bases de usuários
@EXCEPT = ‘SQLDAYES,traces’, –Ignora as bases dessa lista
@DATABASE_DESTINATION = ‘DBA_MONITOR’, –Banco destino para salvar a coleta
@TABLE = ‘RESULTADO_SHOWINDEX’ –Tabela destino para salvar a coleta

Você poderá acompanhar seus índices na tabela RESULTADO_SHOWINDEX, ou na tabela que escolheu.

SELECT * FROM [RESULTADO_SHOWINDEX]

Caso a tabela informada não exista, você receberá um comando de criação dela:

EXEC SP_DBA_INDEXMONITOR
@DATABASES = ‘AdventureWorks2016CTP3’,
@DATABASE_DESTINATION = ‘TRACES’,
@TABLE = ‘RESULTADO_SHOWINDEX_NEW’,
@PURGE_HISTORY_DAYS = 200

Você pode customizar quais bases de dados gostaria de monitorar, também podendo colocar uma lista de exceção. Quando informado USER_DATABASES, todos os bancos de dados de usuários serão coletados, exceto os listados no parâmetro @EXCEPT.

Pensando na quantidade de dados que será armazenada por dia, adicionei o parâmetro @PURGE_HISTORY_DAYS, seu valor default é de 180 dias, então todos os dados acima de seis meses serão expurgados da tabela.

Dependendo da quantidade de índices que houver dentro da sua base de dados, a execução pode levar alguns minutos, pois a SHOWINDEX é executada com o parâmetro @INDEX_FRAGMENTATION = 1, isso faz com que utilize a DMV SYS.DM_DB_INDEX_PHYSICAL_STATS.

Internamente essa é a chamada da SP_SHOWINDEX dentro da procedure SP_DBA_INDEXMONITOR, com suas devidas tratativas:

EXEC SP_SHOWINDEX
@INDEX_DETAILS = 1,
@INDEX_FRAGMENTATION = 1,
@OUTPUT_COLUMNS =’SERVER_NAME,DATABASE_NAME,TABLE_NAME,
INDEX_NAME,INDEX_TYPE,SEEKS,SCANS,LOOKUPS,UPDATES,
PAGE_COUNT,ROW_COUNT,FILL_FACTOR,DATA_COMPRESSION,
AVG_FRAGMENTATION_IN_PERCENT,COLUMNS,INCLUDE_COLUMNS,
PRIMARY_KEY,LAST_SEEK,LAST_SCAN,LAST_LOOKUP,LAST_UPDATE,IS_DISABLED’,
@DESTINATION_TABLE =’RESULTADO_SHOWINDEX’,
@DESTINATION_DATABASE = ‘DBA_MONITOR’

O ideal é o Job ser executado antes da sua rotina de manutenção de índices, para que possa ser capturado a fragmentação dos índices, entre outros dados daquele dia.

Um exemplo de relatório que receberá sobre seus índices:

Quantidade de índices por banco de dados.

Tabelas com índices duplicados, TOP 10 índices mais utilizados.

Índices mais fragmentados.

Tabelas com Primary Key, mas como índice não cluster.

Índices com Fill Factor fora do padrão, índices com compressão.

Todas essas informações chegarão em um único e-mail diariamente. Essas informações podem ter limites configurados, assim como a opção do que será enviado. Lista de itens enviados:

• Índices mais fragmentados
• Índices pouco utilizados
• Índices mais atualizados (Escritas)
• Índices mais utilizados (Leituras)
• Quantidade de índices por banco de dados
• Índices desabilitados
• Índices duplicados
• Índices com PK não cluster
• Índices com Fill Factor fora do padrão
• Índices com compressão

A procedure SP_SEND_INDEXREPORT contém os parâmetros para auxiliar caso seu e-mail esteja muito poluído e você queira filtrar informações.

Parâmetros default:

@Indexfrag bit = 1, — Apresenta fragmentação dos índices
@IndexfragPercent tinyint = 50, –Apenas índices com mais de 50% de fragmentação.
@IndexfragPages int = 10000, –Apenas índices com mais de 10000 páginas
@IndexLowutilization bit = 1, –Índices com baixa utilização
@IndexLowutilizationMonths tinyint = 3, –Avaliar últimos 3 meses de coleta
@IndexHighUpdates bit = 1, –Índices com bastante escrita
@IndexUtilization bit = 1, –Índices com bastante leitura
@QtdIndex bit = 1, –Quantidade de índices por banco de dados
@IndexDuplicate bit = 1, –Apresenta índices duplicados
@IndexDisabled bit = 1, –Apresenta índices desabilitados
@IndexPkNonClustered bit = 1, –Apresenta índices PK non clustered
@IndexFillFactor bit = 1, –Apresenta fill factor dos índices
@IndexFillFactorPercent tinyint= 98, –Índices apenas com Fill factor menor que 98
@IndexCompression bit = 1 — Apresenta índices com compressão

Exemplo de chamada para adicionar no seu Job:

EXEC SP_SEND_INDEXREPORT
@IndexfragPercent = 70, –Índices com fragmentação acima de 70%
@IndexfragPages = 10000, –Índices com mais de 10000 páginas
@IndexLowutilizationMonths = 1, –Avaliar 1 mês de utilização
@IndexFillFactorPercent = 98, –Índices com Fill Factor menor que 98
@IndexHighUpdates = 0, –Não apresentar utilização de índices (Escrita)
@IndexDuplicate = 0, –Não apresentar índices duplicados
@IndexDisabled = 0 –Não apresentar índices desabilitados

Observação: Para a procedure SP_SEND_INDEXREPORT, é necessário que já tenha um Database Mail configurado. Troque apenas as informações de Profile no script.

Para configuração do Database Mail, indico a leitura do artigo criado pelo meu amigo Dirceu Resende:

• SQL Server – Como ativar e configurar o Database mail para enviar e monitorar e-mails pelo banco de dados (sp_send_dbmail)

O que podemos responder com esse monitoramento:

• Criei um índice. Como está sua utilização de leitura?
• Como está a fragmentação dos meus índices?
• Como estava a utilização dos meus índices no mês passado?
• Posso desabilitar o índice IDX_XXX?
• Quando a estrutura de um índice foi alterada?
• Quando um índice foi desabilitado?
• Quando um índice foi deletado?
• Tenho índices duplicados?
• Quantos índices eu tenho por base de dados?
• Tenho índices com compressão?
• Tenho chaves primárias como índice não cluster?
• Tenho índices com Fill Factor muito baixo?
• Quais índices receberam mais escrita no último mês?
• Qual índice recebeu mais leitura no mês passado?
• Quais índices fragmentam mais que 90% todos os dias?
• Quais campos faziam parte do INCLUDE do índice IDX_XX no dia 01/09/2018?

Meu índice foi apagado, consigo recuperar a estrutura dele?

Bom, eu diria que você conseguiria responder quase todas as perguntas relacionadas aos índices do seu ambiente.

Mesmo que não queira receber o relatório por e-mail, pois em alguns casos pode mais atrapalhar do que ajudar, você pode deixar ao menos o Job de coleta, e com isso você poderá responder todas as questões acima, acessando a tabela via T-SQL!

É isso aí, pessoal. Espero que gostem. Fiquem à vontade para customizar suas próprias visualizações de e-mail. Dúvidas, sugestões, críticas ou problemas, por favor me contate – vamos trabalhar juntos para entregar um conteúdo cada vez mais rico.

Se você quer adicionar informações na SP_SHOWINDEX, fique à vontade. Vamos fazer um novo deploy adicionando sua sugestão!