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

Parte 2 – Atualizando as Estatísticas de Banco de Dados

No post anterior (http://sqlserver-brasil.blogspot.com/2008/03/atualizao-das-estatsticas-de-banco-de.html) vimos que o Otimizador de consultas utiliza as informações das Estatísticas de Banco de Dados na escolha do plano de execução, sendo assim fundamental para se ter um bom desempenho mantê-las atualizadas. Se ocorrer uma grande alteração na distribuição de valores em uma coluna indexada e a estatística não for Atualizada, o Otimizador pode escolher um plano de execução menos eficiente.

O SQL Server disponibiliza uma propriedade de banco de dados chamada AUTO UPDATE STATISTICS (habilitada por padrão), que monitora a atualização nas tabelas e dispara um UPDATE STATISTICS quando o volume de alterações for grande. Mesmo com este recurso habilitado, um volume pequeno de alterações pode não disparar a atualização automática das estatísticas e fazer com que o Otimizador tenha como base valores desatualizados, escolhendo o plano de execução menos eficiente.

Por exemplo, vamos utilizar a tabela DETAILS da Parte 1 do artigo, onde a query abaixo retorna 301 linhas sendo mais eficiente o plano de execução com Index Seek e Boulkmark Lookup. Habilitando as estatísticas de I/O observamos:

SET STATISTICS IO ON
SELECT * FROM dbo.details WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 305

Agora vamos executar uma alteração na tabela modificando a quantidade de linhas com valores em SalesOrderID maiores que 75000.

UPDATE dbo.details SET SalesOrderID = 76000
WHERE SalesOrderID < color="#006600">-- 14.148 linhas alteradas

Esta alteração não provocou a atualização automática das estatísticas, pois representa um volume de linhas alteradas pequeno comparando com o total de linhas que a tabela possui 121.317 (11,66% de linhas alteradas). Executando a query novamente observamos que o Otimizador adotou o mesmo plano de execução (Index Seek com Bulkmark Lookup), porém neste caso o menos eficiente, já que agora o filtro WHERE seleciona 14.449 linhas.

SET STATISTICS IO ON
SELECT * FROM dbo.details WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 14479
-- Index Seek com Bulkmark Lookup

SELECT * FROM dbo.details with(index(0)) WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 994
-- Table Scan

Neste caso o plano de execução ideal seria o Table Scan, basta comparar o volume de páginas lidas (Logical Reads):
- 14479 páginas lidas com o plano Index Seek com Bulkmark Lookup.
- 994 páginas lidas com o plano Table Scan.

Repare o uso do Hint de tabela with(index(0)), obrigando o Otimizador a resolver a query com Table Scan!

Para atualizar as Estatísticas de Banco de Dados utilizamos à instrução UPDATE STATISTCS , atualizando todas as estatísticas de uma tabela. Executando a instrução abaixo o Otimizador passa a escolher o melhor plano, Table Scan:

UPDATE STATISTICS dbo.details

SET STATISTICS IO ON
SELECT * FROM dbo.details WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 994
-- Table Scan

Podemos concluir que é necessário atualizar as Estatísticas de Banco de Dados periodicamente, pois apenas a propriedade de banco de dados AUTO UPDATE STATISTICS não garante a freqüência ideal de atualização das estatísticas. O comando UPDATE STATISTICS deve ser executado para cada tabela, um modo mais simples é utilizar a Stored Procedure SP_UPDATESTATS que atualiza as estatísticas de todas as tabelas.

Até o proximo post.
Landry.