Estatísticas – Range_Scan, Singleton_Lookup e Forward_Fetch

Posted on maio 6, 2013

0


As estatísticas são utilizadas sempre que o Q.O (Query Optimizer) necessita saber qual melhor plano de execução a se utilizar para uma consulta, Porém, é somente o índice criando em uma coluna que interessa? Na verdade não, o Q.O se baseia em diversas outras informações por ser do que chamamos de Cost Based ou seja Custo Baseado, ele não somente verifica e utiliza se possível o índice em um campo, mas usa as estatísticas para provê informações como: frequência, distribuição, cardinalidade dentre outros.

Com isso, quando tentamos visualizar as estatísticas operacionais de um índice, possuímos informações cruciais para análise como: Quantidade de scans realizados no índice, quantidade de bookmark lookups e quantidade de páginas excedidas em uma tabela heap (Forwarding Pointers).

 

Range_Scan

Toda a vez que o índice realiza um SCAN em toda a tabela ou em um grande RANGE de informações.

 

USE AdventureWorks2012

 

go

 

 

 

 

SELECT OBJECT_NAME(ios.object_id) AS TableName,

 

                   i.name AS IndexName,

 

                   ios.range_scan_count

 

FROM sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID(‘Sales.SalesOrderDetail’),NULL,NULL) ios

 

INNER JOIN sys.indexes i

 

ON i.object_id = ios.object_id

 

AND i.index_id = ios.index_id

 

ORDER BY ios.range_scan_count DESC

 

 

image

(Figura 1 – Nenhuma estatística criada.)

 

Agora se realizarmos um TABLE SCAN na tabela veremos nessa DMV que:

 

SELECT *

 

FROM Sales.SalesOrderDetail

 

 

image

(Figura 2 – Table Scan na tabela Sales.SalesOrderDetail)

 

SELECT OBJECT_NAME(ios.object_id) AS TableName,

 

                   i.name AS IndexName,

 

                   ios.range_scan_count

 

FROM sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID(‘Sales.SalesOrderDetail’),NULL,NULL) ios

 

INNER JOIN sys.indexes i

 

ON i.object_id = ios.object_id

 

AND i.index_id = ios.index_id

 

ORDER BY ios.range_scan_count DESC

 

 

image

(Figura 3 – Range_Scan_Count = Table Scan na Tabela Sales.SalesOrderDetail)

 

Com isso as estatísticas foram atualizadas para 1 ou seja tivemos um TABLE SCAN nesta tabela.

 

Singleton_Lookup

Toda a vez que ocorrer um Bookmark Lookup ou seja o índice necessitar ir no índice primário para buscar o restante das informações especificadas no SELECT. Para cada busca no índice e incrementado o valor ou seja, se o índice for 50 vezes no índice o contador será de 50.

 

USE AdventureWorks2012

 

go

 

 

 

 

SELECT OBJECT_NAME(ios.object_id) AS TableName,

 

                   i.name AS IndexName,

 

                   ios.singleton_lookup_count

FROM sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID(‘Sales.SalesOrderDetail’),NULL,NULL) ios

 

INNER JOIN sys.indexes i

 

ON i.object_id = ios.object_id

 

AND i.index_id = ios.index_id

 

ORDER BY ios.range_scan_count DESC

image

(Figura 4 – Singleton_Lookup_Count, nenhuma busca no índice até o momento)

Realizando um BookMark Lookup veremos que:

SELECT *

 

FROM Sales.SalesOrderDetail

 

WHERE rowguid IN(’06A66921-6B9F-4199-A912-DDAFD383472B’,‘174A4AFD-391E-4C67-B99B-20BCFF9FA93D’)

 

image

(Figura 5 – BookMark Lookup no Índice PK_SalesOrderDetails)

 

SELECT OBJECT_NAME(ios.object_id) AS TableName,

 

                   i.name AS IndexName,

 

                   ios.singleton_lookup_count

FROM sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID(‘Sales.SalesOrderDetail’),NULL,NULL) ios

 

INNER JOIN sys.indexes i

 

ON i.object_id = ios.object_id

 

AND i.index_id = ios.index_id

 

ORDER BY ios.range_scan_count DESC

 

image

(Figura 6 – Singleton_Lookup_Count sendo incrementado)

 

O contador é mostrado como 2 porque na realidade especifiquei uma cláusula de busca com IN e assim foi retornado dois valores, fazendo com que o índice tivesse que realizar a busca 2 vezes.

 

Forwarded Fetch

A fragmentação em tabelas heaps faz com que tenhamos diversos Forwarding Pointers ou seja este contador é incrementado toda vez que essa operação ocorre.

 

USE tempdb

go

CREATE TABLE DadosFragmentationHeap

( ID UNIQUEIDENTIFIER CONSTRAINT PK_DadosFragmentationHeap_ID PRIMARY KEY NONCLUSTERED,

   Dados VARCHAR(3000) NULL )

GO

INSERT INTO DadosFragmentationHeap

SELECT NEWID(), ‘Luan Moreno’

GO 100

UPDATE DadosFragmentationHeap

       SET Dados = REPLICATE(‘a’,100)

 

SELECT OBJECT_NAME(ios.object_id) AS TableName,

 

                   i.name AS IndexName,

 

                   ios.forwarded_fetch_count

FROM sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID(‘DadosFragmentationHeap’),NULL,NULL) ios

 

INNER JOIN sys.indexes i

 

ON i.object_id = ios.object_id

 

AND i.index_id = ios.index_id

 

ORDER BY ios.range_scan_count DESC

image

(Figura 7 – Quantidade de Forward Pointers na tabela Heap.)

 

 

Conclusão

Toda a vez que você necessitar analisar informações como quantidade de scans, bookmark lookups de um índice ou ainda ver se há muita fragmentação em suas tabelas Heaps utilize essas DMV’s para te ajudar a solucionar esses problemas.  Ou seja, se você possui uma enorme quantidade de RANGE_SCANS quer provavelmente dizer que você necessita criar índice em suas tabelas, se ver diversos Singleton_Lookups então suas consultas não estão sendo totalmente “cobertas” pelos índices criados ou seja avalie melhor seus índices e se você possui muitos Fowarded Pointers, então realize um Rebuild no seu ambiente frequentemente.