Blog – Fabiano Neves Amorim

Ao migrar de SQL 2000 para SQL 2005 existem algumas considerações importantes no Transact-SQL que necessitam de nossa atenção, por exemplo existem alguns comandos que hoje estão rodando normalmente no SQL 2000 e ao migrar simplesmente não funcionam, vamos a alguns exemplos.

 

/*

  Exemplo de sintaxes que não funcionam ao migrar de SQL 2000 para 2005

*/

 

use master

 

if exists(select * from master.dbo.sysdatabases where name = ‘TesteCompatibilidade’)

begin

  drop database TesteCompatibilidade

end

 

create database TesteCompatibilidade

 

use TesteCompatibilidade

 

if OBJECT_ID(‘teste’) is not null

begin

  drop table teste

end

GO

 

— Vamos criar uma tabela para teste

create table teste (id int Identity(1,1), nome VarChar(200) default NEWID())

GO

 

— Vamos incluir 10 linhas na tabela teste

insert into teste values(default)

go 10

 

create index ix_teste on teste(nome)

GO

 

— Seta o banco para usar o nivel de compatibilidade do SQL Server 2000(80)

— Consultas que funcionam normalmente no SQL Server 2000

sp_dbcmptlevel TesteCompatibilidade, 80

GO

–Consulta forcando o uso de um indice sem colocar o WITH depois do nome da tabela

select * from teste(index=ix_teste)

GO

— Consulta usando order by baseado em uma coluna, porem o alias ID é do mesmo nome da coluna ID

— repare que o SQL ordenou a tabela pelo campo ID da tabela e não pelo alias ID(campo nome)

select ID, Nome as ID  from teste order by ID

GO

–Consulta usando order by passando o Nome da tabela mais o alias da coluna

select ID, Nome as AliasParaNome  from teste order by teste.AliasParaNome

GO

 

— Marca o banco para usar o nivel de compatibilidade do SQL Server 2005

— Os mesmo selects efetuados acima não funcionan no SQL Server 2005

sp_dbcmptlevel TesteCompatibilidade, 90

GO

–Consulta forcando o uso de um indice sem colocar o WITH depois do nome da tabela

select * from teste(index=ix_teste)

GO

— Consulta usando order by baseado em uma coluna, porem o alias ID é do mesmo nome da coluna ID

— repare que o SQL ordenou a tabela pelo campo ID da tabela e não pelo alias ID(campo nome)

select ID, Nome as ID  from teste order by ID

GO

–Consulta usando order by passando o Nome da tabela mais o alias da coluna

select ID, Nome as AliasParaNome  from teste order by teste.AliasParaNome

 

A Microsoft disponibiliza um software que ajuda a evitar surpresas como estas, ele se chama “Microsoft SQL Server 2005 Upgrade Advisor” vale a pena baixar o software e rodar ele na sua base para ver as incompatibilidades. Também vale a pena dar uma lida no “SQL Server 2005 Upgrade Technical Reference Guide”.

 

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

( (55 – 14) 3404-3700

Em relação a performance uma falha muito encontrada em banco de dados SQL Server é a má escolha dos datatypes das colunas.

 

É muito importante conhecer e escolher corretamente os datatypes das colunas nas suas tabelas por causa dos tamanho que elas ocupam  no seu banco de dados, por exemplo vamos imaginar o seguinte cenário.

 

create table testeA (ID   BigInt Primary Key,

                     Nome NChar(80),

                     Data DateTime,

                     Ano  Int,

                     Mes  Int)

 

/*

ID – BigInt = 8 bytes

Nome NChar() = 160 bytes(2 bytes para cada caracter unicode)

Data – DateTime = 8 bytes

Ano – Int = 4 bytes

Mes – Int = 4 bytes

Total = 184 bytes de dados por linha.

*/

 

— Esta mesma tabela poderia ter a seguinte estrutura

 

create table testeB (ID   Int Primary Key,

                     Nome Char(80),

                     Data SmallDateTime,

                     Ano  SmallInt,

                     Mes  TinyInt)

 

/*

ID – Int = 4 bytes

Nome Char() = 80 bytes(1 bytes para cada caracter)

Data – DateTime = 4 bytes

Ano – SmallInt = 2 bytes

Mes – TinyInt = 1 byte

Total = 91 bytes de dados por linha.

*/

 

Uma página de dados do SQL Server tem uma tamanho fixo de 8k,

ou 8192 bytes sendo distribuidos entre 3 partes: Page Header 96 bytes, Data Rows e Row OffSet Array.

 

 

Considerando que o SQL Server tem 8096 bytes livres(8192 – 96) para armazenamento dos dados da minha tabela e o ArrayOffSet(2 bytes) podemos dizer que dentro de cada página da minha tabela testeA temos 43 linhas de dados gravados, já na tabela testeB temos 87 registros.

 

Ou seja, a tabela testeB ocupa a metade do tamanho da tabela testeA, porque isso é importante? Vamos fazer alguns testes de select.

 

— Primeiro vamos incluir 5000 registros em cada tabela

set NoCount on

declare @i Int

set @i = 0

while @i < 5000

begin

  insert into testeA(ID, Nome, Data, Ano, Mes)

  values(@i, ‘Fabiano – ‘ + Convert(VarChar, @i), GetDate(), Year(GetDate()), Month(GetDate()))

  insert into testeB(ID, Nome, Data, Ano, Mes)

  values(@i, ‘Fabiano – ‘ + Convert(VarChar, @i), GetDate(), Year(GetDate()), Month(GetDate()))

  set @i = @i + 1

end

 

— Repare que o SQL alocou 122 paginas para a tabela

DBCC SHOWCONTIG(TesteA)

— Pages Scanned…………………………..: 122

 

— Podemos fazer a seguinte conta para verificar qual o tamanho da tabela

— 122 páginas vezes o tamanho ocupado por cada pagina / 1024 para converter o tamanho em Kb

select (122 * 8192) / 1024 — 976

— Confirmando o tamanho da tabela

sp_spaceUsed TesteA — Data = 976

 

–Vamos fazer um select na tabela TesteA e verificar quantos IOs são feitos para retornar 1/5 da tabela.

set statistics io on

select * from testeA

where ID < 1000

— Table ‘testeA’. Scan count 1, logical reads 26, physical reads 0, read-ahead reads 0.

 

— Vamos verificar o mesmo select na tabela TesteB,

— Observe que o Select na tabela testeB fez bem menos leitura para retornar os dados

— obtendo assim ganho de performance.

select * from testeB

where ID < 1000

— Table ‘testeB’. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0.

 

A escolha dos data types são muito importantes para evitar IOs desnecessários e evitar que seu banco de dados se torne muito grande rapidamente.

No projeto REAL da Microsoft, a equipe de desenvolvimento opotu por definir os campos do tipo Data como Int para poder ganhar 4 bytes de diferença entre o DateTime e o Int, a data é gravada no formato ‘yyyymmdd’.

 

Vale lembrar tambem que no SQL Server 2005 Enterprise service pack 2 existe o campo VarDecimal que vem para substituir os campos Decimal e Numeric. Você pode ler mais sobre ele no link vardecimalStorage.docx

 

Fica a seguinte regra: “Quanto mais dados em menos páginas, menos leitura para retornar mais dados”. J

 

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

 

 

 

 

É muito comum durante o desenvolvimento de um código SQL necessitarmos zerar o valor das variáveis que serão utilizadas no código.

Uma dica em relação a performance é que o comando SELECT é mais rápido do que o SET, porém está regra só se aplica quando podemos substituir um bloco de SET por um SELECT por ex:

 

— Executa um loop zerando o valor de 10 variaveis utilizando SET

— Gere o plano de execução e repare que no plano de execução que no Loop o SQL gera um SELECT para cada comando SET.

DECLARE @i Int, @Test1 int, @Start datetime

DECLARE @V1 Char(6),

        @V2 Char(6),

        @V3 Char(6),

        @V4 Char(6),

        @V5 Char(6),

        @V6 Char(6),

        @V7 Char(6),

        @V8 Char(6),

        @V9 Char(6),

        @V10 Char(6);

 

SET @Test1 = 0

SET @i = 0

SET @Start = GetDate()

WHILE @i < 50000

BEGIN

  SET @V1 = ”

  SET @V2 = ”

  SET @V3 = ”

  SET @V4 = ”

  SET @V5 = ”

  SET @V6 = ”

  SET @V7 = ”

  SET @V8 = ”

  SET @V9 = ”

  SET @V10 = ”

      SET @i = @i + 1                  

END                               

SET @Test1 = DATEDIFF(ms, @Start, GetDate())

SELECT @test1

 

GO

— Executa um loop zerando o valor de 10 variaveis utilizando SELECT

— Diferente do primeiro plano de execução o SQL gerou apenas 1 instrução para setar os valores para as 10 variáveis.

DECLARE @i Int, @Test1 int, @Start datetime

DECLARE @V1 Char(6),

        @V2 Char(6),

        @V3 Char(6),

        @V4 Char(6),

        @V5 Char(6),

        @V6 Char(6),

        @V7 Char(6),

        @V8 Char(6),

        @V9 Char(6),

        @V10 Char(6);

 

SET @Test1 = 0

SET @i = 0

SET @Start = GetDate()

WHILE @i < 50000

BEGIN

SELECT @V1 = ”,

       @V2 = ”,

       @V3 = ”,

       @V4 = ”,

       @V5 = ”,

       @V6 = ”,

       @V7 = ”,

       @V8 = ”,

       @V9 = ”,

       @V10 = ”,

       @i = @i + 1;

END                               

SET @Test1 = DATEDIFF(ms, @Start, GetDate())

SELECT @test1

 

Uma outra observação importante é que o SQL usou muito mais CPU no primeiro comando do que no segundo vejamos o profiler.

 

 

 

Fica a dica de utilizar o comando SELECT para atribuir valor a um bloco de variáveis.

 

Segue o link de um excelente artigo sobre o assunto.

http://vyaskn.tripod.com/differences_between_set_and_select.htm

 

__________________________________________________________________ 
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 participei de um treinamento de SQL Server na empresa em que falamos sobre execution plan, durante o treinamento surgiram alguns operadores de joins em nossas consultas de exemplo e percebi a necessidade de falar mais sobre alguns tipos de joins, ou seja, assunto para o Blog.

 

Estou lendo o livro SQL Server 2005 Query Tunning and Optimization e vou aproveitar os excelentes exemplos e explicações de como os operadores de join funcionam.

 

Vamos nos basear na seguinte consulta para os exemplos:

 

Select codigo from tabela1

Inner join tabela2

  On tabela1.id = tabela2.id

Where tabela1.id = 10

  

Nested Loops Joins – Compara cada linha de uma tabela(também conhecido por OUTER TABLE(FROM)) com cada linha de outra tabela(também conhecido por inner table) procurando pelas linhas que satisfaçam o relacionamento.

Pseudo-Código:

 

for each row tabela1 in the outer table

  for each row tabela2 in the inner table

     if tabela1 joins with tabela2

        return(tabela1, tabela2)

 

Merge Join  – Trabalha em simultâneo lendo e comparando cada linha das duas tabelas ordenadas pelo relacionamento. Para cada etapa compara a linha atual para cada valor na tabela do join, se o valor for igual a entrada então ele retorna o join e continua a comparação. Se o valor não for igual ele descarta o resultado e continua a próxima leitura. Um Merge Join não necessariamente irá ler toda a tabela, quando a leitura chegar no fim de qualquer tabela envolvida no join o loop é parado.

Pseudo-Código:

 

Get first row tabela1 from input 1

Get first row tabela2 from input 1

While not at the end of either input

Begin

  If tabela1 joins with tabela2

  Begin

    Output(tabela1, tabela2)

    Get next row tabela2 from input 2

  end

  else if tabela1 < tabela2

    get next row tabela1 from input 1

  else

    get next row tabela2 form input 2

end

 

Hash Join – O hash join compartilha alguns conceitos do Merge Join, o algoritmo de Hash é executado em duas fases conhecidas por Build(Construção) e Probe(prova ou experimento). Durante a fase de Build o SQL irá ler todas as linhas do primeiro input(from) e criar uma tabela com os dados lidos mais o relacionamento gerando uma Hash Table em memória. Na fase do probe o SQL irá ler cada linha do segundo input(tabela do join) gerar o valor hash e verificar se o seu valor tem o mesmo valor da tabela Hash table recém criada retornando todas as linhas que tem o mesmo valor. É importante destacar que não necessariamente a Hash table sempre será gerada em memória pois o SQL irá reservar a quantidade de memória necessária para criação da tabela antes de cria-lá baseado nas statisticas geradas pelo otimizador. Caso durante e build o SQL necessite de mais memória para gerar a tabela Hash ele pode gravar os dados em um espaço temporário ou seja, TempDB.

Pseudo-Código:

 

For each row tabela1 in the build table

Begin

  Calculate hash value on tabela1 join key(s)

  Insert tabela1 into the appropriate hash bucket

End

For each row tabela2 in the probe table

Begin

  Calculate hash value on tabela2 join key(s)

  For each row tabela1 in the corresponding hash bucket

    If tabela1 with tabela2

      Ouput (tabela1, tabela2)

end

 

Cabe a dica que para os seus testes você pode forcar o SQL a usar um determinado tipo de join se os exemplos:

 

SELECT Tabela1.ID

FROM Tabela1

INNER LOOP JOIN Tabela2

  ON Tabela1.ID = Tabela2.ID

WHERE Tabela1.ID = 10

GO

SELECT Tabela1.ID

FROM Tabela1

INNER MERGE JOIN Tabela2

  ON Tabela1.ID = Tabela2.ID

WHERE Tabela1.ID = 10

GO

SELECT Tabela1.ID

FROM Tabela1

INNER HASH JOIN Tabela2

  ON Tabela1.ID = Tabela2.ID

WHERE Tabela1.ID = 10

 

Fonte – Inside SQL Servr 2005: Query Tunning and Optimization.

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

( (55 – 14) 3404-3700