Archive

Posts Tagged ‘Tunning’

Conhecendo um pouco de performance

Fala Galera, boa noite.

Tudo bem? Faz tempo neh!? Hoje não colocarei o desafio de T-SQL (não ainda..rs). Hoje vim pra divulgar um assunto que também estava ficando um pouco desatualizado ,mas, como algumas pessoas não conseguiram comparecer e algumas não ficaram sabendo, estou divulgando aqui no blog.

No dia 03/12 tivemos a última reunião, do ano de 2015, do SQLManiacs  organizada pelo Vitor Fava, onde eu falei um pouco de performance e tuning (novidade..) utilizando alguns cenários práticos do dia-a-dia.

Para quem tiver interesse em reproduzir todos os cenários descritos na reunião, basta fazer o download dos scripts através do link abaixo:

Reunião do SQLManiacs – Conhecendo um pouco de performance

Ou ir até o blog do Fava, pois, lá possui mais algumas informações, fotos e etc.

https://vfava.wordpress.com/2015/12/08/reuniao-do-sqlmaniacs-conhecendo-um-pouco-de-performance-3/

Muito Obrigado e até a próxima

 

 

Advertisements

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

 

Brinquedinho de Processamento

Galera, boa noite.

Pra descontrair!! Esse é o meu mais novo brinquedinho……

 

Por enquanto o SQL Server dorme…

Conhecendo Indices Step-by-Step V

Fala Galera, blz? Bem vindos ao quinto artigo da série “Conhecendo Indices Step-By-Step”. Esse post é dedicado a Bianca Almeida de Oliveira, que esta sempre ao meu lado , minha incrível esposa. Hoje iremos ver como o SQL Server acessa os dados de um índice nonclustered em uma tabela clusterizada. Na seção três vimos como o SQL Server usa o RID para encontrar um registro especifico, conhecido como RID Lookup. A técnica aplicado para o caso de hoje é o que chamamos de KeyLookup. Vamos a prática:

Neste exemplo usaremos uma tabela chamada TB_CLIENTES que possui apenas um índice clustered na coluna ID_CLIENTE. Fazendo a query a seguir, o SQL Server optou por usar um Clustered Index Scan, vejamos:

SET STATISTICS IO ON

SELECT ID_Cliente, Nome, Col1

FROM TB_CLIENTES

WHERE Nome = ‘Colin B9A7D004’

SET STATISTICS IO OFF

Abaixo o resultado e o plano de execução:

Plano de execução:

Voltando as posts iniciais desta série é correto afirmar que os índices são construídos em estruturas B-Tree. No nosso plano o que foi apresentado foi um clustered index scan, mas, será que não seria interessante criarmos um nonclustered index por nome? Vamos avaliar?

CREATE NONCLUSTERED INDEX IXNC_Nome ON TB_CLIENTES(Nome)

Ao criar o índice, podemos afirmar que o SQL Server utilizará o índice para buscar os nomes, porém, como foi dito nos posts anteriores: O SQL server cria uma estrutura B-Tree pra cada índice e neste caso é o nome. Pare um pouco e pense caro leitor, a query apenas solicita o nome? NÃO. Ela ainda precisa retornar para o cliente a coluna Col1 e o Id_cliente, como o SQL Server busca essa informação? Ao executar a mesma query do inicio do artigo, podemos perceber que o SQL Server apenas efetuou seis leituras lógicas e fez o que chamamos de KeyLookup:

O KeyLookup pode ser visto como um dos responsáveis por alto custo no plano de execução:

Thiago! Não entendi, como o SQL Server fez isso? Vamos simular a leitura dos índices para entender esse comportamento.

Obs: Lembrando que o conceito, é muito parecido com os posts anteriores.

Tendo como base o comando DBCC PAGE gerado, vamos começar a leitura.

SELECT dbo.fn_HexaToDBCCPAGE(Root), *

FROM sys.sysindexes

WHERE name = ‘IXNC_Nome’

AND id = OBJECT_ID (‘TB_CLIENTES’)

DBCC TRACEON (3604)

DBCC PAGE (31,3,303,3) — 1º Leitura

Baseando-se no resultado do print, foram retornados dois registros. A pergunta é: A letra “C” vem antes ou depois da letra “J”? Resposta fácil: Que dizer que nossa próxima leitura é na ChildPageId 26528.

DBCC PAGE (31,3,26528,3) — 2º Leitura

Na nossa segunda leitura podemos ver que o cliente Colin B9A7D004, pode estar dentro da página 26661 ou 26662. Mas qual será a página certa? Posso afirmar com certeza que é a página 26661, pois, o cliente Colin que procuramos começa com B e em uma ordenação, os números sempre veem antes das letras. Que dizer que o cliente Colin 90026186 está antes do Colin B9A7D004. Vamos para a terceira leitura:

DBCC PAGE (31,3,26661,3) — 3º Leitura

EURECA! Encontramos nosso cara! Se você que está lendo, vem acompanhando todos os artigos da série, você deve se lembrar que quando fizemos essa mesma quantidade de leitura para uma Heap, encontramos uma coluna de nome “Heap RID (Key)” com valores em hexadecimais. Como estamos falando de uma tabela Clusterizada, temos a referência do nosso índice clustered (Coluna id_cliente(Key)) dentro do índice nonclustered. Thiago! Que dizer que todos nonclustered indexes possui o clustered index? CORRETO.

Obs: Muito cuidado ao escolher seus clustereds index. Caso você tenha uma tabela com um índice clustered composto por três colunas. E nesta tabela você tenha dois nonclustered indexes…as três colunas do índice clustered irá aparecer no índice nonclustered. Isso é um grande vilão de performance. Fique sempre atento: PERFORMANCE COMEÇA NA MODELAGEM.

Voltando ao assunto do post: A partir daqui fizemos três leituras, mas, o SQL Server apenas sabe qual o nome do cliente que ele está buscando no índice e automaticamente seu id_cliente que é 77939. Mais uma pergunta! Então, até agora apenas navegamos na estrutura B-Tree do índice nonclustered Thiago? Sim. Agora o que o SQL Server faz é pegar a “chave” e navegar na estrutura do índice clustered. Vamos então a quarta leitura? Mas, primeiro precisamos retornar o DBCC PAGE do índice clustered:

SELECT dbo.fn_HexaToDBCCPAGE(Root), *

FROM sys.sysindexes

WHERE name = ‘PK’

AND id = OBJECT_ID (‘TB_CLIENTES’)

DBCC PAGE (31,3,295,3) — 4º Leitura

Novamente a pergunta que o SQL Server faz. O valor de id_cliente 77939 é maior que o id_clente 40899? A resposta é sim. Vamos a quinta leitura, tendo como base a ChildPageID 19328.

DBCC PAGE (31,3,19328,3) — 5º Leitura

Como podemos ver, o cliente de id 77939 encontra-se dentro da página 20241 onde o id_cliente 77913 é menor que o id_cliente 77939. Vamos para a sexta e última leitura:

DBCC PAGE (31,3,20241,3) – 6º Leitura Encontramos o ID_Cliente = 77939

Pronto! Simulamos as seis leituras lógicas do SQL Server. Thiago uma pergunta! Como faço pra evitar que o SQL Server faça as últimas três leituras adicionais? Não seria mais performático pra ele achar a Col1 quando chegar no nível folha do índice nonclustered? A resposta é: Sim, seria mais fácil. No próximo post sobre índices, veremos uma das features que surgiu a partir do SQL Server 2005, o Covered Index que particularmente acho incrível.

Espero ter ajudado

Thiago Carlos [TC] de Alencar

Conhecendo Indices Step-by-Step IV

Boa tarde, comunidade SQL Server. Dando continuidade a série “Conhecendo índices Step-By-Step”. No  último artigo foi abordado como o SQL Server navega em um índice nonclustered em uma heap. Já neste artigo mostraremos como o SQL Server navega em um clustered index.

Iremos utilizar uma tabela chamada ClusteredTable que tem apenas um clustered index. A coluna que estamos falando é a coluna id_cliente. Iremos executar a query e analisaremos o plano de execução gerado.

SET STATISTICS IO ON

SELECT * 

  FROM ClusteredTable

 WHERE ID_Cliente = 500

SET STATISTICS IO OFF

Plano de execução:

Resultado:

Leituras lógicas realizadas foram três, uma a menos do que no nonclustered index do artigo anterior. Vamos entender? O procedimento de verificar as leituras é idêntico ao do artigo anterior.

Executaremos a query com a função que nos traz o comando dbcc.

SELECT dbo.fn_HexaToDBCCPAGE(Root), *

  FROM sys.sysindexes

 WHERE name = ‘PK_CL’

Abaixo executo os comandos dbcc onde encontra-se o registro:

DBCC TRACEON (3604)

DBCC PAGE (31,3,288,3)  — 1º Leitura

A segunda leitura resume-se na ChildPageId 14728.

DBCC TRACEON (3604)

DBCC PAGE (31,3,14728,3) — 2º Leitura

Como pode ser visto, o id_cliente 500 encontra-se na página 14606. Mas como assim? O motivo é porque página 14606 começa no id_cliente 443 e termina no id_cliente 516 que é onde começa a próxima página. Executando o comando dbcc podemos confirmar isso:

DBCC TRACEON (3604)

DBCC PAGE (31,3,14606,3) — 3º Leitura Encontramos o ID_Cliente = 500

No resultado da nossa terceira leitura podemos ver que o registro está lá no slot 57. Até o próximo post. E o assunto a ser falado será!? Suspense. Index..rs

Conhecendo Indices Step-by-Step III

Fala galera, blz? Bem-vindo ao terceiro artigo da série Conhecendo Índice Step-by-Step. Hoje iremos “navegar” pelos nossos índices e demonstrar como o SQL Server faz isso por debaixo dos Panos. Literalmente é isso mesmo “NAVEGAR”…rs. Não adianta saber onde um índice pode ajuda-lo ou atrapalha-lo sem saber como o SQL Server decide usá-lo e quando decide usá-lo. Este post é dedicado ao Mestre Fabiano Neves Amorim, um cara que manja demais de QO. Com o treinamento de Avanced Tunning que tive ministrado por ele, abriu muito minha cabeça e isso me ajudou a crescer profissionalmente. Vamos para de falar e mão na massa…..

Obs: Os scripts que foram usados para a carga da tabela pertencem ao Fabiano Amorim.

A tabela que utilizaremos se chama Heap. Essa tabela não possui um índice sequer, o que o SQL Server irá fazer. Primeiros precisamos listar todas as páginas que são alocadas para a nossa heap. Thiago, mas, como faremos isso? Simples! Faremos isso através dos comandos dbcc. O comando abaixo retorna todas as páginas alocadas para a nossa heap e mais algumas informações importantes. Vejamos:

DBCC TRACEON (3604)

DBCC IND (dbMonitoring, Heap, 1)

Todas as informações retornadas são importantes, porém, eu irei falar das colunas que mais considero importante para o nosso propósito:

PageFID – Id do arquivo de banco de dados que no caso da minha base de dados é o três. Isso porque eu tenho um arquivo .ldf (id 2), um arquivo de dados primário .mdf (Id 1) e um arquivo secundário .ndf (id 3) onde encontram-se todos os meus objetos definidos pelo o usuário.

PageId – Id da página de dados.

PageType – Tipo da página de dados:

  • 1 – data page
  • 2 – index page
  • 3 and 4 – text pages
  • 8 – GAM page
  • 9 – SGAM page
  • 10 – IAM page
  • 11 – PFS page

Após o comando retorna essas informações, usaremos o PageFID e o PageID no comando dbcc. A sintaxe para o comando é:

DBCC PAGE({‘NomeDoBanco’ | IdDB}, IDArquivo, NumPagina [,printopt={0|1|2|3}])

No nosso caso ficaria da seguinte forma

DBCC PAGE(dbMonitoring, 3, 298, 3) — O número da página passada dever ser o da página IAM

Serão retornadas diversas informações da página de dados com o id 298 (a IAM), eu selecionei dois trechos de informações que considero bastante importantes. Abaixo pode ser visto o primeiro bloco de informações:

No segundo print podemos ver informações abaixo do cabeçalho da nossa página:

Agora iremos realizar um select na nossa tabela heap. Já que uma heap não tem uma ordenação física dos registros, o que o SQL Server irá fazer? Se você pensou em um Table Scan. Parabéns, você está prestando atenção. Vamos fazer um select e analisar o plano de execução:

SELECT * FROM Heap WHERE ID_Produto = 50

O plano de execução mostra para a query é o seguinte:

O produto retornado é o:

Até o momento é correto afirmar que: Um select em uma heap sem um nonclustered index o SQL Server optará por um Table Scan (Se existisse estatísticas para esta tabela, provavelmente o SQL escolheria outro plano, ainda não fiz este teste).

Agora se tivéssemos um nonclustered index? PENSE, antes de dar a resposta. No artigo anterior eu falei como o nonclustered index se comporta. Ele apenas cria a estrutura da B-Tree para as colunas no índice, certo? No nosso exemplo estamos solicitando o retorno de todas as colunas com o “*”. Como o SQL Server “encontra” as demais informações quando temos um nonclustered index em uma Heap? Se você for ao artigo anterior (criar um link aki), você verá que ele usa o RID. Mas o que diabos é o RID? Vamos ver o comportamento na prática. Vamos executar essa consulta novamente com o os statistics io ligado.

–Crio o indice para a tabela Heap

CREATE NONCLUSTERED INDEX IDX_Heap ON Heap(ID_Produto)

Executando a mesma query:

SET STATISTICS IO ON

SELECT * FROM Heap

WHERE ID_Produto = 50

SET STATISTICS IO OFF

Podemos ver que: O SQL Server optou por usar o nonclustered index para encontrar o id_produto = 50. Fazendo um RID Lookup para encontrar as demais informações (devido ao uso do “*”).

Thiago! E quantas leituras o SQL Server fez para encontrar meu registro? A reposta é: 4.

Agora vamos pular para a parte boa do artigo! Iremos simular o que o SQL Server faz nessas quatro leituras.

O primeiro passo é retorna o hexadecimal do índice que o SQL Server utilizou e depois transformar o hexadecimal em decimal para poder passar os valores para o DBCC PAGE. CALMA! Não se preocupem, o nosso amigo Fabiano Neves Amorim criou uma função que recebe o hexadecimal da página root, e retorna o comando dbcc page com os valores já convertidos.

SELECT dbo.fn_HexaToDBCCPAGE(Root), *

  FROM sys.sysindexes

 WHERE name = ‘IDX_Heap’

Vamos executar o commando DBCC retornado e simular as leituras do SQL Server.

DBCC TRACEON (3604)

DBCC PAGE (31,3,10538,3) – 1º Leitura

O retorno traz a página e suas páginas filhas. A pergunta é: Onde está o registros com o id_produto = 50? Simples meu caro Watson, está na ChildPageId 10536. Mas como assim? A segunda linha tem o id_produto a partir do 207516. O número 207516 é menor ou maior que 50? Maior. Completamos a primeira leitura, agora iremos fazer o mesmo DBCC PAGE mudando apenas a página de dados.

DBCC TRACEON (3604)

DBCC PAGE (31,3,10536,3) — 2º Leitura

Agora faremos a mesma pergunta que a anterior, a única coisa que irá mudar é o ChildPageID. : Onde está o registros com o id_produto = 50? Está na ChildPageId 10408. Mas como assim? A segunda linha tem o id_produto a partir do 540. O número 540 é menor ou maior que 50? Maior. Segunda leitura concluída. Vamos para a terceira.

DBCC TRACEON (3604)

DBCC PAGE (31,3,10408,3) — 3º Leitura

Até agora simulamos 3º leituras. Thiago, já chegamos no valor do Id_produto 50, não acaba por aqui? Simplesmente não, pois, a query ainda retorna mais duas colunas: Descricao e Col1. Agora o SQL Server irá usar o RID para chegar nesses valores.

Primeiro precisamos converter o valor do hexadecimal 0x4890000003003100. Inicialmente temos que querbar esse valor em quatro blocos, ficando assim:

0x4890 0000 0300 3100

Agora precisamos fazer uma alteração neste consjunto de números: O primeiro valor de cada bloco passa a ser o terceiro valor, e o segundo valor passa a ser o quarto valor, ficando da seguinte maneira: 0x9048 0000 0003 0031

Após termos os valores em mãos, basta converter para inteiro no SQL Server e ter nossos dados, para o DBCC PAGE e assim realizar a quarta leitura que o SQL Server fez anteriormente.

SELECT CONVERT(Int, 0x9048)AS Página,CONVERT(Int, 0x0003) AS Arquivo ,CONVERT(Int, 0x0031) AS Slot

Agora só precisamos executar novamente o quarto DBCC PAGE:

DBCC PAGE (31,3,36936,3) — 4º Leitura

Como pode ser visto no resultado, nosso registro esta lá No Slot 49 e as três colunas: ID_Produto, Descricao e Col1.

Espero Ter ajudado

Thiago Carlos [TC] de Alencar