Por que é recomendado armazenar BLOBs em tabelas separadas do SQL Server?

Embora eu discorde que os BLOBs devem estar apenas em outra tabela - eles não devem estar no banco de dados . Armazene um ponteiro para onde o arquivo reside no disco e, em seguida, obtenha-o do banco de dados ...

O principal problema que eles causam (para mim) é com a indexação. Usando XML com planos de consulta, porque todos os têm, vamos fazer uma tabela:

SELECT TOP 1000
ID = IDENTITY(INT,1,1),
deq.query_plan
INTO dbo.index_test
FROM sys.dm_exec_cached_plans AS dec
CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq

ALTER TABLE dbo.index_test ADD CONSTRAINT pk_id PRIMARY KEY CLUSTERED (ID)

São apenas 1000 linhas, mas verificando o tamanho ...

sp_BlitzIndex @DatabaseName = 'StackOverflow', @SchemaName = 'dbo', @TableName = 'index_test'

São mais de 40 MB para apenas 1.000 linhas. Supondo que você adicione 40 MB a cada 1.000 linhas, isso pode ficar muito feio rapidamente. O que acontece quando você atinge 1 milhão de linhas? Isso é apenas cerca de 1 TB de dados, lá.

Todas as consultas que precisam usar seu índice clusterizado agora precisam ler todos esses dados BLOB na memória quando a coluna de dados BLOB é referenciada.

Você consegue pensar em maneiras melhores de usar a memória do SQL Server do que armazenar BLOBs? Porque com certeza posso.

Expandindo-o para índices não clusterizados:

CREATE INDEX ix_noblob ON dbo.index_test (ID)

CREATE INDEX ix_returnoftheblob ON dbo.index_test (ID) INCLUDE (query_plan)

Você pode projetar seus índices não clusterizados para evitar amplamente a coluna BLOB para que as consultas regulares possam evitar o índice clusterizado, mas assim que você precisar dessa coluna BLOB, precisará do índice clusterizado.

Se você adicioná-lo como uma INCLUDEDcoluna a um índice não clusterizado para evitar um cenário de pesquisa de chave, você acabará com índices não clusterizados gigantescos:

Mais problemas que causam:

• Se alguém executar uma SELECT *consulta, obterá todos os dados BLOB.
• Eles ocupam espaço em backups e restaurações, tornando-os mais lentos
• Eles diminuem a velocidade DBCC CHECKDB, porque eu sei que você está verificando se há corrupção, certo?
• E se você fizer qualquer manutenção de índice, eles também diminuem a velocidade.

Espero que isto ajude!

Qual o tamanho dessas imagens e quantas você espera ter? Embora eu concorde principalmente com @sp_BlitzErik , acho que há alguns cenários em que não há problema em fazer isso e, portanto, ajudaria ter uma imagem mais clara do que realmente está sendo solicitado aqui.

Algumas opções a considerar que aliviam a maioria dos aspectos negativos apontados por Erik são:

• FILESTREAM (a partir do SQL Server 2008)
• FileTables (a partir do SQL Server 2012)

Ambas as opções são projetadas para ser um meio-termo entre armazenar BLOBs totalmente no SQL Server ou totalmente fora (exceto por uma coluna de string para manter o caminho). Eles permitem que os BLOBs façam parte do modelo de dados e participem de transações sem desperdiçar espaço no buffer pool (ou seja, memória). Os dados BLOB ainda estão incluídos nos backups, o que faz com que eles ocupem mais espaço e demorem mais para fazer backup erestaurar. No entanto, tenho dificuldade em ver isso como um verdadeiro negativo, pois, se for parte do aplicativo, ele precisa ser copiado de alguma forma, e ter apenas uma coluna de string contendo o caminho é completamente desconectado e permite que os arquivos BLOBs sejam obtidos excluídos sem indicação disso no banco de dados (ou seja, ponteiros inválidos / arquivos ausentes). Ele também permite que os arquivos sejam "excluídos" dentro do banco de dados, mas ainda existam no sistema de arquivos que precisarão ser limpos eventualmente (ou seja, dor de cabeça). Mas, se os arquivos forem ENORMES, talvez seja melhor deixar totalmente fora do SQL Server, exceto pela coluna de caminho.

Isso ajuda com a questão "dentro ou fora", mas não toca na questão da mesa única versus mesa múltipla. Posso dizer que, além dessa questão específica, certamente existem casos válidos para dividir tabelas em grupos de colunas com base em padrões de uso. Muitas vezes, quando se tem 50 ou mais colunas, algumas são acessadas com frequência e outras não. Algumas colunas são gravadas com frequência, enquanto outras são lidas principalmente. Separar colunas frequentemente acessadas versus raramente acessadas em várias tabelas com uma relação 1:1 é muitas vezes benéfica porque desperdiçar o espaço no Buffer Pool para dados que você provavelmente não está usando (semelhante ao por que armazenar imagens grandes emVARBINARY(MAX)colunas é um problema)? Você também aumenta o desempenho das colunas de acesso frequente reduzindo o tamanho da linha e, portanto, ajustando mais linhas em uma página de dados, tornando as leituras (físicas e lógicas) mais eficientes. Claro, você também introduz alguma ineficiência ao precisar duplicar o PK, e agora às vezes você precisa juntar as duas tabelas, o que também complica (mesmo que apenas um pouco) algumas consultas.

Portanto, existem várias abordagens que você pode adotar, e o que é melhor depende do seu ambiente e do que você está tentando realizar.

Fiquei com a impressão de que o SQL Server armazena apenas um ponteiro para alguma estrutura de dados BLOB dedicada na tabela

Não tão simples. Você pode encontrar algumas boas informações aqui, qual é o tamanho do ponteiro LOB para (MAX) tipos como Varchar, Varbinary, etc? , mas o básico é:

• TEXT, NTEXTe IMAGEtipos de dados (por padrão): ponteiro de 16 bytes
• VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)(por padrão):
  • Se os dados couberem na linha, eles serão colocados lá
  • Se os dados forem inferiores a aprox. 40.000 bytes (a postagem do blog vinculada mostra 40.000 como limite superior, mas meus testes mostraram um valor um pouco mais alto) E se houver espaço na linha para essa estrutura, haverá entre 1 e 5 links diretos para páginas LOB, começando em 24 bytes para o primeiro link para os primeiros 8.000 bytes e aumentando 12 bytes por cada link adicional para cada conjunto adicional de 8.000 bytes, até 72 bytes no máximo.
  • Se os dados forem superiores a aprox. 40.000 bytes OU não há espaço suficiente para armazenar o número apropriado de links diretos (por exemplo, apenas 40 bytes restantes na linha e um valor de 20.000 bytes precisa de 3 links que são 24 bytes para o primeiro mais 12 para os dois links adicionais para 48 bytes espaço total necessário na linha), haverá apenas um ponteiro de 24 bytes para uma página de árvore de texto que contém os links para as páginas LOB).

Se os dados devem ser armazenados no SQL Server por qualquer motivo, posso pensar em alguns benefícios para armazená-los em uma tabela separada. Alguns são mais convincentes do que outros.

1. Colocar os dados em uma tabela separada significa que você pode armazená-los em um banco de dados separado. Isso pode ter vantagens para manutenção programada. Por exemplo, você pode executar DBCC CHECKDBapenas no banco de dados que contém os dados BLOB.

2. Se você nem sempre colocar mais de 8000 bytes no BLOB, é possível que ele seja armazenado em linha para algumas linhas. Você pode não querer isso porque isso tornará as consultas que acessam dados usando o índice clusterizado, mesmo que a coluna não seja necessária para a consulta. Colocar os dados em uma tabela separada remove esse risco.

3. Quando armazenado fora da linha, o SQL Server usa um ponteiro de até 24 bytes para apontar para a nova página. Isso ocupa espaço e limita o número total de colunas BLOB que você pode adicionar a uma única tabela. Veja a resposta de srutzky para mais detalhes.

4. Um índice columnstore clusterizado não pode ser definido em uma tabela contendo uma coluna BLOB. Essa limitação foi removida será removida no SQL Server 2017.

5. Se você eventualmente decidir que os dados devem ser movidos para fora do SQL Server, pode ser mais fácil fazer essa alteração se os dados já estiverem em uma tabela separada.