O SQL Server armazena em cache o resultado de uma função com valor de tabela de várias instruções?

Os resultados de uma função com valor de tabela de várias instruções (msTVF) nunca são armazenados em cache ou reutilizados em instruções (ou conexões), mas há algumas maneiras pelas quais um resultado msTVF pode ser reutilizado na mesma instrução. Nesse sentido, um msTVF não é necessariamente repovoado toda vez que é chamado.

Exemplo msTVF

Este msTVF (deliberadamente ineficiente) retorna um intervalo especificado de inteiros, com um registro de data e hora em cada linha:

IF OBJECT_ID(N'dbo.IntegerRange', 'TF') IS NOT NULL
    DROP FUNCTION dbo.IntegerRange;
GO
CREATE FUNCTION dbo.IntegerRange (@From integer, @To integer)
RETURNS @T table 
(
    n integer PRIMARY KEY, 
    ts datetime DEFAULT CURRENT_TIMESTAMP
)
WITH SCHEMABINDING
AS
BEGIN
    WHILE @From <= @To
    BEGIN
        INSERT @T (n)
        VALUES (@From);

        SET @From = @From + 1;
    END;
    RETURN;
END;

Variável de tabela estática

Se todos os parâmetros para a chamada de função forem constantes (ou constantes de tempo de execução), o plano de execução preencherá o resultado da variável da tabela uma vez. O restante do plano pode acessar a variável de tabela várias vezes. A natureza estática da variável de tabela pode ser reconhecida no plano de execução. Por exemplo:

SELECT
    IR.n,
    IR.ts 
FROM dbo.IntegerRange(1, 5) AS IR
ORDER BY
    IR.n;

Retorna um resultado semelhante a:

O plano de execução é:

O operador Sequence primeiro chama o operador Table Valued Function, que preenche a variável da tabela (observe que esse operador não retorna nenhuma linha). Em seguida, a Sequência chama sua segunda entrada, que retorna o conteúdo da variável de tabela (usando um Clustered Index Scan neste caso).

A indicação de que o plano está usando um resultado de variável de tabela 'estática' é o operador Table Valued Function abaixo de uma sequência - a variável de tabela precisa ser totalmente preenchida uma vez antes que o restante do plano possa prosseguir.

Múltiplos acessos

Para mostrar o resultado da variável da tabela acessada mais de uma vez, usaremos uma segunda tabela com linhas numeradas de 1 a 5:

IF OBJECT_ID(N'dbo.T', 'U') IS NOT NULL
    DROP TABLE dbo.T;

CREATE TABLE dbo.T (i integer NOT NULL);

INSERT dbo.T (i) 
VALUES (1), (2), (3), (4), (5);

E uma nova consulta que une esta tabela à nossa função (isso também pode ser escrito como um APPLY):

SELECT T.i,
       IR.n,
       IR.ts
FROM dbo.T AS T
JOIN dbo.IntegerRange(1, 5) AS IR
    ON IR.n = T.i;

O resultado é:

O plano de execução:

Como antes, a sequência preenche primeiro o resultado da variável de tabela msTVF. Em seguida, os loops aninhados são usados ​​para unir cada linha da tabela Ta uma linha do resultado msTVF. Como a definição da função incluiu um índice útil na variável de tabela, uma busca de índice pode ser usada.

O ponto chave é que quando os parâmetros para o msTVF são constantes (incluindo variáveis ​​e parâmetros) ou tratados como constantes de tempo de execução para a instrução pelo mecanismo de execução, o plano apresentará dois operadores separados para o resultado da variável da tabela msTVF: um para preencher o tabela; outro para acessar os resultados, possivelmente acessando a tabela várias vezes e possivelmente fazendo uso de índices declarados na definição da função.

Parâmetros correlacionados e parâmetros não constantes

Para destacar as diferenças quando parâmetros correlacionados (referências externas) ou parâmetros de funções não constantes são usados, vamos alterar o conteúdo da tabela Tpara que a função tenha muito mais trabalho a fazer:

TRUNCATE TABLE dbo.T;

INSERT dbo.T (i) 
VALUES (50001), (50002), (50003), (50004), (50005);

A consulta modificada a seguir agora usa uma referência externa à tabela Tem um dos parâmetros da função:

SELECT T.i,
       IR.n,
       IR.ts
FROM dbo.T AS T
CROSS APPLY dbo.IntegerRange(1, T.i) AS IR
WHERE IR.n = T.i;

Essa consulta leva cerca de 8 segundos para retornar resultados como:

Observe a diferença de tempo entre as linhas na coluna ts. A WHEREcláusula limita o resultado final para uma saída de tamanho razoável, mas a função ineficiente ainda demora um pouco para preencher a variável de tabela com 50.000 linhas ímpares (dependendo do valor correlacionado de ifrom table T).

O plano de execução é:

Observe a falta de um operador Sequence. Agora, há um único operador Table Valued Function que preenche a variável de tabela e retorna suas linhas em cada iteração da união de loops aninhados.

Para ser claro: com apenas 5 linhas na tabela T, o operador Table Valued Function é executado 5 vezes. Ele gera 50.001 linhas na primeira iteração, 50.002 na segunda... e assim por diante. A variável de tabela é 'jogada fora' (truncada) entre as iterações, então cada uma das cinco chamadas é uma população completa. É por isso que é tão lento e cada linha leva aproximadamente o mesmo tempo para aparecer no resultado.

Notas laterais:

Naturalmente, o cenário acima é deliberadamente planejado para mostrar como o desempenho ruim pode ser quando o msTVF preenche muitas linhas em cada iteração.

Uma implementação sensata do código acima definiria ambos os parâmetros msTVF como , e removeria a cláusula iredundante . WHEREA variável de tabela ainda seria truncada e preenchida novamente em cada iteração, mas apenas com uma linha de cada vez.

Também poderíamos buscar os ivalores mínimo e máximo Te armazená-los em variáveis ​​em uma etapa anterior. Chamar a função com variáveis ​​em vez de parâmetros correlacionados permitiria que o padrão de variável de tabela 'estática' fosse usado conforme observado anteriormente.

Cache para parâmetros correlacionados inalterados

Voltando à questão original mais uma vez, onde o padrão estático Sequence não pode ser usado, o SQL Server pode evitar o truncamento e o repovoamento da variável de tabela msTVF se nenhum dos parâmetros correlacionados tiver sido alterado desde a iteração anterior de uma junção de loop aninhada.

Para demonstrar isso, substituiremos o conteúdo de Tpor cinco valores idênticos i :

TRUNCATE TABLE dbo.T;

INSERT dbo.T (i) 
VALUES (50005), (50005), (50005), (50005), (50005);

A consulta com um parâmetro correlacionado novamente:

SELECT T.i,
       IR.n,
       IR.ts
FROM dbo.T AS T
CROSS APPLY dbo.IntegerRange(1, T.i) AS IR
WHERE IR.n = T.i;

Desta vez, os resultados aparecem em cerca de 1,5 segundos :

Observe os timestamps idênticos em cada linha. O resultado armazenado em cache na variável de tabela é reutilizado para iterações subsequentes em que o valor correlacionado ipermanece inalterado. Reutilizar o resultado é muito mais rápido do que inserir 50.005 linhas de cada vez.

O plano de execução é muito semelhante ao anterior:

A principal diferença está nas propriedades Actual Rebinds e Actual Rewinds do operador Table Valued Function:

Quando os parâmetros correlacionados não mudam, o SQL Server pode reproduzir (rebobinar) os resultados atuais na variável de tabela. Quando a correlação muda, o SQL Server deve truncar e preencher novamente a variável de tabela (revincular). A única religação acontece na primeira iteração; as quatro iterações subsequentes são todas retrocessos, pois o valor de T.ipermanece inalterado.