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.

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.

SQL Server 2005: Executando relatórios customizados no Management Studio

Um engano comum dos meus alunos dos cursos Oficiais de SQL Server é achar que Service Pack serve apenas para resolver problemas. Além de resolver problemas um Service Pack pode acrescentar e retirar funcionalidades, tornando a leitura da documentação obrigatória antes da sua adoção em produção.

O Service Pack 2 do SQL Server 2005 é um bom exemplo, pois além de corrigir problemas incluiu duas funcionalidades novas: o novo tipo de dados VARDECIMAL e os Relatórios customizados no Management Studio.

Os relatórios de sistema disponíveis no Management Studio do SQL Server 2005 fizeram a alegria de muitos administradores de banco de dados, ficando fácil obter informações valiosas do servidor em um formato eficiente e elegante.

A partir do Service Pack 2 temos agora como acrescentar novos relatórios desenvolvidos com o Reporting Service, gerando um arquivo .RDL e registrando no Management Studio.Vamos observar primeiro a alteração de interface que ocorreu no Service Pack 2 na visualização dos relatórios. Até o SP1 os relatórios eram acessados selecionando um item no Object Explorer, por exemplo um banco de dados, e localizando na janela “Summary” o item “Report”, veja na Figura abaixo.

Já no Service Pack 2 o item “Report” passou da janela “Summary” para o menu de contexto no próprio Object Explorer, como podemos observar na Figura abaixo.

Outra novidade, em destaque na Figura acima (em vermelho), é a possibilidade de acrescentarmos novos relatórios desenvolvidos no Reporting Service. Basta clicar em “Custom Reports” e localizar o arquivo .RDL que o seu relatório será executado e irá aparecer na lista.

É possível customizar os relatórios com parâmetros de entrada de acordo com o objeto que você acionou no menu de contexto. Por exemplo, se você clicar com o botão direito em um banco de dados e executar um relatório, este poderá utilizar o nome do banco como parâmetro. Segue abaixo a relação dos parâmetros que podem ser utilizados:

O relatório da Figura abaixo utilizou o parâmetro “DatabaseName” como filtro da query:

Select top(12) convert(char(10),Data,103) as Data, Tamanho_MB,EspacoUtil_MB
from msdb.Crescimento.OCUPACAODB

where NomeDB = @DatabaseName order by Data desc

Utilizando no relatório um parâmetro com o mesmo nome da tabela de parâmetros acima, o Management Studio executa o relatório passando o valor referente ao item selecionado no menu de contexto. Na Figura acima o relatório recebeu como parâmetro o nome do banco de dados selecionado no Object Explorer (DW_SQL2005).

Para retirar o relatório do menu de contexto basta excluir o arquivo .RDL e tentar executar o relatório, após o erro o Management Studio irá retirar o nome do relatório da lista automaticamente.

Até o próximo post.

Landry.

Executando scripts em múltiplas instâncias no SQL Server 2008

Este artigo foi escrito utilizando o CTP de Novembro do SQL Server 2008.

O novo recurso de execução de scripts em múltiplas instâncias do SQL Server 2008 vem atender as necessidades de muitos administradores. Já passei por situações onde fui obrigado a abrir várias sessões em instâncias diferentes no SQL Server para executar o mesmo script. Em uma empresa que presto consultoria foi desenvolvido um pequeno programa em VBNET para executar scripts em várias instâncias do SQL Server, previamente cadastradas.

Para ser possível a execução de scripts em várias instâncias basta registrar um grupo de servidores na janela “Registered Servers”, e depois abrir uma janela de query a partir do grupo de servidores (figura abaixo) no Management Studio.

No meu ambiente de teste tenho uma instância de SQL Server 2008 chamada SRV2008 e uma instância de SQL Server 2000 chamada MIAMI. Executei a query abaixo que produziu a resposta que aparece na figura abaixo:

Select Name as DatabaseName,Dbid from sysdatabases

Repare na figura acima que foram acrescentadas ao resultado duas colunas que não foram solicitadas na query (“Server Name” e “Login”). No menu Tools\Options você poderá desabilitar a visualização destas colunas, assim como separar o resultado por instância, veja:

Na query abaixo ordenei o resultado pelo nome do servidor e ID do banco de dados:

select @@SERVERNAME ServerName,Name as DatabaseName,Dbid from sysdatabases order by 1,3

Neste último exemplo estou criando uma tabela Teste e incluindo 3 linhas utilizando uma nova opção do INSERT, onde em VALUES estou incluindo valores para 3 linhas.

create table Teste (col int)

insert Teste values (1), (2), (3)

Este script irá falhar na instância Miami, por ser SQL Server 2000, porém na instância SRV2008 foram incluídas as 3 linhas, veja:

Acredito que este recurso será muito utilizado no SQL Server 2008, não sendo mais necessário utilizar ferramentas de terceiros para executar scripts em múltiplas instâncias. Até o próximo post.

Landry.