Recentemente o Luciano Moreira publicou um post no seu blog perguntando porque as vezes o SQL Server não usa um determinado índice, aproveitando a deixa resolvi escrever um pouco sobre isso.

O link para o post do Luciano é este Exibir artigo...

DROP TABLE TMP

CREATE TABLE TMP(ID INT IDENTITY(1,1) PRIMARY KEY, NOME VARCHAR(200), Nome2 VarChar(200))

CREATE INDEX ix_Teste on TMP(Nome)

SET NOCOUNT ON

DECLARE @I INT

SET @I = 0

WHILE @I < 1000

BEGIN

  INSERT INTO TMP(NOME, Nome2) VALUES('A', NewID())

  INSERT INTO TMP(NOME, Nome2) VALUES('B', NewID())

  INSERT INTO TMP(NOME, Nome2) VALUES('C', NewID())

  INSERT INTO TMP(NOME, Nome2) VALUES('D', NewID())

  INSERT INTO TMP(NOME, Nome2) VALUES('E', NewID())

  SET @I = @I + 1;

END

-- Foram incluidos 5000 registros na tabela TMP

SELECT COUNT(*) FROM TMP

/*

  Pressione CTRL-M para incluir o plano de execução no resultado da consulta e execute o comando abaixo.

  O Select irá selecionar 1/5 dela

*/

SELECT * FROM TMP

WHERE Nome = 'A'

/*

  Repare que o SQL Server não utilizou o indice pela coluna Nome e achou

  melhor fez um clustered scan, Vamos analisar melhor este comportamento.

  Primeiramente vamos ver quantas páginas o SQL Server terá que ler para

  retornar os dados da consulta, para isso vamos ligar o statistics io e rodar

  o select novamente.

*/

SET STATISTICS IO ON

SELECT * FROM TMP

WHERE Nome = 'A'

/*

Table 'TMP'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Podemos observar que o SQL leu 37 páginas de dados para retornar o resultado.

Agora vamos forçar o uso do indice por nome para fazer o Seek e não o Clustered Scan,

repare no plano de execução que o SQL fez um Seek e um bookmark para

pegar o valor da coluna Nome2 que não faz parte do indice nonclustered ix_teste.

No indice cluster(no nosso exemplo é o campo ID que é a primary key) temos as informações de todos

os dados da tabela, portanto sempre que uma consulta seleciona campos a mais do que os cobertos pelo

indice nonclustered o SQL irá no indice cluster ler esta informação.

No nosso exemplo abaixo isso aconteceu porque a coluna Nome2 não pertence ao indice nonclustered,

portanto o SQL fez o lookup para ler esta informação no indice cluster.

*/

SELECT * FROM TMP WITH(INDEX = ix_teste)

WHERE Nome = 'A'

/*

Table 'TMP'. Scan count 1, logical reads 2076, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Podemos observar que desta vez o SQL leu 2076 páginas de dados para retornar o resultado.

Com isso podemos concluir que o Lookup é MUITO "caro" para sua consulta e o

SQL só irá optar fazer o lookup se a quantidade de linhas que será retornada for

aproximadamente menor que 0,1% do total de sua tabela.

Uma solução para forçar o uso do indice sem perder tanto em performance

seria incluir a coluna Nome2 no indice noncluster,

No SQL 2000

CREATE INDEX ix_Teste on TMP(Nome, Nome2)

No SQL 2005 poderiamos utilizar a clausula INCLUDE

CREATE INDEX ix_Teste on TMP(Nome) INCLUDE(Nome2)

*/

/*

Vamos incluir uma linha na tabela TMP com o valor X e efetuar

o select para retonar apenas o valor X e ver o plano de execução.

*/

INSERT INTO TMP(NOME, Nome2) VALUES('X', NewID())

SELECT * FROM TMP

WHERE Nome = 'X'

GO

SELECT * FROM TMP

WHERE Nome = 'A'

GO

/*

Repare que no primeiro select o SQL usou o Indice e no

segundo não usou porque irá retornar muitas linhas

*/

/*

Um outro comportamento interessante é quando o valor do filtro é passado

por uma variável, vimos no select acima que o SQL usou o indice para

o filtro nome = 'X', vamos passar o valor 'X' para uma variável

e utiliza-la no where.

*/

DECLARE @var VarChar(200)

SET @var = 'X'

SELECT * FROM TMP

WHERE Nome = @var

/*

Desta vez o SQL não usou o indice e fez o Scan mesmo a consulta retornando

apenas 1 registro, isso acontece porque o SQL não sabe o valor de @var

por este motivo ele opta por fazer o Scan porque ele não sabe se @var irá retornar

1 linha ou 5000.

Existem várias soluções para este problema, a idéia a fazer o SQL compilar

o código com o valor de @var.

Você poderia utilizar a SP_EXECUTESQL

Ou então criar uma procedure e executar a consulta na procedure

Ou então utilizar o Hint RECOMPILE

Veja um exemplo do uso das 3 opções

*/

-- SP_EXECUTESQL

DECLARE @SQL NVARCHAR(200)

SET @SQL = 'SELECT * FROM TMP WHERE Nome = @Var'

EXEC SP_EXECUTESQL @SQL, N'@Var VarChar(200)', @Var = 'X'

-- Procedure

-- Criei a proc utilizando a clausula WITH RECOMPILE

-- mas esse assunto fica pra uma 2 Parte do post

CREATE PROC st_Teste @Var VarChar(200)

WITH RECOMPILE AS

SELECT * FROM TMP

WHERE Nome = @var

GO

EXEC st_Teste @Var = 'A'

-- HINT - RECOMPILE

DECLARE @var VarChar(200)

SET @var = 'X'

SELECT * FROM TMP

WHERE Nome = @var

OPTION(RECOMPILE)

_________________________________________________________________
Fabiano Neves Amorim
Análise - NewCon Enterprise
* fabiano@cnpm.com.br - http://fabianosqlserver.spaces.live.com/

Business Objects vendida pra a SAP por $6,8 bi e a Cognos vendida para a IBM por $5,0 bi, e qual foi o resultado?

Microsoft líder em vendas de Business Intelligence,

http://www.informationweek.com/news/showArticle.jhtml?articleID=206104502&subSection=News

Hoje no newsletter da SQLTeam.com recebi o link para um artigo falando sobre a integração entre o PerfMom e o Profiler 2005.

Eu já tinha visto o Javier Loría da Solid Quality abrir um log do Performance Monitor no Profiler do SQL Server 2005, assim como usar o Profiler para capturar os códigos MDXs gerados pelo Analysis Services, quem já usou o OWC e sempre quis ver o MDX que o componente gerava fica ai a dica, voltando ao PerfMom, após ler o artigo e fazer alguns testes na minha máquina cheguei a seguinte conclusão.

DBAs só não fazer o trabalho deles por 3 motivos:

1.       Porque não querem.

2.       Porque não sabem.

3.       Porque gostam de culpar os desenvolvedores pelos problemas de performance J.

Como diria a Delaney, Have Fun!

Ainda falando sobre o TempDB, hoje li um artigo escrito pelo Paul Randal, vale a pena gastar um tempo para ler.

Achei bem legal ele falando que mesmo quando se trata do mesmo assunto existem várias idéias e opiniões diferentes por parte dos funcionários da Microsoft. Portanto não devemos SEMPRE confiar em tudo que eles dizem ou escrevem.

Fiquei imaginando a cena, o cara querendo convencer o Paul de que o DBCC CHECKDB é isso e não é aquilo, e ele simplesmente respondendo, 'I wrote that code - I'm afraid you *are* wrong'. (Eu escrevi o código – Temo que você está errado)

Obs.: “I’m afraid” segundo o Concise Oxford Dictionary significa pedir desculpas educadamente, rs senti uma certo sarcasmo na frase.

Segue o link para o “Debate” sobre criar ou não vários arquivos para o TempDB.

http://www.sqlservercentral.com/Forums/Topic448122-361-2.aspx

__________________________________________________________________
Fabiano Neves Amorim
Análise - NewCon Enterprise
* fabiano@cnpm.com.br - http://fabianosqlserver.spaces.live.com/

( (55 - 14) 3404-3700

Em relação a performance um problema muito comum é a contenção no TempDB.

O Banco de dados TempDB é utilizado para várias operações, ao invés de escrever aqui quais são estas operações eu vou de CTRL-C + CTRL+V do Books Online que é mais facil.

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

·         Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.

·         Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

·         Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.

·         Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

No site do TechNet tem um excelente artigo falando sobre o TempDB no SQL Server 2005, segue o link Working with tempdb in SQL Server 2005.

No artigo acima podemos observar em “Troubleshooting contention caused by to DML operations” que quando temos problema de performance no TempDB é recomendado criar um arquivo de dados para cada processador existente no servidor e habilitar o trace flag 1118.

Obs.: Neste caso um processador Dual Core é considerado como 2 processadores, ou seja, criar 2 arquivos.

Conforme o artigo Q328551 a mesma recomendação também foi feita pela Microsoft para servidores rodando SQL Server 2000.

No TechEd 2007 participei de uma mesa redonda sobre Otimização de Performance com o SQL Server 2005 - Dicas e Truques e discutimos sobre o TraceFlag 1118, e os profissionais da Microsoft presentes reforçaram o que os artigos acima recomendam.

Lembro do Fabricio Catae Premier Field Engineer dizer o seguinte:

·         Por precaução e para não ter futuros problemas nós recomendamos o uso do TraceFlag 1118 e criamos um arquivo do TempDB para cada processador.

Concordo com ele, na duvida se o TempDB está sendo um problema ou não, antes de ter problema habilite o TraceFlag e crie um arquivo para cada processador do servidor.

Antes de continuar a falar sobre isso vamos entender um pouco mais sobre os Extents pois eles tem tudo a ver com o TraceFlag 1118.

No SQL Server existem dois tipos de extents, os extents mistos e extents uniformes.

Assim como o extent uniforme o extent misto tem 64 kb(8 páginas de 8k). A diferença entre eles é que um extent misto pode ter páginas de mais de um objeto diferente. Veja o exemplo abaixo.

Repare que no Extent Misto temos informações de vários objetos, Table2, Index1, Index2 e etc...

O SQL Server usa 2 tipos de controladores para alocação dos extents, o GAM e o SGAM.

·         GAM - Para os extents uniformes o SQL irá usar a Global Allocation Map que é responsável por registrar as alocações dos extents uniformes de todos os objetos do banco de dados. O GAM usa um bit para controlar se o extent está livre ou alocado. Se o bit valer 1 o extent está livre se o bit valor 0 o extent está alocado.

·         SGAM - Para os extents mistos o SQL irá usar a Shared Global Allocation Map como o próprio nome diz “Shared” ele é responsável por registrar quais são os extents mistos e qual deles tem páginas livres para uso. O SGAM também usa um bit para fazer este controle, se o bit for 1 então o extent é misto e tem pelo menos uma página livre para utilização, se o bit for 0 então o extent não é um extent misto ou então todas as páginas já estão utilizadas.

Vamos imaginar o seguinte cenário, um simples insert um uma nova tabela temporária.

Todo objeto criado no banco de dados é iniciado com um extent misto até que a tabela ocupe 8 páginas de dados, sabendo disso então podemos dizer que o SQL irá alocar um extend misto para gravar minha informação nesta tabela temporária. Para chegar neste extent misto e na página onde minha informação será gravada o DataBase Engine usa a controladora SGAM.

Ok já sei que o SQL aloca extents mistos para novos objetos, mas o que isso tem a ver com o TraceFlag e o TempDB?

Tem a ver que quando habilitamos o TraceFlag 1118 o SQL passará a não mais alocar extents mistos e sim extents uniformes, pulando a etapa que usa a SGAM para achar um extent misto.

Podemos observar que o SQL tem o trabalho de alocar extents mistos e conforme a tabela vai crescendo o SQL tem controlar se no extent atual cabe os dados que estão sendo inseridos ou se ele precisa alocar um novo extent,isso irá acontecer quando a tabela ficar maior que 64 k, para fazer este controle o SQL irá utilizar a SGAM e a PFS(controla o espaço livre dentro de uma página). Ao alocar direto um extend uniforme o SQL não precisa toda hora consultar a SGAM e a PFS para verificar se ele necessita alocar outro extent para inserir os dados.

O Luciano Moreira deu um show de explicação sobre como, quando, onde e porque utilizar o TraceFlag 1118 o artigo pode ser lido em Analisando o trace flag 1118.

Um detalhe importante é que recentemente(não tão recente assim) a Microsoft divulgou um artigo dizendo que ao fazer o que ela diz você pode ter problemas de desempenho. O artigo pode ser lido aqui Q936185,

O MVP Linchi Shea escreveu um artigo no seu blog falando sobre esse problema, Reduce the Contention on tempdb with Trace Flag 1118: To Enable, or Not to Enable?

Até a próxima e stay tuned!

______________________________________________________________
Fabiano Neves Amorim (MCP - MCTS - SQL Server)
Análise - NewCon Enterprise
* fabiano@cnpm.com.br - http://fabianosqlserver.spaces.live.com/

Novidades no Transact SQL do SQL Server 2008,

-- Criar uma tabela para teste

create table TabNome(Nome VarChar(80), SobreNome VarChar(80))

-- Incluir 4 linhas na tabela

-- Existe uma nova Sintax no SQL 2008 que permite incluir varias linhas com apenas um comando Insert.

-- Commando relativo ao SQL Server 2008

insert into TabNome(Nome, Sobrenome)

values('Fabiano','Amorim'),('Ricardo','Lopes'),('Eduardo','Silva'),('Maria','Oliveira')

-- Commando relativo ao SQL Server 2000, 2005

insert into TabNome(Nome, Sobrenome)

values('Fabiano','Amorim')

insert into TabNome(Nome, Sobrenome)

values('Ricardo','Lopes')

insert into TabNome(Nome, Sobrenome)

values('Eduardo','Silva')

insert into TabNome(Nome, Sobrenome)

values('Maria','Oliveira')

-- É possivel fazer um select passando uma lista como tabela segue um exemplo,

-- repare que o Alias do nome das colunas é passado após o nome da tabela.

select TabNome.*, Tab.Apelido

from TabNome

inner join (

  values('Fabiano','Amo'),('Ricardo','Lop'),('Eduardo','Sil'),('Maria','Oli')

           ) AS tab (Nome, Apelido)

ON TabNome.Nome = tab.Nome;

__________________________________________________________________
Fabiano Neves Amorim (MCP - MCTS - SQL Server)
Análise - NewCon Enterprise
* fabiano@cnpm.com.br - http://fabianosqlserver.spaces.live.com/

Primeiramente quero agradecer ao Sanches pelas dicas sobre os Posts, Fiquem a vontade para me enviar criticas ou sugestões para posts.

Esta semana fiz uma alteração em uma tabela do no nosso banco de dados onde mudei a ordem das colunas do meu índice cluster, isso pode ser muito útil caso as consultas que são executadas nesta tabela tenham as colunas do índice cluster no where. Vamos a um exemplo prático.

--Vamos criar uma tabela para teste

drop table teste

create table teste (ID   Int Identity(1,1) NOT NULL,

                    Nome VarChar(200) NOT NULL,

                    Data DateTime)

alter table teste add constraint xpk_Nome_ID primary key (Nome, ID)

go

set nocount on

declare @i Int

set @i = 0

while @i < 1000

begin

  insert into teste (nome,data) values('A', GetDate())

  insert into teste (nome,data) values('B', GetDate())

  insert into teste (nome,data) values('C', GetDate())

  insert into teste (nome,data) values('D', GetDate())

  insert into teste (nome,data) values('E', GetDate())

  set @i = @i + 1;

end

-- Vamos imaginar a seguinte consulta

-- Repare que o SQL não consegue fazer o seek porque a tabela está ordenada pela coluna Nome e não pela coluna ID

select * from teste

where id = 10

--Apaga a primary key

alter table teste drop constraint xpk_Nome_ID

GO

--Recria a primary key ordenado por ID e Nome

alter table teste add constraint xpk_ID_Nome primary key (ID, Nome)

-- Ao executar a consulta novamente conseguimos um seek.

select * from teste

where id = 10

Claro que isso depende de um estudo bem feito das consultas que são executadas na sua tabela, bem como a alteração dos valores das colunas(UPDATE) porque uma má alteração poderia causar fragmentação na tabela, mas fragmentação é assunto para um outro post.

Valeu

__________________________________________________________________
Fabiano Neves Amorim (MCP - MCTS - SQL Server)
Análise - NewCon Enterprise
* fabiano@cnpm.com.br - http://fabianosqlserver.spaces.live.com/

( (55 - 14) 3404-3700

Semana passada me deparei com um problema de performance de uma procedure chamada pela minha aplicação Win32 exibindo uma mensagem de TimeOut para o usuário. Somente após as estatísticas das tabelas serem atualizadas é que o processo conseguiu rodar.

Vamos analisar um pouco o problema.

Primeira pergunta: O que são e para que servem as estatísticas das tabelas?

O SQL Server guarda informações em relação aos registros das tabelas para que o Otimizador possa decidir qual o melhor plano de execução para uma determinada consulta baseado nos dados que ele lê nas statistics das tabelas.

Vamos a um exemplo:

Segunda pergunta: Como identificar o problema?

Identifiquei que o problema era um mal plano de execução gerado pelo SQL de uma consulta feita dentro da procedure que retornava os dados do processo.

Esta consulta fazia vários joins com tabelas com mais de 15 milhões de registros e o SQL Server estava fazendo um Index Scan em quase todas estas tabelas.

Rodei a procedure no SSMS com a opção para exibir o Plano de Execução (CTRL+M) ligada e tive o seguinte resultado.

Observando a imagem acima podemos identificar que as querys 5  e 6 são responsáveis pelo gargalo da procedure então me limitei em analisar estas consultas.

Após analisar as consultas constatei que o SQL Server não estava gerando um bom plano de execução para as consultas já que ele estava gerando vários Index Scan ao invés de Seeks.

Terceira pergunta: Como resolver o problema?

Após identificar que o problema era o plano de execução temos várias maneiras de forçar o SQL Server a gera determinado plano por exemplo com o uso de Hints, no meu caso achei melhor criar um cursor e dentro de cada linha do cursor eu executo a instrução SQL mas passando uma condição bem restritiva no WHERE baseado no valor do cursor fazendo o SQL Server gerar um plano de execução com vários Seeks o que foi bem melhor que apenas um Index Scan.

Fiz mais outras alterações como trocar os DataTypes das tabelas temporárias usadas na procedure DateTime para SmallDateTime, Int para SmallInt ou TyniInt, SET NOCOUNT ON dentre outros, mas o ideal é sempre melhorar o gargalo.

A melhora foi muito grande, vamos analisar os resultados no profiler.

Conclusão de 2:20 minutos para 0. J

Verificamos então como identificar o problema, fica a dica sobre Statistics e a analise dos planos de execução utilizando o CTRL-M, é claro que isso não funciona sempre mas ajuda.

__________________________________________________________________
Fabiano Neves Amorim (MCP - MCTS - SQL Server)
Análise - NewCon Enterprise
* fabiano@cnpm.com.br - http://fabianosqlserver.spaces.live.com/

( (55 - 14) 3404-3700

Meu primeiro Post no Blog.

Senhoras e senhores, inicia aqui mais um Blog sobre tecnologia, mais especificamente sobre SQL Server.

Minha intenção é colocar no blog dicas sobre o uso do SQL Server e fatos do dia a dia que podem ser de interesse dos leitores.

Sem mais delongas vamos ao que interessa SQL. :-)

Vai aqui uma dica muito boa para quem trabalha com o SQL e pode ser útil do dia a dia.

--Vamos criar uma tabela de exemplo

IF Object_ID('teste') IS NOT NULL

  DROP TABLE teste

GO

CREATE TABLE teste (ID   Int Identity(1,1),

                    Nome VarChar(200))

GO

-- Agora digamos que eu queira incluir 10 registros nesta tabela, temos várias formas de fazer isso

-- o mais comum de vermos nos sites por ai é:

DECLARE @i Int

SET @i = 0

WHILE @i < 10

BEGIN

  INSERT INTO teste VALUES(NewID())

  SET @i = @i + 1

END

go

-- Agora vamos ver uma forma mais simples.

IF Object_ID('teste') IS NOT NULL

  DROP TABLE teste

GO

CREATE TABLE teste (ID   Int Identity(1,1),

                    Nome VarChar(200))

GO

-- Usando o GO 10 o comando Insert será executavo 10 vezes.

INSERT INTO teste VALUES(NewID())

GO 10

Para quem está começando no SQL Server aqui vão algumas explicações sobre alguns comandos acima que podem ser desconhecidos.

1.       NewID() é uma função que retorna um conjunto de caracteres aleatórios.

2.       Obeject_ID() retorna o ObjectID de um determinado objeto no nosso exemplo a tabela teste. Esta informação está gravada na tabela sysobjects do SQL Server 2000 ou sys.objects do SQL Server 2005.

Por hoje é só, espero nos ajudar na nossa jornada SQL Server.