Blog – Fabiano Neves Amorim

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/

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/