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

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:

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:

USE MASTER
IF EXISTS (SELECT NAME FROM sys.databases WHERE name = 'TesteIndexacao') 
BEGIN
	ALTER DATABASE TesteIndexacao SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE TesteIndexacao;
END	

CREATE DATABASE TesteIndexacao
GO
USE TesteIndexacao
GO

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:

CREATE TABLE dbo.Pessoa
(
	ID INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
	NOME VARCHAR(30) NULL DEFAULT 'Pessoa ',
	ATIVO BIT NULL	DEFAULT 1
);
GO

-- INSERT: Mil inserts, cada um em um batch diferente... 

INSERT INTO dbo.Pessoa DEFAULT VALUES 
GO 1000

-- SELECT: Nossa primeira leitura na tabela (E é um scan) 
SELECT * FROM dbo.Pessoa

-- Vamos criar um índice nonclustered pra ter mais um exemplo de interpretação.
CREATE NONCLUSTERED INDEX idx_Nome ON dbo.Pessoa 
(
	NOME ASC
);
GO

-- UPDATE: Atualizando mil registros. 
UPDATE  dbo.Pessoa
SET NOME = NOME+CAST(ID AS VARCHAR(4))
GO

-- SELECT: Vamos fazer mais uma leitura na tabela (SCAN). Agora, copie o registro "Pessoa 2".
SELECT * FROM dbo.Pessoa
GO

-- SELECT: Criamos um índice para cobrir a consulta por nome. Que tal fazermos um seek agora? 
SELECT ID, NOME FROM dbo.Pessoa WHERE NOME = 'Pessoa 2'
GO

-- INSERT: Vamos adicionar mais algumas linhas agora (3 registros)
INSERT INTO Pessoa (NOME) VALUES ('Jubertina'),('Sara'),('Jurema')
GO
-- DELETE: Agora, vamos deletar a Jubertina (1 registro)
DELETE FROM Pessoa WHERE NOME = 'Jubertina'
GO

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:

-- Query para Operational Stats
SELECT 
	DB_NAME(DATABASE_ID) as [DatabaseName] , 
	object_name(O.object_id) as Objeto, 
	i.name as [NomeIndice], 
	leaf_insert_count,
	leaf_delete_count,
	leaf_update_count,
	leaf_ghost_count
FROM SYS.dm_db_index_operational_stats(DB_ID(),OBJECT_ID('PESSOA'),NULL,NULL) O 
INNER JOIN sys.indexes i ON o.object_id = i.object_id and o.index_id = i.index_id
GO

-- Query para Physical Stats 

SELECT 
	DB_NAME(DATABASE_ID) AS Database_name, 
	object_name(i.object_id) as [Tabela],
	i.name as [Índice],
	avg_fragmentation_in_percent, 
	avg_fragment_size_in_pages, 
	avg_page_space_used_in_percent, 
	record_count, 
	AVG_RECORD_SIZE_IN_BYTES 
FROM SYS.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('PESSOA'),NULL,NULL,'SAMPLED') o
INNER JOIN sys.indexes i ON o.object_id = i.object_id and o.index_id = i.index_id

GO

-- Query para Usage Stats

SELECT 
	DB_NAME(DATABASE_ID) AS Database_name,
	object_name(i.object_id) as [Tabela],
	i.name as [Índice], 
	user_scans,user_updates, 
	last_user_scan,
	last_user_update,
	last_system_scan
FROM SYS.dm_db_index_usage_stats o 
INNER JOIN sys.indexes i ON o.object_id = i.object_id and o.index_id = i.index_id
WHERE database_id = DB_ID('TesteIndexacao')  
GO

 

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).

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.

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.

..

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.

ALTER INDEX ALL ON dbo.Pessoa REBUILD

E o que muda com o rebuild?

Mesmo SELECT da [Operational Stats]

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]

Ó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]

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)