Live Query Statistics e Live Execution Plan

Uma das atividades mais comuns em consultoria é a análise de consultas de baixo desempenho, problema sério que afeta diversas aplicações.  O SQL Server 2016 trouxe um importante aliado para identificar gargalos no desempenho de consultas, Live Query Statistics.

No Management Studio este recurso aparece no menu Query opção Include Live Query Statistics.

Quando habilitamos a opção acima, o Management Studio utiliza a visão sys.dm_exec_query_profiles para obter as informações de execução e mostrar graficamente. 

As permissões SHOWPLAN no banco de dados e VIEW SERVER STATE na instância são necessárias para utilizar Live Query Statistics.

Segue exemplo abaixo:

O Management Studio mostra o progresso da execução como na figura acima, ficando claro as etapas que representam gargalos na execução. 

CUIDADO!!! Live Query Statistics NÃO substitui Actual Execution Plans, pois existem algumas limitações:

- A consulta roda mais lenta quando Live Query Statistics está habilitado.

- Alertas de uso da TEMPDB não aparecem em Live Query Statistics.

- Uso de índices Columnstore não aparecem em Live Query Statistics.

- Consultas em Memory Optimized Tables não são suportadas por Live Query Statistics.

- Natively Compiled Stored Procedures não são suportadas por Live Query Statistics.

Uma boa notícia é que Live Query Statistics pode ser utilizado no SQL Server 2014 SP1.

CONCLUSÃO

Live Query Statistics é uma alternativa interessante para analisar consultas de média e longa duração, identificando claramente etapas que representam gargalos importantes.

Até o próximo post.

Saudações Tricolores,

Landry

Filtered Index in SQL Server 2008

Neste post vou falar de um recurso novo no SQL Server 2008 para melhorar o desempenho das consultas: Filtered Index. A partir do SQL Server 2008 pode-se acrescentar ao comando CREATE INDEX uma cláusula WHERE, filtrando linhas da tabela e reduzindo o tamanho do índice.

Para mostrar este recurso utilizei como exemplo a tabela “Production.TransactionHistory” do banco de dados AdventureWorks2008. A consulta abaixo utiliza um “Cluster Index Scan” (leia-se “Table Scan”, pois no nível folha do índice cluster temos a própria tabela) como plano de execução.

set statistics profile on
set statistics io on

select BusinessEntityID,FirstName,MiddleName,LastName,ModifiedDate
from Person.Person where PersonType = 'VC'

-- Cluster Index Scan
-- Table 'Person'. Scan count 1, logical reads 3816O índice ideal para atender a consulta acima seria um Nonclustered Index com a chave na coluna PersonType e Include nas demais colunas do SELECT, veja:

create index IX_Person_PersonType on Person.Person(PersonType)
include (FirstName,MiddleName,LastName,ModifiedDate)
Se executarmos a consulta acima novamente o SQL Server fará Index Seek, reduzindo consideravelmente o I/O.

-- Index Seek (IX_Person_PersonType)
-- Table 'Person'. Scan count 1, logical reads 4
A partir do SQL Server 2008 pode-se criar índices menores, especializados na cláusula WHERE de uma consulta, gerando redução do espaço ocupado. Basta acrescentar ao final de um CREATE INDEX uma cláusula WHERE:

create index IX_Person_PersonType_Filter on Person.Person(PersonType)
include (FirstName,MiddleName,LastName,ModifiedDate)
where PersonType = 'VC'
Se executarmos a consulta do início do artigo novamente o SQL Server irá gerar o mesmo plano de execução, porém utilizando o novo índice:

-- Index Seek (IX_Person_PersonType_Filter)
-- Table 'Person'. Scan count 1, logical reads 4
Comparando os dois índices com a instrução abaixo, podemos observar a redução no tamanho do índice de 137 para 4 páginas de 8k.

select i.name Indice,used_page_count,row_count
FROM sys.dm_db_partition_stats P INNER JOIN sys.indexes I
ON I.index_id = P.index_id AND I.OBJECT_ID = P.OBJECT_ID
WHERE p.OBJECT_ID = OBJECT_ID('Person.Person')
Até o próximo post,
Landry.

Clone de Banco de Dados para analisar plano de execução

Versões do SQL Server: 2005 e 2008.

Olá... o post de hoje é muito útil para quem trabalha com tuning de consultas, principalmente consultores!

Você é um consultor e precisa melhorar o desempenho de algumas consultas... Você terá que trabalhar no cliente porque este possui um banco de dados de 400GB de informações sigilosas! Bem, fazer Backup e levar para casa (ou escritório) é inviável devido ao tamanho do banco de dados e a necessidade de sigilo das informações. Uma alternativa é fazer um Clone do Banco de Dados levando a estrutura e as estatísticas de banco de dados, sem levar os dados. Um processo muito mais rápido que Backup e Restore, ocupa menos espaço em disco e resolve o problema do sigilo pois não leva os dados.

Para fazer um clone do Banco de dados basta entrar no Management Studio (SSMS), conectar na instância que contém o banco, expandir “Databases”, clicar com o botão direito no banco, selecionar “Tasks” e “Generate Scripts”.


Selecione o banco de dados e marque a opção “Script all objects in the selected database” como na figura abaixo:


Na próxima tela “Choose Script Options” altere algumas propriedades utilizando a tabela abaixo:


Na última tela gere o script para um arquivo qualquer, veja:


O próximo passo é editar o arquivo gerado... No início do arquivo, no comendo CREATE DATABASE altere o parâmetro SIZE do arquivo de dados e log para valores menores, pois não vamos ter os dados neste banco! Se for necessário altere a localização dos arquivos.

Agora basta executar o script (você verá alguns de GRANT para usuários inexistentes). Para conferir as estatísticas execute o DBCC SHOW_STATISTICS (,) para conferir! As tabelas estão vazias, mas as estatísticas refletem os dados da produção!

Pronto agora é só analisar as consultas, pois os planos de execução serão os mesmos da produção!

Até o próximo post,
Landry.

Melhorando o Desempenho das Consultas com INCLUDE

Estava preparado para finalizar o artigo sobre o novo tipo de dado hierárquico do SQL Server 2008, quando ao tentar entrar no SQL Profiler recebi a mensagem indicando que o SQL 2008 CTP havia expirado (ainda não instalei o CTP de Fevereiro 2008 no notebook)! Como estou ministrando o curso 2784 (tuning de consultas), resolvi escrever sobre um recurso novo no SQL Server 2005, a cláusula INCLUDE do CREATE INDEX.

Vamos criar uma tabela a partir da VIEW vIndividualCustomer do banco de dados AdventureWorks para utilizar nos exemplos deste artigo.

use TEMPDB
go
select CustomerID,FirstName,MiddleName,Lastname,
Phone,EmailAddress,AddressLine1 as Address,'RJ' as Region,
dateadd(d,-CustomerID,getdate()) DataCadastro
into customer
from AdventureWorks.Sales.vIndividualCustomer

Quando criamos um índice nonclustered o SQL Server constrói uma Árvore B preenchendo todos os níveis (raiz, intermediário e folha) com as colunas que compõe e chave. Para atender a consulta abaixo, vamos criar um índice nonclustered com chave composta FirstName, LastName e Phone, evitando a necessidade de acessar a tabela:

create nonclustered index IXCustomer_FirstName on
Customer(FirstName,LastName,Phone)

Agora vamos executar a consulta abaixo, habilitando o plano de execução gráfico (menu query opção “Include actual execution plan”) e as estatísticas de I/O:

set statistics io on
go
select FirstName,LastName,Phone from Customer
where FirstName = 'David'
-- Table 'customer'. Scan count 1, logical reads 4

Veja no comentário do script que foram lidas quatro páginas para navegar no índice e retornar o resultado! A busca binária foi feita utilizando o filtro WHERE na coluna FirstName, já as colunas LastName e Phone serviram apenas para compor o resultado final. Para esta consulta, as colunas LastName e Phone não precisam estar no nível raiz e intermediário do índice, já que não foi feita pesquisa binária no índice com elas.

A partir do SQL Server 2005 você poderá reduzir I/O de consultas utilizando a cláusula INCLUDE, retirando as colunas que não serão utilizadas na busca binária do nível raiz e intermediário do índice, gerando um índice menor, com conseqüente melhora no desempenho. As colunas definidas na cláusula INCLUDE são acrescentadas ao nível folha do índice.

Vamos agora alterar o índice movendo as colunas LastName e Phone da chave para a cláusula INCLUDE:

create index IXCustomer_FirstName_LastName_Phone
on Customer(FirstName) INCLUDE (LastName,Phone)
with drop_existing

select FirstName,LastName,Phone from Customer
where FirstName = 'David'
-- Table 'customer'. Scan count 1, logical reads 2

Repare que o I/O da consulta foi reduzido pela metade!!!

Outra vantagem da cláusula INCLUDE é o limite de 1023 colunas, diferente da chave que contém um limite muito menor de 16 colunas ou 900 bytes! Alguns tipos de dados que não podem fazer parte da chave, como VARCHAR(MAX), mas podem compor o INCLUDE.

Ate o próximo post,
Landry.

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.

Missing Index (Índices Ausentes)

Neste post vou falar sobre um recurso novo no SQL Server 2005 chamado Missing Index.

Quando o otimizador elabora um plano de execução, ele analisa os melhores índices para uma condição de pesquisa. Se os melhores índices não forem encontrados o otimizador gera um plano que não seria o ideal, porém registra a ausência destes índices. O otimizador só gera informações sobre os índices ausentes, para queries com cláusula WHERE e que não foram resolvidas utilizando Plano Trivial (Trivial Plane).

Estas informações são mantidas até o SQL Server reiniciar. Nas versões RTM e SP1 são armazenados até 500 índices ausentes, alcançando o limite o otimizador para de registrar. No SP2, quando o limite de 500 é alcançado, o otimizador passa a apagar 20% dos índices menos relevantes para dar espaço a novos índices ausentes.

Cada indice ausente pertence a um grupo de índices ausentes, porém no SQL Server 2005 existe relação de 1-1 entre grupo de índices e índices. Em edições futuras a Microsoft pretende agrupar índices que resolveriam uma query, facilitando a análise de queries complexas que utilizam vários índices.

O SQL Server expõe as informações dos índices ausentes em 3 DMVs (Dynamic Views) e uma função:

► sys.dm_db_missing_index_details
Esta view retorna uma linha para cada índice que o otimizador não encontra ao elaborar o plano de execução de uma query. Ela retorna a lista de colunas que devem ser utilizadas como chave e Include.

► sys.dm_db_missing_index_group_stats
Atualizada a cada execução de query (e não a cada compilação), retornando informações consolidadas sobre grupos de índices ausentes (no SQL 2005 existe uma relação de 1-1 entre grupo de índice e índice).

► sys.dm_db_missing_index_groups
Relaciona cada índice em sys.dm_db_missing_index_details com um grupo de índices em sys.dm_db_missing_index_group_stats.

► dm_db_missing_index_columns
Função que retorna uma tabela com a lista de colunas (chave e Include) que compõe um índice ausente.

Para observar os índices ausentes vamos criar duas tabelas no banco de dados TEMPDB com o script abaixo:

USE tempdb
go
SELECT * INTO dbo.OrderHeader FROM Adventureworks.Sales.SalesOrderHeader;
SELECT * INTO dbo.Customers FROM Adventureworks.Sales.Customer;
go

Agora vamos executar uma query com JOIN utilizando as tabelas criadas no script anterior. Reparem que estas tabelas não possuem índices.

SELECT SalesOrderID, OrderDate, [Status], h.CustomerID, c.AccountNumber
FROM dbo.OrderHeader h JOIN dbo.Customers c ON h.CustomerID = c.CustomerID
WHERE c.TerritoryID = 2

Executando a instrução abaixo, utilizando a DMV sys.dm_db_missing_index_details, podemos observar os índices ausentes gerados pelo otimizador de consultas.

select index_handle, object_name(object_id) as 'Tabela',
equality_columns,inequality_columns,included_columns
from sys.dm_db_missing_index_details
where database_id = db_id('tempdb') and
[object_id] in (object_id('dbo.OrderHeader'),object_id('dbo.Customers'))

A função sys.dm_db_missing_index_columns retorna a relação de colunas de um índice ausente. Ela recebe como parâmetro do handle do índice obtido na coluna index_handle da DMV anterior.

SELECT * FROM sys.dm_db_missing_index_columns(11);

Implementando um JOIN entre as três DMVs obtemos mais informações, como a coluna user_seeks que retorna um contador incrementado a cada query executada que utilizaria o índice ausente.

SELECT d.*,s.* FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
where database_id = db_id('tempdb') and
[object_id] in (object_id('dbo.OrderHeader'),object_id('dbo.Customers'))

No próximo post vou escrever sobre como associar um índice ausente e uma query utilizando outro recurso novo do SQL Server 2005 que é plano de execução em XML.