Atualização das Estatísticas de Banco de Dados - Parte 1

Parte 1 – Entendendo o uso das Estatísticas de Banco de Dados

Venho trabalhando com consultorias e treinamento em SQL Server desde a versão 7.0, e um dos problemas mais comuns são as consultas de baixo desempenho. Um motivo freqüente para o baixo desempenho de uma consulta é ter Estatísticas de Banco de Dados desatualizadas, pois o Otimizador de consultas acaba selecionando um plano de execução menos eficiente.

Este artigo foi dividido em duas partes, na primeira veremos como o Otimizador de consultas utiliza a Estatística de Banco de Dados para elaborar o plano de execução. Na segunda parte do artigo vou mostrar o prejuízo que uma Estatística desatualizada causa no desempenho de uma consulta.

As Estatísticas de Banco de Dados fornecem informações valiosas ao Otimizador, orientando a escolha do plano de execução de uma query. Ao criar um índice o SQL Server cria automaticamente uma estatística associada, contendo a distribuição dos valores da primeira coluna da chave do índice. Quando a propriedade de banco de dados AutoCreateStatistics está com TRUE (configuração padrão), o Otimizador cria a estatística sempre que detectar sua ausência durante a elaboração de um plano de execução.

Um dos componentes principais das Estatísticas de Banco de Dados é a distribuição de valores da coluna chave, onde é armazenada a quantidade de linhas contendo cada valor individual (se existem muitos valores individuais o SQL Server divide estes valores em intervalos e armazena o total de ocorrência dentro do intervalo).

Vamos observar o uso da estatística na prática criando a tabela abaixo e incluindo algumas linhas:

USE AdventureWorks
GO
IF OBJECT_ID('dbo.details', 'U') IS NOT NULL
DROP TABLE dbo.details
GO
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,
OrderQty, ProductID,SpecialOfferID, UnitPrice,
UnitPriceDiscount, ModifiedDate
INTO dbo.details
FROM AdventureWorks.Sales.SalesOrderDetail

CREATE NONCLUSTERED INDEX idx_nc_col2
ON dbo.details(SalesOrderID)
GO

Foi criado um índice na tabela DETAILS utilizando como chave a coluna SALESORDERID, sendo criada uma estatística de banco de dados para esta coluna. Vamos agora trilhar o caminho que o Otimizador utilizou para elaborar o plano de execução da query:

SELECT * FROM dbo.details WHERE SalesOrderID > 75000

Existem dois planos que o Otimizador poderá escolher:

1) Table Scan verificando o filtro WHERE linha a linha
2) Index Seek para resolver o filtro WHERE e depois Bookmark Lookup para recuperar as demais linhas da tabela que não se encontram no índice (veja que no SELECT temos *, isto é, todas as colunas).

Vamos contabilizar o custo de I/O (Imput/Output - leituras de página de dados e índice) necessário para resolver cada plano acima. Para determinar o Plano 1 (Table Scan), o SQL Server recorre ao catálogo para identificar quantas páginas de dados a tabela está ocupando, utilizando a query abaixo:

SELECT rows as QtdLinhas, data_pages Paginas8k
FROM sys.partitions p JOIN sys.allocation_units a
ON p.hobt_id = a.container_id
WHERE p.[object_id] = object_id('details')
and index_id in (0,1)

Resultado:
QtdLinhas: 121317
Paginas8k: 994

Desta maneira o Otimizador identificou que para resolver a query com o Plano 1 (Table Scan) serão necessárias 994 leituras de páginas de 8kb (páginas de dados – data pages).

Para contabilizar o I/O do Plano 2 (Index Seek + Bulkmark Lookup) o Otimizador identifica quantas linhas serão retornadas pelo filtro WHERE SalesOrderID > 75000, para isso ele utiliza a Estatística de Banco de Dados criada quando o índice na coluna SalesOrderID foi gerado. Para visualizar uma Estatística de Banco de Dados o SQL Server disponibiliza a instrução DBCC SHOWSTATISTICS.

DBCC SHOW_STATISTICS ('dbo.details','idx_nc_col2')

Executando esta instrução obtemos o resultado abaixo:



Em azul temos a data e hora que a estatística foi atualizada, informação importante como vocês verão mais a frente. Em vermelho a coluna Steps determina a quantidade de faixas de valores geradas no terceiro e último Grid (o terceiro Grid possui 143 linhas). Se você rolar este Grid até o final veremos os valores abaixo:



Vamos analisar o resultado utilizando as duas últimas linhas em vermelho:

RANGE_HI_KEY – representa o último valor de cada faixa, por exemplo: na linha 142 (em vermelho na figura acima) temos o valor 75122 representando a faixa que tem início no valor 74661 (o valor seguinte da linha 141 valor 74660) até o próprio 75122.

RANGE_ROWS – quantidade de linhas que possuem valores iguais ao da faixa, excluindo o último valor em RANGE_HI_KEY, por exemplo: na linha 142 (em vermelho na figura acima) temos a faixa de 74661 até 75122, porém 1077 representa a quantidade de linhas contendo os valores de 74661 até 75121. O Otimizador não tem como saber a exata distribuição das ocorrências dentro da faixa, porém as demais colunas fornecem uma boa idéia desta distribuição!

EQ_ROWS – quantidade de linhas que possuem o último valor da faixa, definido em RANGE_HI_KEY, por exemplo: na linha 142 (em vermelho na figura acima) podemos afirmar que existem duas linhas na tabela com o valor 75122.

DISTINCT_RANGE_ROWS – quantidade de valores dentro da faixa (SELECT DISTINCT dentro do intervalo da faixa), por exemplo: na linha 142 (em vermelho na figura acima) existem 461 valores dentro da faixa (75122 - 74661 = 461). Observamos que é um seqüencial sem pular valor!

AVG_RANGE_ROWS – igual a RANGE_ROWS / DISTINCT_RANGE_ROWS, por exemplo: 1077 / 461 = 2.336226.

Agora, utilizando a estatística da Figura 2, podemos ter uma idéia aproximada da quantidade de linhas retornadas pelo filtro WHERE SalesOrderID > 75000:

Linha 142 da figura acima: 75121 – 75000 = 121 valores dentro da faixa, em AVG_RANGE_ROWS temos 2.336226 multiplicando por 121 encontramos 282.683346. Somando as duas linhas em EQ_ROWS referente ao valor 75122, temos um total de 284.683346

Linha 143 da figura acima: 3 linhas com valor 75123.

O Otimizador concluiu então que seriam retornadas aproximadamente 287.683346 linhas com o filtro WHERE SalesOrderID > 75000, gerando 287 Bulkmark Lookups para as páginas de dados. Somando-se alguns poucos I/Os para navegar no índice (Index Seek), ficou bem a baixo do Plano 1 (Table Scan) com um total de 994 páginas.

Reparem que analisando a estatística o Otimizador errou por pouco a previsão da quantidade de linhas 287, onde na verdade a query retornou 301 linhas gerando 305 I/Os (301 páginas no Bulkmark Lookups e 4 páginas no Index Seek).



Obs.: No SQL Server 2005 o Bulkmark Lookup aparece no plano de execução composto por duas fases: Nested Loops e RID Lookup (se a tabela não tem índice Cluster) ou Cluster Index Seek (se a tabela tem índice cluster) até SP1, no SP2 aparece como Key Lookup. No SQL Server 2000 aparecia uma única fase chamada de Bulkmark Lookup.

Agora que já conhecemos as estatísticas de Banco de Dados, veremos no proximo post a importância de mantê-las atualizadas, até lá!
Landry.