quinta-feira, 29 de novembro de 2012

TEXT x VARCHAR(MAX)

Este é um dos posts sobre os exemplos apresentados na palestra sobre modelagem no 24 Hours of PASS - PT. Você pode ver a série de posts em Impactos da Modelagem na Performance do SQL Server.

Este eu peguei emprestado pra palestra a partir de um post que o Luti colocou no site da Sr Nimbus, mas como minha didática é melhor que a dele, vou colocar o exemplo com meus comentários aqui. ;-)

Colunas TEXT e VARCHAR(MAX) armazenam objetos grandes, ou LOB's (Large Objects). Sintaticamente são iguais: uma coluna TEXT ou VARCHAR(MAX) armazena um valor string de até 2GB por registro. No entanto há uma diferença que pode impactar severamente na quantidade de IO realizada por uma consulta em uma tabela que contenha colunas destes tipos, e também na performance do Data Cache, um dos principais mecanismos de otimização do SQL Server, que é a área de memória que serve de buffer para as páginas lidas de disco. Tudo isto por uma "pequena" diferença na forma como o armazenamento dos dados LOB é feita, dependendo do tipo usado para declarar a coluna.

O script a seguir demonstra isto. Copie ele para uma nova query no Management Studio (não esqueça de mudar para um banco de testes), vá executando os pedaços de código entre os comentários e veja os resultados. Qualquer dúvida posta uma pergunta!




/* ===============================================================================================================================
   Comparação entre modos de armazenamento de dados para os tipos de dados "large data type values" (varchar(max), nvarchar(max) e
   varbinary(max) e "large object values" (text, ntext, image)
   Por default os "large data types" armazenam dados "in row", o que pode ocasinar muito IO desnecessário e mau uso do data cache.
   Este comportamento pode ser modificado através da opção de tabela "large value types out of row"
   http://msdn.microsoft.com/en-us/library/ms189087(v=sql.105).aspx
   =============================================================================================================================== */

-- Tabela com campo LOB armazenado em coluna VARCHAR(MAX)

DROP TABLE dbo.TabelaTEXT 
CREATE TABLE dbo.TabelaTEXT (
ID INT IDENTITY NOT NULL CONSTRAINT PK_TabelaTEXT PRIMARY KEY
, Nome VARCHAR(100) NOT NULL DEFAULT ('Sr. Nimbus')
, DataRegistro DATETIME2 NOT NULL DEFAULT(SYSDATETIME())
, Texto TEXT NOT NULL DEFAULT (REPLICATE('A', 2000))
)
GO

-- Mesma estrutura, exceto campo LOB que é armazenado em coluna TEXT

DROP TABLE dbo.TabelaVARMAX 
CREATE TABLE dbo.TabelaVARMAX (
ID INT IDENTITY NOT NULL CONSTRAINT PK_TabelaVARMAX PRIMARY KEY
, Nome VARCHAR(100) NOT NULL DEFAULT ('Sr. Nimbus')
, DataRegistro DATETIME2 NOT NULL DEFAULT(SYSDATETIME())
, Texto VARCHAR(MAX) NOT NULL DEFAULT (REPLICATE('A', 2000))
)
GO

-- Popula as 2 tabelas

INSERT INTO dbo.TabelaTEXT DEFAULT VALUES
INSERT INTO dbo.TabelaVARMAX DEFAULT VALUES
GO 10000

select count(*) from TabelaTEXT
select count(*) from TabelaVARMAX

-- Vamos verificar a quantidade de IO realizada pelas consultas abaixo

SET STATISTICS IO ON

SELECT ID, Nome FROM dbo.TabelaTEXT
SELECT ID, Nome FROM dbo.TabelaVARMAX
-- TabelaTEXT > TabelaVARMAX
-- Em TabelaTEXT a área de dados contém campos LOB
-- SELECT não requisitou estes campos!

SET STATISTICS IO OFF

-- Tamanho e localização dos dados nas tabelas

SELECT
 OBJECT_NAME(object_id) AS ObjectName ,
 AU.type ,
 AU.type_desc ,
 AU.container_id ,
 AU.filegroup_id ,
 AU.total_pages ,
 AU.used_pages ,
 AU.data_pages
FROM SYS.system_internals_allocation_units AS AU
  INNER JOIN SYS.Partitions AS P
ON AU.Container_id = P.Partition_id
WHERE Object_ID IN (object_id('TabelaTEXT'), object_id('TabelaVARMAX'))
ORDER BY object_id, type

-- Mudando o comportamento de VARCHAR(MAX): opção "large value types out of row"

EXEC sp_tableoption 'TabelaVARMAX', 'large value types out of row', 1
GO

-- Para dados existentes, armazenamento só muda quando linha é modificada

UPDATE dbo.TabelaVARMAX SET Texto = Texto

-- Rebuild do índice para compactar tabela

ALTER INDEX PK_TabelaVARMAX ON dbo.TabelaVARMAX REBUILD

-- Vamos verificar a quantidade de IO realizada pelas consultas abaixo

SET STATISTICS IO ON

SELECT ID, Nome FROM dbo.TabelaTEXT
SELECT ID, Nome FROM dbo.TabelaVARMAX
-- TabelaTEXT == TabelaVARMAX
-- Ambas agora usam armazenamento de LOB's out of row

SET STATISTICS IO OFF