É bastante comum nos deparamos com databases que utilizam Large Objects. XML, armazenamento de arquivos (planilhas, imagens, documentos, etc) e logs gigantescos de aplicação são exemplos de utilização de LOBs.
Em termos práticos no SQL Server, podemos utilizar os seguintes tipos de dados para trabalhar com LOBs.
- varchar(max)
- nvarchar(max)
- varbinary(max)
- xml
- text
- ntext
- image
Ok, mas quais os cuidados eu devo ter ao estar utilizando campos LOB? Vamos rodar uma pequena demonstração para tocar alguns pontos importantes sobre performance com LOBs no SQL Server.
Para o nosso exemplo, vamos criar um database e uma tabela utilizando campo VARCHAR(max). Após isso, vamos fazer uma carga de 1 milhão de linhas com dados aleatórios para começar os testes de performance.
-- Create database create database LobData -- Cria tabela com campo varchar(max) drop table if exists dbo.InventarioDB_Varchar go create table dbo.InventarioDB_Varchar ( Id int identity(1,1) not null primary key , DtCriacao date not null , TipoObjeto varchar(32) , Texto varchar(max) ) go -- Realiza carga na tabela InventarioDB_Varchar (~23 segundos) insert into dbo.InventarioDB_Varchar (DtCriacao, TipoObjeto, Texto) select top 1000000 convert(date, c.create_date), c.type_desc, a.definition from sys.all_sql_modules a cross join sys.all_objects c
Ótimo, inserimos os registros na tabela. Vamos agora fazer uma consulta?
set statistics io, time on -- Vamos fazer uma consulta para retornar quantidade de objetos por tipo. select TipoObjeto , count(TipoObjeto) as [Count] from dbo.InventarioDB_Varchar group by TipoObjeto order by count(TipoObjeto) desc

A consulta rodou em menos de 1 segundo, mas vamos analisar o número de logical reads e o tempo de CPU gasto.

A consulta rodou bem rápido (143 ms), mas algo está estranho. Foi necessário ler 267 mil páginas de dados pra retornar a consulta. É um número bastante alto para uma tabela de 1 milhão de linhas, não? Vamos entender como funciona o armazenamento dos dados pra tentar melhorar isso.
Para começar, vamos conferir como ficou a distribuição dos dados dentro da tabela? Pra isso, estou criando uma procedure que realiza a consulta em DMVs com informações das estruturas internas dos dados nas tabelas (links da documentação das DMVs no final do post).
-- Cria procedure para retornar distribuição das páginas na tabela create or alter procedure dbo.GetPagesAllocation (@object sysname) as begin SELECT OBJECT_NAME ([sp].[object_id]) AS [Object Name] , [au].[type_desc] AS [Alloc Unit Type] , [fg].name as [FileGroup] , [au].total_pages AS TotalPages , [au].data_pages AS DataPages , [au].used_pages as UsedPages , sp.[rows] AS [Rows] FROM sys.partitions AS [sp] inner join sys.system_internals_allocation_units [au] on [au].container_id = sp.[partition_id] inner join sys.filegroups [fg] on [fg].data_space_id = [au].filegroup_id WHERE [sp].[object_id] = ( CASE WHEN (@object IS NULL) THEN [sp].[object_id] ELSE OBJECT_ID (@object) END ) end go -- Verifica alocação dos dados nas tabelas exec dbo.GetPagesAllocation @object = 'InventarioDB_Varchar'
Ao executar a procedure, obtivemos o resultado abaixo. Perceba que a distribuição das páginas no nosso exemplo é feita entre IN_ROW_DATA e LOB_DATA. O que é isso?

Cada partição de uma tabela é alocada em um tipo de Allocation Unit, que pode ser IN_ROW_DATA, ROW_OVERFLOW_DATA ou LOB_DATA. Se quiser ler mais sobre Allocations Units, alguns links: Link1 | Link2
O SQL Server trata os objetos LOBs de forma distinta, considerando uns como Large User-Defined Type (varchar(max), nvarchar(max), varbinary(max) e xml) e outros como BLOB (text, ntext e image). Mas o que isso implica em termos práticos?
Por exemplo, se você usa VARCHAR(MAX), o SQL Server por default vai tentar armazenar o conteúdo LOB dentro da página de dados (IN_ROW_DATA), respeitando o limite de 8000 bytes. Caso o valor seja maior então a coluna é armazenado no LOB Storage (LOB_DATA) tendo apenas um ponteiro dentro do IN_ROW_DATA.
No exemplo de um campo TEXT, o SQL Server por default armazena o conteúdo LOB diretamente no LOB Storage (LOB_DATA).
Mas é possível alterar esse comportamento. Existem duas Table Options específicas pra armazenamento LOBs. Você pode alterá-las através do comando sp_tableoption. Segue abaixo parte da documentação do site da Microsoft, disponível também nesse link. Mas prestem atenção, cada configuração vale para tipos de dados distintos.
text in row | When OFF or 0 (disabled, the default), it does not change current behavior, and there is no BLOB in row. When specified and @OptionValue is ON (enabled) or an integer value from 24 through 7000, new text, ntext, or image strings are stored directly in the data row. All existing BLOB (binary large object: text, ntext, or image data) will be changed to text in row format when the BLOB value is updated. For more information, see Remarks. |
large value types out of row | 1 = varchar(max), nvarchar(max), varbinary(max), xml and large user-defined type (UDT) columns in the table are stored out of row, with a 16-byte pointer to the root. 0 = varchar(max), nvarchar(max), varbinary(max), xml and large UDT values are stored directly in the data row, up to a limit of 8000 bytes and as long as the value can fit in the record. If the value does not fit in the record, a pointer is stored in-row and the rest is stored out of row in the LOB storage space. 0 is the default value. Large user-defined type (UDT) applies to: SQL Server 2008 through SQL Server 2017. Use the TEXTIMAGE_ON option of CREATE TABLE to specify a location for storage of large data types. |
Alguém percebeu que mesmo a configuração “large value types out of row” igual a 0, eu ainda tenho páginas no LOB_DATA? Por quê?

Conforme explicado acima o SQL Server armazena no ROW_DATA apenas os valores até 8000 bytes. Caso seja maior, ele utiliza um ponteiro para uma página LOB_DATA.
No meu exemplo, tenho 96399 linhas com mais de 8000 bytes e por isso foram armazenadas ao LOB_DATA.
-- Retorna registros com Texto maior que 8000 bytes select count(Id) as [Count] from dbo.InventarioDB_Varchar where DATALENGTH(Texto) > 8000 -- Count -- ----------- -- 96399
Agora que entendemos o armazenamento de LOBs, como fica a performance ao buscar os dados? Vamos voltar ao nosso exemplo.
Lembrando que a consulta utilizada não necessita do valor da coluna Texto (varchar(max)). Pensando nisso, seria uma boa alternativa deixar os registros LOBs em um Allocation Unit exclusivo para LOB_DATA, certo? Dessa maneira teríamos menos páginas de dados IN_ROW_DATA e consequentemente menas leituras necessárias para retornar nossa consulta. Então vamos fazer esse teste.
-- Vamos habilitar o "large value types out of row" para a tabela EXEC sp_tableoption 'InventarioDB_Varchar', 'large value types out of row', 1;
Ao alterar o modo de armazenamento da tabela InventarioBD_Varchar ele vai considerar apenas as novas operações. O que já está armazenado continua do mesmo jeito. Qual a solução então? Forçar uma operação em todas as linhas sem alterar o conteúdo. Após isso, vamos rodar um Rebuild e consultar como ficou a distribuição das páginas de dados na tabela.
-- Necessário atualizar todo o campo blob simplesmente pra assumir a nova configuração de armazenamento. update InventarioDB_Varchar set Texto = Texto -- Realiza o REBUILD pra eliminar eventual fragmentação alter index all on InventarioDB_Varchar rebuild -- Por fim, verifica alocação dos dados com novo modelo de armazenamento exec dbo.GetPagesAllocation @object = 'InventarioDB_Varchar'

Uouuuu! A diferença é bem grande. Antes haviam 262396 páginas IN_ROW_DATA e agora temos 7091. Porém o número de páginas LOB_DATA aumentou para 483605 comparado a 244524. Vamos rodar novamente a consulta e comparar os resultados.
set statistics io, time on -- Vamos fazer uma consulta para retornar quantidade de objetos por tipo. select TipoObjeto , count(TipoObjeto) as [Count] from dbo.InventarioDB_Varchar group by TipoObjeto order by count(TipoObjeto) desc

Obtivemos uma melhora significativa de performance, principalmente no número de Logical Reads. Conforme comparativo entre as duas execuções ANTES \ DEPOIS.
Logical Reads | CPU Time Ms | Elapsed Time Ms | |
ANTES | 267398 | 312 | 143 |
DEPOIS | 7076 | 78 | 81 |
OBSERVAÇÕES IMPORTANTES
Pessoal, por favor se atentem as observações abaixo.
- O nosso exemplo é uma consulta leve, mas considerando um caso com tabelas maiores e alto volume de chamadas simultâneas, isso pode dar um fôlego grande para o ambiente e salvar a sua pele.
- Cuidado, a consulta apresentada não retorna a coluna TEXTO (varchar(MAX)) e devido a isso ele não acessa as páginas LOB_DATA. Se o seu cenário for diferente, estude muito bem o seu ambiente para saber se essa alteração faz sentido ou não, pois o seu custo pode ficar maior.
- Faça o mesmo teste com uma coluna TEXT e observe que por default ele já separa os valores LOBs em LOB_DATA. Não estou falando que TEXT é melhor, apenas notem o comportamento default. E não esqueçam: TEXT já está marcado como DEPRECATED.
Alguma dúvida, correção, crítica, sugestão? Por favor respondam nos comentários ou diretamente por email: guilherme@datatuning.com.br
REFERÊNCIAS
https://docs.microsoft.com/pt-br/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-2017
https://www.microsoftpressstore.com/articles/article.aspx?p=2225060
SQL Server – Understanding Allocation Units – In Row Data, LOB Data & Row Overflow Data
Antigo post do Luti (site da Nimbus que saiu do ar) e tenho apenas ele em modelo PDF, graças ao acervo digital do meu amigo Igor rs.