Blog – Fabiano Neves Amorim

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/