Archive

Posts Tagged ‘Tunning SQL Server’

Create Index não é Tuning II

Esse post é dedicado a um grande amigo que me ensinou muito sobre Tuning e Performance um dos melhores DBAs que conheci, Sergio Bonsague.

Esse artigo é continuação do último post lançado aqui no blog. A coisa mais divertida no SQL Server é saber, porque ele fez isso ou aquilo? Por que “tal” decisão foi tomada, entender um pouco desse mundo obscuro…rs. Vamos parar de tagarelar e colocar a mão na massa.

Se analisarmos o plano de execução do post print, o SQL Server escolheu utilizar um Clustered Index Seek para ler toda a tabela. Porém, fica a pergunta, Ele não poderia ter escolhido um Clustered Index Scan? Como vou ler tudo, um Scan não é melhor que um Seek? A resposta é: Depende.

Executando duas vezes a mesma query, uma com o HINT WITH(INDEX=0) que força a Não utiliza do Index da tabela e uma sem utilização de hint, para que possamos comparar o antes e depois.

 

Executando a query forçando um Clustered Index Scan Vs um Clustered Index Seek podemos ver que no plano de execução o custo de ambos é  de 50% (Atual e Estimado).

ambosPlano

 

Thiago, que dizer que é a mesma coisa? A resposta é não. Quando se está avaliando performance, o ideal também é utilizar como parâmetro os tempos de execução, para isso sempre uso o Profiler. A quantidade de leitura é muito menor quando utilizamos um Clustered Index Seek, Vejamos o resultado:

EvidenciaProfiler

 

O SQL Server realizou uma quantidade menor de leituras utilizando o Index Seek. Mas, porque?

No Seek temos duas propriedades que não aparecem no Scan que no caso é a “Direção da Leitura”, como pode ser visto utiliza o “FORWARD” e está marcada como “ORDERED” como True.

ClusteredIndexSeek

 

Já o Clustered Index Scan, essas propriedades não são apresentadas. É a única questão que acredito ser o motivo.

ClusteredIndexScan

 

 

Esperam que vocês tenham gostado.

 

O que é o DTA?

Amigos da comunidade, blz? Esse post é dedicado a um outro amigo de trabalho. O DBA Felipe Melo , conhecimento SQL Geek em replicação, tunning e T-SQL. Sempre trocamos uma “figurinha” nos desafios profissionais, afinal, sempre é bom ter uma outra opinião. A certeza é: Nunca estamos totalmente certos! Mão na massa!?

O Database Engine Tuning Advisor é uma das “novas” features do SQL Server, ele analisa arquivos de carga de trabalho e propõe alterações no banco de dados, a fim de melhorar seu desempenho geral.

Para as alterações propostas, o Tuning Advisor também mostra o impacto que causará cada modificação.

Entre as suas capacidades, estão:

  • Query Optimazer, para propor índices e visões indexadas;
  • Recomendação de particões;
  • Análise de impacto das recomendações;
  • Fornecimento de informações sobre o número de consultas e o número de índices.

Opções de ajuste:

  • Quais objetos o Tuning Advisor poderá recomendar;
  • Quais partições analisará;
  • Quais estruturas serão mantidas no banco de dados;
  • Espaço máximo para recomendações;
  • Número máximo de columas por índice.

Criando analises com o DTA

Para iniciar o DTA, vá em StartAll / Programs / Microsoft SQL Server 2008 / Performance Tools / DataBase Engine Tunning Advisor.

Quando o programa for iniciado, clique em File/New Connection.

Aparecerá a tela de conexão para qual servidor você deseja criar a análise

Obs: Para o exemplo será usado um ambiente de testes.

O servidor será mostrado no canto superior direito da tela, clique com o botão direito e escolha a opção New Session.

A seguinte tela será mostrada:

Clique na guia Tunning Options e será mostrado a seguinte tela:

Physical Design Structure(PDS) to use in database: Esta opção avalia o que a Engine do DTA irá analisar referente a objetos de design na estrutura do banco(índex,índex views e etc). A opção de Índex views é desabilitada no SQL Server STD.

Partitioning strategy to employ: A opção de particionamento, verifica se existem objetos que podem ser particionados e avalia o particionamento que já existente. Disponível somente na versão Enterprise do SQL Server.

Physical Design Structure(PDS) to keep in database: Avalia a estrutura fisica do banco, abalia se existem índices clustereds e nonclustereds devem ser deletados(ele não deleta os indices), ou se existe algo divergente na no modelo como um todo. O padrão é Keep All existing PDS.

Voltando a guia General, aonde:

  • Session Name: Nome da Sessão
  • WorkLoad: A origem de ondes virão os dados que serão analisados(no caso do profiler o mesmo pode ser um arquivo ou uma tabela)
  • DataBase for WorkLoad Analysis: Aonde serão gaurdadas as analises temporarias.
  • Select DataBase and tables to tune: Lista de banco de dados e de tabelas que serão parte da analise. (sempre coloque os bancos de dados que [*] foram filtrados no profiler)

Coloque o nome no profiler e escolha o caminho aonde está o arquivo de trace do profiler. Ao clicar no Radio Button File, clique no binóculo a esquerda e será aberta a tela do Windows. Escolha o profiler que deseja analisar.

Clique em abrir.

Caso o trace tenha sido guardado em uma tabela, marque o Radio Button Table, clique no binóculo a esquerda e será aberta a tela de conexão:
Escolha qual banco, schema e tabelas estão os profilers que foram armazeandos.

Obs:  O login qual abriu a sessão no DTA deve ter acesso as tabelas que estão os traces, caso contrário, uma mensagem de erro será retornada.

 

O preenchimento deve ficar parecido com a tela abaixo:

 

Clique no botão Start Analysis. Após clicar em Star Analysis, uma tela de progresso será exibida.

 

Na guia recomendações, é apresenta a porcentagem de melhoria da análise.

 

No menu Actions aparecerá a opção de aplicar as recomendações ou savá-las.
Clique em Save Recommendations

Será gerado um arquivo .sql com as recomendações.

Espero Ter ajudado

 

Removendo Cursor – Dica Rápida

Ola Galera, blz? Como o tempo está um pouco corrido essa vai ser uma dica rápida que pode ser usada no dia-a-dia.
Imaginem o cenário que é necessário criar uma string com diversos ID’s. E esses ID’s estão dentro de uma tabela. A idéia inicial seria realizar um loop para incrementar uma variavel e montar a nossa string.. Vamos ao exemplo:
O script abaixo cria uma tabela com os códigos do cliente que iremos usar para montar a nossa string.
use tempdb
go
if OBJECT_ID(‘dbo.tb_cliente’) is not null
drop table dbo.tb_cliente
go
create table dbo.tb_cliente(id int identity, codigoCliente int , nome varchar(50))
go
–Bloco que popula a tabela com informações dos clientes
set nocount on
declare @i int = 1
while @i <= 100
begin
insert into dbo.tb_cliente(codigoCliente,nome)values(@i,replicate(‘Thiago’,2))
set @i = @i + 1 *(2)
end
go

Declaramos as variaveis @count, @total para realizar o nosso loop. As variáveis @idCliente e @idClienteString serão usadas para armazenar os codigos concatenados. A tabela temporaria #CodigosCliente é usada para guardar as informações pertinentes ao predicado IN. O script abaixo preenche a tabela temporária com os registros que serão montados para o loop. Após a inserção, o total de registros e colocado em uma variável para que iniciemos a montagem da string.
declare @count int , @total int
declare @idCliente varchar(max), @idClienteString varchar(max)
create table #CodigosCliente(id int identity, codigoCliente int)
–Popula a temporaria com as informacoes que sera colocadas em uma string
insert into #CodigosCliente(codigoCliente)
select codigoCliente
from dbo.tb_cliente where codigoCliente between 10 and 50

set @idCliente = ‘ ‘
set @idClienteString = ‘ ‘
select  @total =count(id) from #CodigosCliente
set @count = 1
if @total > 0
begin
set @idClienteString = ‘codigoCliente IN ( ‘
end
else
begin
set @idClienteString = ‘codigoCliente IN (0 ‘
end
while @count <= @total
begin
if  @count > 1
begin
set @idClienteString = @idClienteString + ‘, ‘
end
select @idCliente=codigoCliente from #CodigosCliente where id=@count
set @idClienteString = @idClienteString + @idCliente
set @count = @count + 1
end
set @idClienteString = @idClienteString + ‘ )’
print @idClienteString
go
Após a execução do script acima, o SQL Server irá montar o predicado in com os valores, conforme imagem:

Conforme imagem acima, temos o resultado esperado, porém, operações linha-a-linha são mais custosas para o SQL Server. Nós poderíamos obter o mesmo resultado com o script abaixo:

declare @idCliente varchar(max )
declare @idClienteString varchar(max)
SET @idCliente =
SET @idClienteString =
SET @idClienteString = ‘codigoCliente IN (‘
SELECT @idCliente=@idCliente +‘,’+CONVERT(varchar(max),codigoCliente)from#CodigosCliente
SELECT @idCliente =substring(@idCliente,2,LEN(@idCliente))
SET @idClienteString = @idClienteString + @idCliente +‘)’
PRINT @idClienteString
Como podemos ver, temos o mesmo resultado com uma menor quantidade de linhas uma instrução única evitando o uso de um loop.

Créditos: Alexandre José Malachias….Valeu Boss

Constraints vs Query Performance

Neste post irei demonstrar a importancia de  criar constraints no SQL Server. Graças as Foreign keys e check constraints o optimizer pode criar planos mais eficientes para as querys. Dado o script abaixo da criação das tabelas temos a tabela de Customers e de Orders. Na modelagem proposta que dizer que um Customer pode ter uma ou mais Orders. Notem que o script de criacao da constraint fisica nessa tabela e feita na tabela “filha” no nosso caso a de Orders.
CREATE TABLEdbo.Customers (CustomerID INT  PRIMARYKEY)
CREATE TABLE dbo.Orders(OrderID INT  PRIMARYKEY,CustomerID INT NOT  NULL CONSTRAINT FKOrdersCustomers REFERENCES dbo.Customers(CustomerID)).
Executando a query abaixo, podemos notar que temos duas tabelas na consulta, mas se vc executar a query com o plano de execução o sql server uma apenas o operador fisico para acessar uma das tabelas. Esse compportamento acontece, pois, o otimizador sabe que não é necessario executar um teste de existencia dos registros, pois a FK garante que serao requeridas todas orders para referenciar com o customer. Conforme print abaixo:
SELECT O.OrderID,o.CustomerID
FROM dbo.Orders AS o
WHERE EXISTS(SELECT CustomerID FROM dbo.Customers AS c
WHERE c.CustomerID=o.CustomerID)

Agora o que aconteceria se desabilitarmos a constraint? Vamos aos testes (Amo muito tudo isso..rs)
ALTER TABLE dbo.Orders NOCHECK CONSTRAINT FKOrdersCustomers

Execute novamente  a query, pressione o CTRL+M antes para habilitar o Include Actual Execution Plan. Agora perceba que  o plano de execucao mudou, isso porque a constraint foi desabilitada e o SQL Server nao pode garantir que todas as Orders  tem um customer valido.
SELECT O.OrderID,o.CustomerID
FROM dbo.Orders AS o
WHERE EXISTS(SELECT CustomerID FROM dbo.Customers AS c WHERE c.CustomerID=o.CustomerID)

Para voltarmos ao plano anterior devemos habilitar novamente a constraint, conforme instrução abaixo:
ALTER TABLE dbo.Orders CHECK CONSTRAINT FKOrdersCustomers

Execute a query novamente e veja o plano de execucao. Note que o plano de execucao continua o mesmo de quando a constraint foi desabilitada.

Esse comportamento se da ao devido fato que: O SQL server nao pode garantir que nao foi inserido um registro nao valido enquanto a FK estava desabilitada. Vc pode verificar isso atraves da dmv sys.foreign_keys. A FK foi marcada como “nao confiavel“. Veja a query abaixo:
select name , is_not_trusted from sys.foreign_keys
Com o resultado da query acima  vc vera que a coluna is_not_trusted esta marcada com o valor 1  , indica que a FK nao e confiavel.
O que pode ser feito nesse caso e adicionar a opção WITH CHECK para a clausula alter table que habilita a constraint:

alter table dbo.Orders with check check constraint FKOrdersCustomers

Se a query for executada novamente vera que o plano de execucao sera o visto da primeira implementacao.

Se nesse meio tempo que a FK foi  desabilitada algum registro que nao condiz com a  implementacao da constraint fosse inserido . Uma mensagem de erro seria retornado para o SQL Server. Por exemplo se uma order fosse inserida com um CustomerID NULL.

Referencia: Microsoft Database Developer 2008 TK 70-433