Eu tenho um procedimento que executa uma consulta dinamicamente em algumas centenas de bancos de dados que têm o mesmo esquema exato e agrega os resultados a uma tabela temporária. A consulta envolve apenas 3 tabelas (cada tabela está entre 10 e 100 de milhões de linhas, mas estou recuperando apenas cerca de 50.000 linhas de dados no total).
Ao olhar para as estatísticas de IO agregadas (via StatisticsParser.com - grite Richie Rump) para a consulta, está mostrando que uma tabela de trabalho foi criada e há cerca de 550.000 leituras lógicas de LOB apenas na tabela de trabalho. O número total de leituras lógicas regulares somado para todas as tabelas na consulta está logo abaixo de cerca de 400.000 leituras lógicas.
Nenhuma das tabelas reais na consulta usa qualquer tipo de dados LOB, então o que diabos isso significa e é uma fonte potencial de um gargalo na minha consulta?
(Além disso, as 50.000 linhas de dados que estou recuperando equivalem apenas a cerca de 3 MB de dados, mas minha consulta está demorando cerca de 10 segundos para ser executada na primeira execução (por exemplo, quando as páginas de dados das tabelas ainda estão sendo carregadas na memória ) e, em seguida, é menos da metade desse tempo em execuções subsequentes, tentando ver se consigo me aproximar consistentemente do benchmark <= 4s e se essas LOB Logical Reads estão relacionadas a ele.)
Atualização : Aqui está uma consulta de exemplo semelhante (que, examinando novamente, posso ter encontrado de onde vêm as LOB Logical Reads).
Assinatura do procedimento: sp_StoredProc_ToGetData (@IdsTable TVP (Id INT), @StartDate DateTime, @EndDate DateTime)
Consultas dentro de sp_StoredProc_ToGetData:
SELECT Id
INTO #IdsTableTemp
FROM @IdsTable;
-- This query is ran using dynamic SQL but for the example simplicity this is just the root query itself
SELECT 'SomeConstant' AS Field1, T1.Field2, T1.Field3, T3.Field4, T3.Field5
FROM Table1 AS T1
INNER JOIN Table2 AS T2 -- Linking table between T1 and T3
ON T1.PrimaryKey = T2.PrimaryKey
INNER JOIN Table3 AS T3
ON T2.NonClusteredIndexField = T3.PrimaryKey
WHERE T1.Date >= @StartDate
AND T2.Date < @EndDate
Atualização 2: Plano de execução da consulta principal que é executado dinamicamente
Atualização 3: Propriedades do Operador Escalar do Computador
Com base na descrição do seu problema:
Parece que você está se deparando com isso: Análise do Plano de Execução: A Mesa de Trabalho Misteriosa
Procure um Compute Scalar que está produzindo um tipo de dados LOB como saída, que então flui para uma junção de loops aninhados com pré-busca.
Resolver isso dependerá muito de sua consulta de origem, mas algo precisará ser feito para mover os dados LOB além da junção do loop ou potencialmente obter um tipo de junção diferente.
Ele sempre acaba sendo o último lugar que eu procuro, mas acontece que a tabela de trabalho e as leituras lógicas de LOB altas estavam sendo produzidas pela variável SQL dinâmica na qual eu estava selecionando recursivamente.
Como tem que ser do tipo NVARCHAR para o parâmetro sp_ExecuteSQL e eu estava usando MAX como tamanho, isso fez com que a Worktable fosse criada no TempDB e as LOB Logical Reads altas.
Ainda não tenho certeza por que seria ~ 550.000 LOB Logical Reads quando está iterando apenas cerca de 250 recursões, por exemplo:
De qualquer forma, alterar minha variável SQL dinâmica de NVARCHAR(MAX) para NVARCHAR(4000) removeu todas as LOB Logical Reads das estatísticas.