LOB_DATA, varreduras de tabela lentas e algumas questões de E/S

A presença do campo XML faz com que a maioria dos dados da tabela sejam localizados nas páginas LOB_DATA (na verdade, ~90% das páginas da tabela são LOB_DATA).

Simplesmente ter a coluna XML na tabela não tem esse efeito. É a presença de dados XML que, sob certas condições , faz com que parte dos dados de uma linha seja armazenada fora da linha, nas páginas LOB_DATA. E embora um (ou talvez vários ;-) possa argumentar que duh, a XMLcoluna implica que realmente haverá dados XML, não é garantido que os dados XML precisem ser armazenados fora da linha: a menos que a linha já esteja praticamente preenchida além de serem quaisquer dados XML, documentos pequenos (até 8.000 bytes) podem caber em uma linha e nunca ir para uma página LOB_DATA.

Estou correto ao pensar que as páginas LOB_DATA podem causar verificações lentas não apenas por causa de seu tamanho, mas também porque o SQL Server não pode verificar o índice clusterizado com eficiência quando há muitas páginas LOB_DATA na tabela?

Scanning refere-se a olhar para todas as linhas. Obviamente, quando uma página de dados é lida, todos os dados da linha são lidos, mesmo que você tenha selecionado um subconjunto das colunas. A diferença com os dados LOB é que, se você não selecionar essa coluna, os dados fora da linha não serão lidos. Portanto, não é realmente justo tirar uma conclusão sobre a eficiência com que o SQL Server pode verificar esse índice agrupado, pois você não testou exatamente isso (ou testou metade dele). Você selecionou todas as colunas, o que inclui a coluna XML e, como você mencionou, é onde a maioria dos dados está localizada.

Portanto, já sabemos que o SELECT TOP 1000 *teste não estava apenas lendo uma série de páginas de dados de 8k, todas seguidas, mas sim pulando para outros locais em cada linha . A estrutura exata desses dados LOB pode variar com base em seu tamanho. Com base na pesquisa mostrada aqui ( Qual é o tamanho do ponteiro LOB para (MAX) tipos como Varchar, Varbinary, etc? ), existem dois tipos de alocações de LOB fora da linha:

1. Inline Root -- para dados entre 8001 e 40.000 (na verdade 42.000) bytes, se o espaço permitir, haverá de 1 a 5 ponteiros (24 - 72 bytes) IN ROW que apontam diretamente para a(s) página(s) LOB.
2. TEXT_TREE -- para dados acima de 42.000 bytes, ou se os ponteiros de 1 a 5 não couberem na linha, haverá apenas um ponteiro de 24 bytes para a página inicial de uma lista de ponteiros para as páginas LOB (ou seja, o " página text_tree").

Uma dessas duas situações está ocorrendo toda vez que você recupera dados LOB com mais de 8.000 bytes ou simplesmente não couberam na linha. Publiquei um script de teste no PasteBin.com ( script T-SQL para testar alocações e leituras de LOB ) que mostra os 3 tipos de alocações de LOB (com base no tamanho dos dados), bem como o efeito que cada um deles tem na lógica e leituras físicas. No seu caso, se os dados XML realmente tiverem menos de 42.000 bytes por linha, nenhum deles (ou muito pouco deles) deve estar na estrutura TEXT_TREE menos eficiente.

Se você quiser testar a rapidez com que o SQL Server pode verificar esse índice clusterizado, faça o seguinte, SELECT TOP 1000mas especifique uma ou mais colunas que não incluam essa coluna XML. Como isso afeta seus resultados? Deve ser um pouco mais rápido.

é considerado razoável ter tal estrutura de tabela/padrão de dados?

Dado que temos uma descrição incompleta da estrutura real da tabela e do padrão de dados, qualquer resposta pode não ser a ideal, dependendo de quais são os detalhes ausentes. Com isso em mente, eu diria que não há nada obviamente irracional sobre sua estrutura de tabela ou padrão de dados.

Posso (no aplicativo ac#) compactar XML de 20 KB para ~ 2,5 KB e armazená-lo na coluna VARBINARY, impedindo o uso de páginas de dados LOB. Isso acelera SELECTs 20x vezes em meus testes.

Isso tornou a seleção de todas as colunas, ou mesmo apenas dos dados XML (agora em VARBINARY) mais rápida, mas na verdade prejudica as consultas que não selecionam os dados "XML". Supondo que você tenha cerca de 50 bytes nas outras colunas e tenha um FILLFACTORde 100, então:

• Sem compactação: 15k de XMLdados devem exigir 2 páginas LOB_DATA, que requerem 2 ponteiros para a Raiz Inline. O primeiro ponteiro tem 24 bytes e o segundo tem 12, para um total de 36 bytes armazenados em linha para os dados XML. O tamanho total da linha é de 86 bytes e você pode ajustar cerca de 93 dessas linhas em uma página de dados de 8060 bytes. Portanto, 1 milhão de linhas requer 10.753 páginas de dados.

• Compactação personalizada: 2,5k de VARBINARYdados caberão na linha. O tamanho total da linha é de 2.610 (2,5 * 1.024 = 2.560) bytes e você pode ajustar apenas 3 dessas linhas em uma página de dados de 8.060 bytes. Portanto, 1 milhão de linhas requer 333.334 páginas de dados.

Portanto, a implementação da compactação personalizada resulta em um aumento de 30 vezes nas páginas de dados para o Clustered Index. Ou seja, todas as consultas usando uma varredura de índice clusterizado agora têm cerca de 322.500 páginas de dados a mais para ler. Consulte a seção detalhada abaixo para ramificações adicionais de fazer esse tipo de compactação.

Gostaria de alertar contra qualquer refatoração com base no desempenho de arquivos SELECT TOP 1000 *. Não é provável que seja uma consulta que o aplicativo emitirá e não deve ser usada como a única base para otimizações potencialmente desnecessárias.

Para obter informações mais detalhadas e mais testes para tentar, consulte a seção abaixo.

Esta pergunta não pode receber uma resposta definitiva, mas podemos pelo menos fazer algum progresso e sugerir pesquisas adicionais para nos ajudar a chegar mais perto de descobrir o problema exato (de preferência com base em evidências).

O que nós sabemos:

1. A tabela tem aproximadamente 1 milhão de linhas
2. O tamanho da tabela é de aproximadamente 15 GB
3. A tabela contém uma XMLcoluna e várias outras colunas dos tipos: INT, BIGINT, UNIQUEIDENTIFIER, "etc"
4. XMLcoluna "tamanho" é, em média , aproximadamente 15k
5. Depois de executar DBCC DROPCLEANBUFFERS, leva de 20 a 25 segundos para a seguinte consulta ser concluída:SELECT TOP 1000 * FROM TABLE
6. O Clustered Index está sendo verificado
7. A fragmentação no Clustered Index está próxima de 0%

O que achamos que sabemos:

1. Nenhuma outra atividade de disco fora dessas consultas. Tem certeza? Mesmo que não haja outras consultas do usuário, estão ocorrendo operações em segundo plano? Existem processos externos ao SQL Server em execução na mesma máquina que podem estar ocupando parte do IO? Pode não haver, mas não está claro com base apenas nas informações fornecidas.
2. 15 MB de dados XML estão sendo retornados. Em que se baseia este número? Uma estimativa derivada de 1.000 linhas vezes a média de 15k de dados XML por linha? Ou uma agregação programática do que foi recebido para aquela consulta? Se for apenas uma estimativa, eu não confiaria nisso, pois a distribuição dos dados XML pode não ser igual à sugerida por uma média simples.

3. A compactação XML pode ajudar. Como exatamente você faria a compactação no .NET? Através das classes GZipStream ou DeflateStream ? Esta não é uma opção de custo zero. Certamente comprimirá alguns dos dados em uma grande porcentagem, mas também exigirá mais CPU, pois você precisará de um processo adicional para compactar/descompactar os dados a cada vez. Este plano também removeria completamente sua capacidade de:

   • consulte os dados XML por meio das funções .nodes, .value, .querye .modifyXML.

   • indexar os dados XML.

     Lembre-se (já que você mencionou que XML é "altamente redundante") que o tipo de XMLdados já está otimizado, pois armazena os nomes de elementos e atributos em um dicionário, atribuindo um ID de índice inteiro a cada item e, em seguida, usando esse ID inteiro ao longo do documento (portanto, não repete o nome completo a cada uso, nem o repete novamente como uma marca de fechamento para elementos). Os dados reais também têm espaços em branco estranhos removidos. É por isso que os documentos XML extraídos não retêm sua estrutura original e porque os elementos vazios são extraídos como <element />se tivessem entrado como<element></element>. Portanto, qualquer ganho de compactação via GZip (ou qualquer outra coisa) só será encontrado comprimindo os valores de elemento e/ou atributo, que é uma área de superfície muito menor que pode ser melhorada do que a maioria esperaria e provavelmente não vale a pena a perda de capacidades conforme observado diretamente acima.

     Lembre-se também de que compactar os dados XML e armazenar o VARBINARY(MAX)resultado não eliminará o acesso LOB, apenas o reduzirá. Dependendo do tamanho do restante dos dados na linha, o valor compactado pode caber na linha ou ainda pode exigir páginas LOB.

Essas informações, embora úteis, não são suficientes. Existem muitos fatores que influenciam o desempenho da consulta, portanto, precisamos de uma imagem muito mais detalhada do que está acontecendo.

O que não sabemos, mas precisamos:

1. Por que o desempenho SELECT *importa? Este é um padrão que você usa no código. Em caso afirmativo, por quê?
2. Qual é o desempenho de selecionar apenas a coluna XML? Quais são as estatísticas e o tempo se você fizer apenas: SELECT TOP 1000 XmlColumn FROM TABLE;?

3. Quanto dos 20 a 25 segundos necessários para retornar essas 1.000 linhas está relacionado a fatores de rede (passando os dados pela rede) e quanto está relacionado a fatores de cliente (representando esses aproximadamente 15 MB mais o restante dos não Dados XML na grade no SSMS ou possivelmente salvando no disco)?

   Fatorar esses dois aspectos da operação às vezes pode ser feito simplesmente não retornando os dados. Agora, pode-se pensar em selecionar uma tabela temporária ou uma variável de tabela, mas isso apenas introduziria algumas novas variáveis ​​(ou seja, E/S de disco para tempdbgravações de log de transações, possível crescimento automático de dados tempdb e/ou arquivo de log, necessidade espaço no Buffer Pool, etc). Todos esses novos fatores podem realmente aumentar o tempo de consulta. Em vez disso, normalmente armazeno as colunas em variáveis ​​(do tipo de dados apropriado; não SQL_VARIANT) que são substituídas a cada nova linha (ou seja, SELECT @Column1 = tab.Column1,...).

   NO ENTANTO , como foi apontado por @PaulWhite neste DBA.StackExchange Q & A, Lógica lê diferente ao acessar os mesmos dados LOB , com pesquisa adicional de minha autoria postada no PasteBin ( script T-SQL para testar vários cenários para leituras LOB ) , LOBs não são acessados ​​consistentemente entre SELECT, SELECT INTO, SELECT @XmlVariable = XmlColumn, SELECT @XmlVariable = XmlColumn.query(N'/')e SELECT @NVarCharVariable = CONVERT(NVARCHAR(MAX), XmlColumn). Portanto, nossas opções são um pouco mais limitadas aqui, mas aqui está o que pode ser feito:

   1. Elimine problemas de rede executando a consulta no servidor que executa o SQL Server, seja no SSMS ou no SQLCMD.EXE.
   2. Descarte os problemas do cliente no SSMS acessando Opções de consulta -> Resultados -> Grade e marcando a opção "Descartar resultados após a execução". Observe que esta opção impedirá TODAS as saídas, incluindo mensagens, mas ainda pode ser útil para descartar o tempo que o SSMS leva para alocar a memória por cada linha e, em seguida, desenhá-la na grade.
      Como alternativa, você pode executar a consulta por meio do SQLCMD.EXE e direcionar a saída para lugar nenhum por meio de: -o NUL:.
4. Existe um tipo de espera associado a esta consulta? Se sim, qual é esse tipo de espera?

5. Qual é o tamanho real dos dados para as XMLcolunas que estão sendo retornadas ? O tamanho médio dessa coluna em toda a tabela realmente não importa se as linhas "TOP 1000" contiverem uma parte desproporcionalmente grande dos XMLdados totais. Se você quiser saber sobre as 1000 linhas TOP, observe essas linhas. Execute o seguinte:

   SELECT TOP 1000 tab.*,
          SUM(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [TotalXmlKBytes],
          AVG(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [AverageXmlKBytes]
          STDEV(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [StandardDeviationForXmlKBytes]
   FROM   SchemaName.TableName tab;
   

6. O esquema de tabela exato . Forneça a declaração completa CREATE TABLE , incluindo todos os índices.
7. Plano de consulta? Isso é algo que você pode postar? Essa informação provavelmente não vai mudar nada, mas é melhor saber que não vai do que adivinhar que não vai e estar errado ;-)
8. Há fragmentação física/externa no arquivo de dados? Embora isso possa não ser um grande fator aqui, já que você está usando "SATA de nível de consumidor" e não SSD ou mesmo SATA supercaro, o efeito de setores ordenados abaixo do ideal será mais perceptível, especialmente porque o número desses setores que precisa ser lido aumenta.

9. Quais são os resultados exatos da seguinte consulta:

   SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(),
                             OBJECT_ID(N'dbo.SchemaName.TableName'), 1, 0, N'LIMITED');
   

ATUALIZAR

Ocorreu-me que eu deveria tentar reproduzir esse cenário para ver se tenho um comportamento semelhante. Então, criei uma tabela com várias colunas (semelhante à descrição vaga na pergunta) e a preenchai com 1 milhão de linhas, e a coluna XML tem aproximadamente 15k de dados por linha (veja o código abaixo).

O que descobri é que fazer um SELECT TOP 1000 * FROM TABLEconcluído em 8 segundos na primeira vez e 2 a 4 segundos a cada vez (sim, executando DBCC DROPCLEANBUFFERSantes de cada execução da SELECT *consulta). E meu laptop de vários anos não é rápido: SQL Server 2012 SP2 Developer Edition, 64 bits, 6 GB de RAM, dual Core i5 de 2,5 Ghz e uma unidade SATA de 5400 RPM. Também estou executando SSMS 2014, SQL Server Express 2014, Chrome e várias outras coisas.

Com base no tempo de resposta do meu sistema, repetirei que precisamos de mais informações (ou seja, detalhes sobre a tabela e os dados, resultados dos testes sugeridos etc.) que você está vendo.

SET ANSI_NULLS, NOCOUNT ON;
GO

IF (OBJECT_ID(N'dbo.XmlReadTest') IS NOT NULL)
BEGIN
    PRINT N'Dropping table...';
    DROP TABLE dbo.XmlReadTest;
END;

PRINT N'Creating table...';
CREATE TABLE dbo.XmlReadTest 
(
    ID INT NOT NULL IDENTITY(1, 1),
    Col2 BIGINT,
    Col3 UNIQUEIDENTIFIER,
    Col4 DATETIME,
    Col5 XML,
    CONSTRAINT [PK_XmlReadTest] PRIMARY KEY CLUSTERED ([ID])
);
GO

DECLARE @MaxSets INT = 1000,
        @CurrentSet INT = 1;

WHILE (@CurrentSet <= @MaxSets)
BEGIN
    RAISERROR(N'Populating data (1000 sets of 1000 rows); Set # %d ...',
              10, 1, @CurrentSet) WITH NOWAIT;
    INSERT INTO dbo.XmlReadTest (Col2, Col3, Col4, Col5)
        SELECT  TOP 1000
                CONVERT(BIGINT, CRYPT_GEN_RANDOM(8)),
                NEWID(),
                GETDATE(),
                N'<test>'
                  + REPLICATE(CONVERT(NVARCHAR(MAX), CRYPT_GEN_RANDOM(1), 2), 3750)
                  + N'</test>'
        FROM        [master].[sys].all_columns sac1;

    IF ((@CurrentSet % 100) = 0)
    BEGIN
        RAISERROR(N'Executing CHECKPOINT ...', 10, 1) WITH NOWAIT;
        CHECKPOINT;
    END;

    SET @CurrentSet += 1;
END;

--

SELECT COUNT(*) FROM dbo.XmlReadTest; -- Verify that we have 1 million rows

-- O.P. states that the "clustered index fragmentation is close to 0%"
ALTER INDEX [PK_XmlReadTest] ON dbo.XmlReadTest REBUILD WITH (FILLFACTOR = 90);
CHECKPOINT;

--

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET STATISTICS IO, TIME ON;
SELECT TOP 1000 * FROM dbo.XmlReadTest;
SET STATISTICS IO, TIME OFF;

/*
Scan count 1, logical reads 21,       physical reads 1,     read-ahead reads 4436,
              lob logical reads 5676, lob physical reads 1, lob read-ahead reads 3967.

 SQL Server Execution Times:
   CPU time = 171 ms,  elapsed time = 8329 ms.
*/

E, como queremos fatorar o tempo gasto para ler as páginas não LOB, executei a seguinte consulta para selecionar todas, exceto a coluna XML (um dos testes sugeridos acima). Isso retorna em 1,5 segundos de forma bastante consistente.

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET STATISTICS IO, TIME ON;
SELECT TOP 1000 ID, Col2, Col3, Col4 FROM dbo.XmlReadTest;
SET STATISTICS IO, TIME OFF;

/*
Scan count 1, logical reads 21,    physical reads 1,     read-ahead reads 4436,
              lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1666 ms.
*/

Conclusão (no momento)
Com base em minha tentativa de recriar seu cenário, não acho que possamos apontar a unidade SATA ou a E/S não sequencial como a principal causa dos 20 a 25 segundos, especialmente porque ainda não sei com que rapidez a consulta retorna ao não incluir a coluna XML. E não consegui reproduzir o grande número de leituras lógicas (não LOB) que você está mostrando, mas tenho a sensação de que preciso adicionar mais dados a cada linha à luz disso e da declaração de:

~90% das páginas da tabela são LOB_DATA

Minha tabela tem 1 milhão de linhas, cada uma com pouco mais de 15k de dados XML, e sys.dm_db_index_physical_statsmostra que existem 2 milhões de páginas LOB_DATA. Os 10% restantes seriam 222k páginas de dados IN_ROW, mas eu tenho apenas 11.630 delas. Mais uma vez, precisamos de mais informações sobre o esquema da tabela real e os dados reais.

estou correto ao pensar que as páginas LOB_DATA podem causar verificações lentas não apenas por causa de seu tamanho, mas também porque o SQL Server não pode verificar o índice clusterizado com eficiência

Sim, a leitura de dados LOB não armazenados em linha leva a IO aleatório em vez de IO sequencial. A métrica de desempenho do disco a ser usada aqui para entender por que ele é rápido ou lento é IOPS de leitura aleatória.

Os dados LOB são armazenados em uma estrutura de árvore em que a página de dados no índice clusterizado aponta para uma página de dados LOB com uma estrutura raiz LOB que, por sua vez, aponta para os dados LOB reais. Ao percorrer os nós raiz no índice clusterizado, o SQL Server só pode obter os dados em linha por meio de leituras sequenciais. Para obter os dados LOB, o SQL Server precisa ir para outro lugar no disco.

Eu acho que se você mudasse para um disco SSD, não sofreria tanto com isso, já que o IOPS aleatório para um SSD é muito maior do que para um disco giratório.

é considerado razoável ter tal estrutura de tabela/padrão de dados?

Sim, pode ser. Depende do que esta mesa está fazendo por você.

Normalmente, os problemas de desempenho com XML no SQL Server ocorrem quando você deseja usar o T-SQL para consultar o XML e ainda mais quando deseja usar valores do XML em um predicado em uma cláusula where ou junção. Se for esse o caso, você pode dar uma olhada na promoção de propriedade ou índices XML seletivos ou um redesenho de suas estruturas de tabela fragmentando o XML em tabelas.

tentei a compressão

Fiz isso uma vez em um produto há pouco mais de 10 anos e me arrependi desde então. Senti muita falta de não poder trabalhar com os dados usando T-SQL, então não recomendaria isso a ninguém se pudesse ser evitado.