Archive

Posts Tagged ‘Performance’

Criando Relatório de Performance e Tuning

Olá pessoal.

Durante o evento do SQL Saturday gostaria de ter mostrado como nós criamos relatórios de performance utilizando arquivos .blg gerados pelo Perfmon. Como não foi possível, estou escrevendo essa dica aqui no blog.

Primeiramente é muito comum em uma análise de performance nós utilizarmos o perfmon para coletar contadores de performance, pois, o mesmo não gera um alto “overhead” para o ambiente e pode lhe mostrar de forma clara aonde está o problema (se você souber relacionar os dados do Perfmon, é claro), no entanto, não mostra qual é a causa raiz do problema. Para esse artigo, não temos a intenção de mostrar como é criado a coleta e o PORQUE dos contadores (ver as referências no fim do artigo), isso será feito em artigos adiantes que irei escrever aqui. O principal objetivo deste post é mostrar: COMO CRIAR UM RELATÓRIO em uma coleta já realizada com o Perfmon.

No meu exemplo utilizarei o arquivo TracePerfmon.blg que deixei realizando uma coleta na minha máquina, conforme pode ser visto abaixo:

 

Figure 1 – Gráfico do Perfmon padrão

 

Agora imagine o presidente da sua empresa ou o seu gerente olhando para esse gráfico para tentar relacionar os dados! Você não verá uma cena muito agradável…rs.

Como posso transformar essas informações de uma maneira que seja fácil para ler? Para quem ainda não conhece, existe uma ferramenta via linha de comando chamada “relog” a qual utilizaremos nesse artigo.

 

 

Utilizando o relog.

O relog extrai informações dos contadores de performance do perfmon e pode transformá-los em outros formatos como text-TSV, text-CSV, SQL e etc.

Abra o prompt de comando do SQL Server e digite o seguinte comando: relog/?

 

Figure 2 – Instrução “Help” do relog

Aperte “Enter“. Será exibido um help dos parametros do relog e algumas sintaxes de como você pode utilizar o comando.

 

Figure 3 – Exemplos de utilização do relog.

Obs: Nesse post não entraremos no mérito de explicar o que significa cada parâmetro, no entanto, existe uma referência do relog no fim do artigo.

Transformando o binário em CSV

Agora que sabemos utilizar o relog, precisamos ir até a pasta onde encontra-se nosso arquivo .blg para poder transformar o arquivo em .CSV.

Figure 4 – Caminho de onde está o arquivo .blg

 

Agora que naveguei até a pasta basta apenas digitar o comando abaixo e apertar o “Enter” para que o arquivo .CSV seja gerado.

 

Figure 5 – Transformação do .blg para .csv, após o relog

 

A hora da Mágica

Com o arquivo .CSV gerado nós iremos importá-lo no excel, para isso, abra o excel e clique em “Data” e “From Text“.

 

Figure 6 – Importando dados no excel através de fontes de texto.

 

Localize o arquivo .CSV que foi gerado através do comando relog e clique em “Importar“. Aparecerá uma tela com o primeiro passo da importação do arquivo, mude para “Delimited” e clique em next, sua tela deve ficar como a imagem abaixo:

 

Figure 7 – Passo 1 da importação

 

A tela seguir irá definir quais caracteres são os seus “delimitadores“, deixe a tela como a tela a seguir:

 

Figure 8 – Passo 2 da importação

O ultimo passo apenas determina a formatação das colunas, deixe como está, no caso “General” e clique em “Advanced“. Uma tela irá se abrir:

 

Figure 9 – Definindo separadores.

Esse é um ponto extremamente importante, pois, dependendo do idioma utilizado essas infomrações podem vir divergentes, causando uma pequena mudança no momento da formatação dos dados. Deixe o separador de decimal como “Ponto” e o separador de milhares como “virgula” e clique em “OK” e “Finish“.

Criando os Gráficos

Após o arquivo ser importado para o excel, remova a segunda linha da planilha, conforme pode ser visto na imagem abaixo:

Figure 10 – Removendo a segunda linha da planilha

 

Após a linha ser removida, formate a primeira coluna da planilha excel para data utilizando o “Custom Format” das células. Sua data deve ficar algo parecido como no “Sample“.

 

Figure 11 – Formatação Excel da coluna de Data.

 

No momento que a nossa data estiver formatada, apenas precisamos adicionar uma nova coluna no arquivo excel, que será chamada de “Hora“.

Nós iremos extrair da coluna da data formatada anteriormente apenas as horas para que possamos fazer nosso relatório de “hora-em-hora“. Poderíamos fazer também de “minutos-em-minutos“. Lembrando que as colunas que serão transformadas em “Horas” deve estar como “General“, conform imagem:

Figure 12 – Aplicando a função de hora para a nova coluna

 

Após o passo anterior realizado, seu arquivo de excel deverá ficar mais o menos com o arquivo abaixo:

 

Figure 13 – Arquivo Excel “pronto” com a coluna de minutos incluso.

 

No momento que as colunas estão criadas, apenas precisamos criar um “Pivot Chart e Pivot Table” para montar os gráficos:

 

Figure 14 – Criando a Pivot Table e o Gráfico

A seguinte tela aparecerá, apenas informe o “Range” das informações que farão parte do gráfico, no nosso caso, todas as células. Deixe como a seguir e clique em “OK“.

 

Figure 15 – Determinando quais informações farão parte do gráfico e da tabela.

 

Agora os Mestres em Excel podem utilizar a sua imaginação para fazer relatórios necessários com os contadores de performance. Como não faço parte deste grupo seleto de mestres, apenas criei um relatório básico que: mostra as informações da “Média de Batch Requests/sec por hora” do período da coleta.

 


Espero que tenham gostado!!!

Até a próxima

Thiago Carlos [TC] de Alencar

 

 

Referências

Relog:

http://technet.microsoft.com/en-us/library/bb490958.aspx

Performance Counters PDF

http://www.quest.com/techbrief/sql-server-perfmon-counters-poster811635.aspx

Criando coletores de performance

http://technet.microsoft.com/en-us/library/cc749337.aspx


 

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.

 

Encontrando Índices Duplicados no SQL Server

Galera, boa noite.

Geralmente nós profissionais do mercado SQL Server sempre estamos procurando, desenvolvendo e criando maneiras de automatizar o trabalho do dia-a-dia. Com isso, podemos usar o nosso precioso tempo para poder desenvolver técnicas e recursos para agir com mais pró-atividade. E isso não seria diferente com os “scripts” que carregamos. A partir de hoje colocarei  nesta seção de scripts, alguns scripts que criei, outros que peguei na internet ou copiei de livros.  Não vou conseguir lembrar o nome de todos os autores do scripts, mas, geralmente serão os nomes dos profissionais mais conhecidos e ativos na comunidade (Se o autor ver seu script aqui, por favor, me mande um e-mail para que eu faça a referência). Até hoje uso os scripts que foram utilizados nas seções “30 Scripts que não podem faltar no pen driver de um DBA (parte 1/2). Que foi apresentada pelo Vitor Fava, Alexandre Lopes e Rodrigo. Simplesmente incrível.

Vamos ao que interessa! O script abaixo foi criado pelo Paul Nielsen para poder encontrar índices duplicados em um banco de dados SQL Sever. O script avalia apenas as chaves dos índices e não as colunas que foram adicionados ao Cover Index e ao Filtered Index:

/*
SELECIONAR INDICES DUPLICADOS ( KEYS INDEX ONLY )

http://sqlblog.com/blogs/paul_nielsen/archive/2008/06/25/find-duplicate-indexes.aspx

*/
— exact duplicates
with indexcols
as (
select
object_id as id
, index_id as indid
, name
, (
select
case keyno
when 0 then null
else colid
end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by
keyno
, colid
for xml path(”)
) as cols
, (
select
case keyno
when 0 then colid
else null
end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by
colid
for xml path(”)) as inc
from sys.indexes as i)
select
object_schema_name(c1.id) + ‘.’ + object_name(c1.id) as ‘table’
, c1.name as ‘index’
, c2.name as ‘exactduplicate’
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;

 

Obrigado Alisson Cardoso

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

Conhecendo Indices Step-by-Step II

Nesta segunda parte do nosso artigo post iremos entender como o SQL Server armazena e lê os dados através de Clustered Indexes, NonClustered Indexes e  Heaps. O objetivo deste post é mostrar conceitualmente como o SQL Server armazena os dados e de como os indices funcionam dentro do SQL Server. Esse post é dedicado ao Luciano [LUTI] Caixeta Moreira, pois, ele é uma das minhas maiores inspirações, após, eu ter realizado o treinamento de SQL Server Internals.

Clustered Tables:

Clustered Tables são conhecidas assim, quando possuem um clustered index. Os dados são armazenados ordenados fisicamente baseando-se na chave do índice, por exemplo. A tabela clientes possui um clustered index sobre a coluna Id, que dizer que os índices são armazenados na ordem dos valores ID. Esses valores podem estar em ordem crescente ou decrescente, iremos ver essa opção na criação do índice mais tarde neste artigo. Como falado anteriormente, existe uma “lista” duplamente ligada entre cada página do índice e a navegação entre os níveis são feitos através de um valor chave. Só pode existir um clustered index por tabela, a pergunta é: Por quê ? Simples! Os dados são armazenados ordenados fisicamente baseando-se na chave do índice. Pra deixar mais claro, vamos a um exemplo completamente BOBO, mas, que pode ajudar na compreensão. Voltando ao tempo de escola quando a “TIA” pedia para que fizéssemos uma fila por ordem de tamanho, neste momento estávamos realizando uma “ordenação física” a um critério que neste caso é o tamanho. Sei que o exemplo parece meio estúpido, mas, esse é um dos motivos pelo qual podemos criar apenas um clustered index por tabela. No caso da professora, ela só pode optar por uma ordem física da fila. Cada clustered index tem uma linha na sys.partitions com o index_id = 1. Abaixo temos a estrutura B-tree do clustered index :

Heaps

Uma heap é uma tabela SEM um clustered index, os dados não são armazenados em uma ordem particular ou obedecem a uma sequencia em particular. Cada heap tem uma linha na sys.partitions com o index_id = 0. Tratando-se de questão de múltiplas partições em uma heap, pode ser aplicado o mesmo conceito que o clustered index . O que difere o clustered index para uma heap é de como o SQL Server realiza a navegação entre as páginas de dados. As páginas de dados não estão ligada em uma lista duplamente ligada com o clustered index então o SQL Server usa uma página IAM (Index Allocation Map) para navegar entre as páginas de uma heap. Na figura abaixo temos a estrutura de uma heap:

Indices NonClustereds:

Índices nonclustered tem a mesma estrutura de uma B-Tree como a de um clustered index. Exceto por as seguintes diferenças:

  • As linhas subjacentes da tabela não são ordenadas e armazenadas baseadas na ordem de suas chaves nonclustereds.
  • O nível folha de um nonclustered índice é feito de páginas de índices ao invés de páginas de dados.

Os índices nonclustereds, podem ser criados em tabelas, views ou em heaps. Basicamente os tipos de índices que existem no SQL Server são: clustereds, Xml índices, Spacial índices e nonclustered indexes (podendo ter variações como: Filtereds, Covereds, Computados e Hash Indexs, que serão cobertos em outros posts). Cada linha de um nonclustered index contém um valor de chave nonclustered (nonclustered key value) e um indicador de linha (row locator). Esse indicador de linha é usado como um ponteiro em um clustered index ou em uma heap. As condições para os dois casos são:

  • Se a tabela é uma Heap, significa ela não tem um clustered index, o localizador é um ponteiro para a linha que é chamado de RID (FileID, PageID e SlotId)
  • Se a tabela tem um clustered index ou o index existe em uma view indexada, o localizador é o que chamamos de chave de clustered index (clustered index key). Pra simplificar é o seguinte, cada nonclustered index dentro de uma tabela clusterizada tem uma referência para um índice clustered.

Índices nonclustereds também podem ser encontrados como as heaps e os clustereds indexes dentro da sys.partitions com o index_id > 1.

Espero ter ajudado!

[] Thiago Carlos [TC] de Alencar

 

 

Conhecendo Indices Step-By-Step

Olá Galera, boa noite.

Desta vez escolhi um assunto extremamente importante e que vejo muita gente falando por ai. Porém, muitas pessoas ainda não entendem perfeitamente como funciona perfeitamente. Meu objetivo neste post é fazer com que o leitor entende como o SQL Server armazena e lê os dados. Isso mesmo, irei falar sobre índices. Esse artigo será divido em diversas partes, por ser um assunto com muito conteúdo e de certa forma complexo.  Já cheguei a falar sobre este assunto no blog, porém, de uma forma introdutória. Este post é dedicado a Andressa A. Martins, ela é uma pessoa que tem me ajudado muito na minha mais nova empreitada e me instruiu como os meus posts podem ter maior visibilidade na comunidade SQL Server.

Obs: Nos posts eu não vou traduzir nonclustered indexes, clustered indexes e alguns termos conhecido na documentação, pelo simples motivo de ficar ZUADO a tradução ao pé da letra.

Introdução:

Os índices são criados em colunas de uma tabela ou em view. O índice oferece uma maneira rápida de procurar dados com base nos valores dentro dessas colunas. Por exemplo, se você criar um índice para a chave primária e em seguida, procure uma linha de dados baseado em um dos valores de chave primária, SQL Server primeiro verifica que o valor no índice, e depois usa o índice para localizar rapidamente toda a linha de dados. Sem o índice, uma verificação de tabela teria de ser realizada a fim de localizar a linha, que pode ter um efeito significativo no desempenho.

Você pode criar índices na maioria das colunas de uma tabela ou em uma view. As exceções são principalmente as colunas configuradas com tipos de dados, tais como imagem, texto, e varchar (max) conhecidos como LOB’s. Você também pode criar índices em colunas XML, mas esses índices são ligeiramente diferentes do índice básico.

B-Tree:

No SQL Server, índices são organizados em B-Trees (O B é de Balanced e não de Binário). Cada página de um index em uma B-Tree é chamada de Nó (Node). O topo do nó da da B-Tree é chamado de Nó Root(Root Node). O ultimo nível desta estrutura é chamado de Nivel Folha (Leaf nodes). Qualquer nível entre o nível folha e o root é conhecido como Nível Intermediário.  As páginas de cada nível do índice estão relacionadas em uma lista duplamente ligada. Tendo como a função de um ponteiro de página para página, porém, isso não ocorre apenas em um sentido. Esses “ponteiros” existem em ambos os sentidos das páginas de dados, cada página tem referência para a página anterior (Last Page) e para a próxima página (Next Page).

Organização de uma Tabela:

A tabela contém uma ou mais partições e cada partição contém linhas de uma heap ou clustered index. As páginas de uma heap ou clustered index são gerenciadas em uma ou mais unidades de alocações (allocation unit) dependendo do tipo de dados. Como pode ser visto, podemos ter três diferentes tipos de unidade de alocação que gerenciam este armazenamento, como: Data, LOB e Row-Overflow.

Para verificar as unidades de alocações usadas pelo seu banco de dados, consulte a DMV: select* from sys.allocation_units

No meu caso eu estou mostrando quais as unidades de alocação que estão sendo utilizadas:

Partições:

Tabelas e páginas de índices são contidas em uma ou mais partições. Por padrão, uma tabela ou índice tem somente uma partição que contém toda a tabela ou as páginas de índice. Uma partição reside em apenas um único FileGroup. Quando temos uma tabela ou índice em múltiplas partições, os dados são particionados horizontalmente e um determinado grupo de linhas que é mapeado dentro de uma partição individual. Esse comportamento pode ser verificado em um artigo que falo sobre particiona mento: https://tcalencar.wordpress.com/2012/03/06/particionando-tabelas/. Para verificar as partições do seu banco de dados, consulte a DMV: select* from sys.partitions

E os níveis da B-Tree que você falou, onde estão? Vamos procurar a nossa resposta. O script cria uma tabela, adiciona um clustered index e realiza algumas inserções.

USE tempdb

GO

IF OBJECT_ID(‘dbo.TabelaFramentada ‘) IS NOT NULL

BEGIN

      DROP TABLE dbo.TabelaFramentada

END

 

CREATE TABLE dbo.TabelaFramentada (ID CHAR(800),

Nome CHAR(2100),

UltimoNome CHAR(2500),

Cidade CHAR(2200))

GO

 

CREATE CLUSTERED INDEX [IXNC_FragTable_ID] ON dbo.TabelaFramentada

(

[ID] ASC

) ON [PRIMARY]

GO

 

INSERT INTO dbo.TabelaFramentada (ID,Nome,UltimoNome,Cidade)

SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.name) RowID,

‘Thiago’,

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN ‘Bianca’

ELSE ‘Andressa Martin’ END,

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN ‘Pinho’

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN ‘TT Maia’

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN ‘Luiz Henrique’

ELSE ‘Bruno Catapano’ END

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

Para verificar os níveis do índice, executaremos a seguinte query:

SELECT OBJECT_NAME(object_id)

,index_level

,record_count

FROM sys.dm_db_index_physical_stats(DB_ID(‘TempDb’),OBJECT_ID(‘dbo.TabelaFramentada’),NULL,NULL,’DETAILED’)

Comparando o resultado da query com o primeiro print deste post (Estrutura B-Tree) podemos afirmar que temos os seguintes níveis: Um nível folha, dois níveis intermediários e o Nível Root.

No próximo post iremos entender como o SQL Server armazena e lê os dados através de Clustered Indexes, NonClustered Indexes e  Heaps.

 

Referências:

http://blog.sqlauthority.com/2010/07/04/sql-server-index-levels-page-count-record-count-and-dmv-%C2%A0sys-dm_db_index_physical_stats/

http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

Books  On Line

Espero ter ajudado!

[] Thiago Carlos [TC] de Alencar

Performance começa na modelagem II

Boa noite galera! Como falei no meu post anterior, “apareci” novamente aqui para falar sobre modelagem. Vou mostrar alguns descuidos que podem causar futuro problemas de performance. Ou até mesmo causado por falta de conhecimento. E é aqui que nós profissionais de SQL Server entramos, pra ajudar os amigos de profissão e tirar dúvidas sempre quando possível.

Agora apresentarei as definições das formas normais:

1º Forma normal – Requer que todos os valores de colunas em uma tabela, sejam atômicos (ex., um número é um átomo, enquanto uma lista ou um conjunto não o são). Por exemplo, a normalização elimina grupos repetidos pondo-os cada um em uma tabela separada, conectando-os com uma chave primária ou estrangeira.

2º Forma normal –  Requer que não haja dependência funcional não-trivial de um atributo que não seja a chave, em parte da chave candidata.

3º Forma normal – Requer não haver dependências funcionais não-triviais de atributos que não sejam chave, em qualquer coisa exceto um superconjunto de uma chave candidata.

Referência: http://pt.wikipedia.org/wiki/Normaliza%C3%A7%C3%A3o_de_dados

Agora imagine que o programador ou o analista de sistemas recebeu a seguinte especificação:

Criar uma sistema de call center que recebe um lote de informações de clientes que devem receber ligações para venda de cartões. Porém, as informações são recebidas em um arquivo de texto, sendo que o nome do cliente, CPF do cliente, telefone residencial do cliente e telefone celular do cliente fazem parte de um único registro. Como em terra de cego que tem um olho é rei. Nosso ilustre amigo criou uma única tabela para receber os dados e assim facilitando o seu trabalho. A tabela ficou da seguinte forma:

USE MCITP
GO
IF OBJECT_ID(‘dbo.TB_CLIENTE’) IS NOT NULL
BEGIN
DROP TABLE dbo.TB_CLIENTE
END
GO
CREATE TABLE dbo.TB_CLIENTE
(
COD_CLI INT IDENTITY NOT NULL PRIMARY KEY,
CPF_CLI CHAR(11) NOT NULL ,
DD_TEL_RES CHAR(2) NOT NULL,
TEL_RES_CLI CHAR(8) NOT NULL,
DD_CEL_CLI CHAR(2) NOT NULL,
TEL_CEL_CLI CHAR(8) NOT NULL
)

Até aqui tudo parece perfeito, porém, a equipe gerencial deseja que uma tela de consulta seja criada e que seja possível realizar consulta por cpf, telefone residencial e telefone celular. Na tela todas os valores serão opcionais exceto cpf, teoricamente, deveria existir um índice para cada coluna, isso iria garantir que o SQL Server usasse o índice requisitado para a coluna escolhida como parâmetro.

O objetivo deste post não é falar sobre indexação ou estratégia de indexação. Esse assunto será abordado em um outro post com maiores detalhes e maiores abordagens. O que está sendo apresentando aqui acontece por ai no mercado, pois, esse é uma situação a qual eu já presenciei.

Voltando ao nosso assunto como todo mundo sabe que índice é “bom” para consultas, nosso amigo criou um índice para a coluna da cpf, telefone residencial e telefone celular.

USE MCITP
GO
CREATE NONCLUSTERED INDEX IX_CPF_CLI ON dbo.TB_CLIENTE (CPF_CLI)
GO
CREATE NONCLUSTERED INDEX IX_TEL_CLI ON dbo.TB_CLIENTE (DD_TEL_RES,TEL_RES_CLI)
GO
CREATE NONCLUSTERED INDEX IX_CEL_CLI ON dbo.TB_CLIENTE (DD_CEL_CLI,TEL_CEL_CLI)

Vamos aos fatos, um índice é uma faca de dois gumes. Em um ambiente OLTP temos muita leitura e criar índices ajuda a devolver os dados de forma mais eficaz e faz com que o SQL Server crie planos de execução mais eficientes. Por outro lado, quando você cria muito índice é possível que tenha baixa performance em instruções INSERT, UPDATE e DELETE. É correto afirmar que: O SQL Server organiza os índices em uma estrutura B-Tree (Esse assunto não será abordado neste post). E quando um índice clustered é atualizado ele deve manter os índices non-clustereds atualizados também. Já conseguiram sacar onde está o nosso possível overhead nas instruções que modificam os dados? Não! Então vamos lá!

Podemos ver que na consulta abaixo é possível verificar quais os índices que foram criados. Temos quatro índices. Um índice clustered para a coluna COD_CLI que é a nossa primary key  e os demais índices NONCLUSTEREDs que críamos anteriormente.

select i.name,i.type_desc from sys.indexes i
where i.object_id = OBJECT_ID(‘dbo.TB_CLIENTE’)

Como não realizamos nenhuma instrução sobre a nossa tabela TB_CLIENTE, podemos perceber que o nível folha e o nível intermediário da B-Tree não sofreu nenhuma modificação:

SELECT i.name,a.leaf_insert_count,a.nonleaf_insert_count
,a.leaf_delete_count,a.nonleaf_delete_count
,a.leaf_update_count,a.nonleaf_update_count
,a.leaf_allocation_count,a.page_lock_count
FROM sys.dm_db_index_operational_stats(DB_ID(‘mcitp’),OBJECT_ID(‘dbo.TB_CLIENTE’),NULL,NULL) as a
join sys.indexes i
on i.index_id = a.index_id
and i.object_id = a.object_id

Agora iremos realizar alguns inserts em nossa tabela e veremos o que acontece.

INSERT INTO dbo.TB_CLIENTE(CPF_CLI,DD_TEL_RES,TEL_RES_CLI,DD_CEL_CLI,TEL_CEL_CLI)
VALUES(‘7893652′,’11’,’24985236′,’11’,’78964521′)
,(‘7896325236′,’11’,’78965230′,’11’,’69365212′)
,(‘33209376325′,’11’,’78785225′,’11’,’80756312′)
,(‘33369863201′,’11’,’96785421′,’11’,’56365425′)

Executaremos novamente a query que traz as modificações feitas no nível folha e no nível intermediário para ver como a nossa estrutura está após os inserts:

Thiago! Continuo sem entender! Então vamos lá? O que aconteceu!? Como temos quatro índices na nossa tabela e realizamos quatro inserções, que dizer que: O SQL Server também atualizou todos os nossos índices nonclustereds no nível folha da nossa estrutura B-Tree como eu havia explicado anteriormente. Podemos ver que, muito índice em uma tabela que recebe alto nível de inserções pode prejudicar a performance. Imagine que eu precisasse importar 2000 linhas. O SQL Server escreveria 2000 linhas para o índice clustered e 2000 para cada índice nonclustered.

Mais e a atualização de registros, como ficaria ? Vejamos irei atualizar a coluna CPF_CLI para o cliente com o código 1.

UPDATE dbo.TB_CLIENTE SET CPF_CLI = ‘123654789’ WHERE COD_CLI = 1

Pronto! Agora com a atualização na coluna CPF_CLI podemos ver um aumento de valor na coluna leaf_insert_count para 5 no indice da coluna CPF. O registro também foi modificado no índice clustered, isso pode ser visto na coluna leaf_update_count que tem o valor de 1.  Mas porque no índice clustered? O índice clustered são os dados propriamente ditos, quando fizemos o update na coluna de cpf o SQL Server

Espero ter ajudado. Até o próximo post.

Performance começa na modelagem

Desempenho começa na Modelagem.

Bom dia, Querido blog, tempo que não escrevo em você….rs. Hoje estou aqui apenas para falar  aos leitores sobre algumas pequenas dicas de performance  que as vezes passam despercebidos aos nossos olhos.

Vamos à análise.
Uma página de dados no SQL Server ocupa 8192 bytes, sendo 8060 bytes para os dados e o restante para o header da página. É correto afirmar que: criar um índice em uma coluna do tipo de dados inteiro que possui 2015 linhas vai ter uma página de dados completamente preenchida:

Calculo: Tipo de dados * Qtde de Linhas
Calculo: Int 4 Bytes * 2015 Linhas = 8060 bytes.

Thiago não estou entendendo aonde você que chegar!? Simples, meu caro blog! Será que preciso de uma coluna do tipo de dados int para a minha tabela. Desenvolvedores, analistas, programadores e estagiários é aqui que a gente entra.

Quando modelar alguma necessidade de negócio, sempre temos que nos atentar a que tipo de dados vamos usar e se é realmente necessário este  tipo de dados para determinada coluna. Já vi em muitos casos tabelas de domínio que apenas guarda poucos valores com o tipo de dados inteiro. Vamos a um exemplo:

YODA deve desenvolver um sistema de venda de produtos e foi especificado que os produtos a serem vendidos, seriam divididos em categorias. YODA criou o seguinte script para a tabela de categoria:

USE tempdb
go
IF OBJECT_ID(‘dbo.CategoriaProduto’) IS NOT NULL
BEGIN
DROP TABLE dbo.CategoriaProduto
END
CREATE TABLE dbo.CategoriaProduto (COD INT IDENTITY, DESCRICAO VARCHAR(50))

GO

Agora vem a pergunta! Até quantos registros posso armazenar em um tipo de dados inteiro? Segundo a definição do meu melhor amigo (Books On line), podemos armazenar -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). Será que preciso armazenar todas estas categorias? Se tivermos apenas 50 ou ate 250 categorias, poderíamos usar o tipo de dados tinyint. Com isso apenas usaríamos um byte de armazenamento. Mas preciso mesmo me preocupar com esses detalhes? Será que essa diferença em bytes pode afetar minha performance ? A resposta é SIM!. O usuário final ou até mesmo o desenvolvedor não pode sentir essa perda, porém, isso para o SQL Server é extremamente importante. Uma das questões é: Tomando esse devido cuidado com tipo de dados você pode economizar espaço em disco e cache. Pode acreditar o buffer pool agradece.

Como o SQL Server ler os dados?

Todos nós sabemos que a leitura em memória é mais rápida do que a leitura em disco. O SQL Server também sabe! Quando uma query é enviada ao SQL Server ele verifica se os dados estão em memória (Buffer Pool), antes de verificar se os dados do disco. Como os dados estão em memória o SQL Server apenas realiza leitura lógica (Não Física). Thiago! E o que tudo isso tem a ver com o que foi escrito anteriormente? SIMPLES. Quanto menor for o tipo de dados usado em nossas colunas, menor a quantidade de espaço iremos armazenar e mais informações teremos dentro do buffer pool, sendo assim, diminuindo o acesso ao disco e aumentando a quantidade de informações em memória.

Pessoal modelar é uma tarefa muito importante, então, por favor. Prestem atenção. Estarei criado mais posts referente a estes assuntos futuramente.