DMO’s de Índices: Testes, resets e rebuilds.

raizes, indices. A ideia era pra ser essa.

Era pra passar a ideia de raízes.

Olá,

Antes de iniciar o post, aviso que vou usar bastante o termo DMO (forma de agrupar os termos DMV e DMF). Clique aqui  e aqui  para ler mais sobre os termos.

DMO’s em geral (DMV’s e DMF’s) são coisas legais do SQL Server, que se tornaram disponíveis para uso público a partir do SQL Server 2005 e desde então tem facilitado bastante a obtenção de certas estatísticas relacionadas ao servidor SQL Server e/ou banco de dados em questão.

Existe uma classe de DMO’s extremamente útil que retorna informações relacionadas aos  índices:

Nome

Tipo

dm_db_index_operational_stats

SQL_INLINE_TABLE_VALUED_FUNCTION (DMF)

dm_db_index_physical_stats

SQL_INLINE_TABLE_VALUED_FUNCTION (DMF)

dm_db_index_usage_stats

VIEW (DMV)

Vou chamar todos os três de DMO neste post, embora pessoalmente goste de usar o termo DMV pra tudo (por conveniência), inclusive pra referenciar DMF. Não estou considerando DMO’s de Missing indexes, pois não são o tópico do momento.

O objetivo deste post é explicar o que cada DMO retorna como resultado entrando utilizando a prática pra fixar o conteúdo, demonstrando alguns detalhes do funcionamento de cada um destes itens que não são tão aparentes assim relativos à persistência destas informações, e que podem gerar entendimentos incorretos sobre as informações que ele retorna.

Os testes foram realizados em uma instância SQL Server 2008 R2, embora possam ser feitos tanto no SQL Server 2008R2 como no 2012 ou no 2014.  Existe apenas uma única diferença que ocorre na db_index_usage_stats. Lá na frente explico o porquê.

Vamos aos trabalhos

Sem demo, a coisa não roda. A coisa não acontece. A coisa não vai. Vamos criar uma base pra começar os testes:

Agora, vamos criar uma tabela com um índice cluster e realizar algumas operações, pra que a gente consiga usar os DMO’s e validar os resultados.

Recomendo atenção com os comentários:

Terminamos nosso “workload” simulado. Vamos agora falar sobre os DMO’s individualmente e entender na prática o que eles retornam pra gente. Os resultados serão resumidos, trazendo apenas alguns campos de interesse, que são justamente aqueles que deixam óbvia qual a utilidade do DMO em questão.

Caso queira reproduzir os resultados, seguem as queries utilizadas para os testes abaixo:

 

Outra coisa: apesar de não ser tão intuitivo assim, uma heap (tabelas sem índice cluster) também tem estatísticas nestes DMO’s. Isso não está coberto nos exemplos, mas achei importante citar…

DM_DB_INDEX_OPERATIONAL_STATS

O que é: Retorna informações operacionais (relacionadas à locking, latching, ghost counts, etc). Também retorna a quantidade de registros afetados por determinadas operações de DML no nível  “folha” do índice (e claro, convenientemente, são as quantidades que eu mostro na tabela abaixo)

As estatísticas retornadas por esta DMF são resetadas em situações normais de várias outros DMO’s não-persistentes, que são:

  • Restart da instância;
  • Indisponibilidade/disponibilidade da base em questão.

Note que o DMO retorna quantidade de registros inseridos (ao todo foram 1003), quantidade de registros atualizados (foi apenas uma operação de update, mas que atualizou todos os registros) e um DELETE que no índice foi marcado como ghost (será removido assim que o cleanup for realizado, para maiores informações pesquise pelos termos “Ghost Cleanup” ou veja as referências).

DatabaseName

Objeto

NomeIndice

leaf_insert_

count

leaf_

delete_

count

leaf_

update_

count

leaf_

ghost_

count

TesteIndexacao

Pessoa

PK__Pessoa__3214EC277F60ED59

1003

0

1000

1

TesteIndexacao

Pessoa

idx_Nome

1003

0

0

1001

Tabela 1 (Registros no nível “folha”  dos índices).

DM_DB_INDEX_PHYSICAL_INDEX

O que é: Esse DMO é o caminho pra identificar fragmentaçãonos índices. Tem que ache que o DBCC SHOWCONTIG muito mais fácil e rápido de usar (comando que esse DMO visa substituir), mas não tem a mesma flexibidade do DMO por permitir a realização de queries.

Database_name

Tabela

Índice

avg_

fragmentation

_in_percent

avg_

fragment

_size_

in_pages

avg_page

_space

_used

_in_percent

record

_count

AVG_

RECORD

_SIZE

_IN_BYTES

TesteIndexacao

Pessoa

PK__Pessoa__

3214EC277

F60ED59

85,71428571

1

49,2869533

1002

25,883

TesteIndexacao

Pessoa

idx_Nome

85,71428571

1

42,21293551

1002

21,883

Tabela 2 (Registro de fragmentação)

DM_DB_INDEX_USAGE_STATS

O que é: Mostra estatísticas de uso por operação. Útil pra saber se o índice está sendo utilizado, se aquele índice sofre mais scan que seek, se é mais atualizado que consultado, se aquela tabela se beneficiaria com a criação de novos índices. É uma ótima forma também de se ter uma ideia do workload de determinada base.

Importante saber: Existe uma confusão que estatísticas este DMO retorna. Note que ele contabiliza operações e não número de registros. Isso significa que se você fizer um UPDATE de um registro, conta como 1 operação. Se você fizer um UPDATE que atualize mil registros, continua contando como uma operação. Se você faz mil INSERTS em batchs diferentes (com um GO embaixo da instrução, conforme foi feito), são mil operações. Significa que, se em algum momento alguém pedir quantidade de registros alterados, essa view não é o caminho.

Comentando rapidamente, note que:

User_Scans = 3

Fizemos dois SELECTs que causaram dois scans. E o nosso update, como varreu todo o índice também realizou outro SCAN. Totalizando três scans.

User_updates = 1003

Fazendo as contas:

– Os inserts totabilizaram 1000 operações, pois fizemos um insert por batch (atenção ao GO no final da instrução).. Total = 1000

– O UPDATE realizado em mil registros contabilizou apenas uma operação. Total  =1001

– O INSERT de três registros  contou como uma operação. Total = 1002

– O DELETE de um registro contou como uma operação. Total = 1003

Porque o detalhamento acima? Pra ilustrar como realmente a contagem desta DMV funciona.

Database_name

Tabela

Índice

user_

scans

user_

updates

last_user

_scan

last_user

_update

last_system

_scan

TesteIndexacao

Pessoa

PK__Pessoa__

3214EC2

77F60ED59

3

1003

2014-12-03 17:53:23.497

2014-12-03 17:53:57.143

2014-12-03 17:52:59.933

TesteIndexacao

Pessoa

idx_Nome

0

3

NULL

2014-12-03 17:53:57.143

2014-12-03 17:53:27.257

..

Agora, vamos realizar um rebuild em todos os índices da tabela.

O motivo disto? Observar o comportamento dos DMO’s em índices recém-construídos.

E o que muda com o rebuild?

Mesmo SELECT da [Operational Stats]

DatabaseName

Objeto

NomeIndice

leaf_

insert

_count

leaf_

delete

_count

leaf_

update

_count

leaf_

ghost

_count

TesteIndexacao

Pessoa

PK__Pessoa__3214EC277F60ED59

0

0

0

0

TesteIndexacao

Pessoa

idx_Nome

0

0

0

0

Note que as informações operacionais (quantidade de registros inseridos no nível “folha”) sumiu. É by design. Porque é importante saber disto: se você montar um relatório ou algo do tipo com o intuito de mensurar quantidade de registros e suas respectivas operações (delete, insert e delete) nos índices  mais realizadas, é óbvio pensar que essas estatísticas serão zeradas apenas nos restarts da base (ou servidor), mas nunca em um rebuild. Significa que por exemplo, se seu último restart da instância ocorreu numa segunda, seus índices sofreram rebuild da quarta e você puxa essas informações na quinta, você não tem informações acumuladas de segunda. Então caso for usar este DMO para realizar algum levantamento, cuidado com os rebuilds (organizes contudo não zeram as estatísticas).

Mesmo SELECT da [Physical Stats]

Database_name

Tabela

Índice

avg_

fragmentation

_in_percent

avg_

fragment_

size_

in_pages

avg_page

_space

_used_

in_percent

record

_count

AVG_

RECORD

_SIZE_

IN_BYTES

TesteIndexacao

Pessoa

PK__Pessoa__

3214EC277F

60ED59

50

1,3333333

86,27069434

1002

25,883

TesteIndexacao

Pessoa

idx_Nome

66,66666667

1

98,52977514

1002

21,883

Óia. Comportamento normal. Se você fez rebuild é natural que as páginas sejam organizadas, diminuindo as fragmentações e densidade de registros por página (aka fragmentação interna). Mas note que é um DMO que puxa metadados dos índices e tanto faz se algum restart ocorrer, as estatísticas não  serão voláteis. Em outras palavras, são estatísticas persistentes. Se você por exemplo realizar a consulta citada, deixar a base OFFLINE e ONLINE, e realizar a mesma consulta novamente, não deverá  terá surpresa com os dados retornados.

Mesmo SELECT da [Usage Stats]

Database_name

Tabela

Índice

user_

scans

user_

updates

last_user

_scan

last_user

_update

last_system

_scan

TesteIndexacao

Pessoa

PK__Pessoa__321

4EC277F60ED59

3

1003

2014-12-03 17:53:23.497

2014-12-03 17:53:57.143

2014-12-03 18:20:29.470

TesteIndexacao

Pessoa

idx_Nome

0

3

NULL

2014-12-03 17:53:57.143

2014-12-03 18:20:29.487

Se a base sofrer mudança de disponibilidade (restart do servidor ou da base de dados via online/offline) os dados são resetados, pois são estatísticas voláteis. Como se tratam de estatísticas de uso do índice, não faz muito sentido que sejam resetados com rebuild, certo?

Quaaaaase. Essa informação não é sempre verdadeira, pois dependendo da versão do SQL Server, existe um bug que reseta as estatísticas de uso caso o índice sofra rebuild: veja este item do connect e como simular o problema e uma forma de reproduzir o erro. Reproduzi testes com as seguintes versões:

  • SQL Server 2008R2 SP3 (10.50.6000.34 )
  • SQL Server 2012 (11.0.2100.60 )
  • SQL Server 2014 ( 12.0.2402.0)

No SQL Server 2008R2, os dados não foram apagados após um rebuild. No SQL Server 2012 e 2014, os testes não tiveram a mesma sorte. Como não era um comportamento esperado, galera usuário do SQL Server chamou isso de bug e abriram um connect (com razão) e foi fechado como “não será consertado” porque a equipe quis assim e pronto, hehe.

Significa que caso você use essas versões do SQL Server e precise usar essa DMV, fique ligado neste detalhe.

Conclusão

Não tem muito segredo usar os DMO’s em questão, mas é importante saber alguns detalhes para que qualquer entendimento incorreto seja evitado!

Como de prática: feedback, sugestões, críticas e impressões sempre são bem vindas. Fique à vontade para comentar.

[]’s

Referências

  1. sys.dm_db_index_operational_stats
  2. sys.dm_db_index_physical_stats
  3. sys.dm_db_index_usage_stats
  4. DBCC SHOWCONTIG (antigo comando usado antes dos DMO’s, FYI)

 

Leave a Comment

Your email address will not be published. Required fields are marked *