Início / dba / Perguntas / 223073
Accepted
JohnLBevan
Asked: 2018-11-22 02:46:18 +0800 CST

Algum mecanismo de cache é usado para colunas computadas não persistentes?

  • 772

O SQL Server armazena em cache o resultado de uma coluna computada não persistente para que possa ser reutilizada sem incorrer em custos de recálculo?

Contexto Adicional Sempre dei o conselho de que, se estiver usando colunas computadas, você deve usar a opção PERSISTED, a menos que esteja esperando uma frequência de inserção/atualização maior do que a frequência de leitura, ou se precisar de melhor desempenho em inserções/atualizações do que as leituras de os dados (ou seja, uma vez que o custo do cálculo deve ser incorrido em um ou outro, então a decisão é realmente onde você quer pagar esse custo).

Há também a consideração do armazenamento adicional para os dados computados, mas normalmente isso é bastante insignificante e barato, portanto, não é muito importante.

No entanto, eu queria verificar se meu conselho é totalmente preciso, já que o SQL pode ser mais inteligente ... consultas. O SQL pode ter um carimbo de data/hora no valor armazenado em cache e outro nos dados do registro subjacente para dizer se esse registro foi alterado desde que o valor calculado foi calculado para determinar se o valor armazenado em cache ainda é válido.

Existe algo assim em vigor / depende dos recursos disponíveis (por exemplo, memória) ou outros fatores (por exemplo, o valor em cache tem um TTL além do tempo de vida do processo)? Eu nunca li nada sugerindo que isso existe, mas ficaria surpreso se não houvesse alguma otimização acontecendo nos bastidores.

sql-server cache

1 respostas

  1. Best Answer

    Os valores de coluna computados não persistentes não são armazenados em cache na memória.

    Aqui está uma breve demonstração que confirma isso. Primeiro vou criar um banco de dados para nossos testes:

    CREATE DATABASE CC_BufferPool_Test;
GO
USE [CC_BufferPool_Test];
GO

    E, em seguida, crie uma tabela sem uma coluna computada como linha de base. Os tamanhos de coluna nesta tabela são escolhidos especificamente para que 4 linhas preencham exatamente uma página de dados de 8 KB (e, portanto, uma página de 1 no cache de buffer do SQL Server):

    CREATE TABLE T
(
    C1 INT NOT NULL IDENTITY PRIMARY KEY,
    Filler CHAR(2011) NOT NULL
);
GO

INSERT INTO T (Filler) VALUES(REPLICATE('A', 2011));
GO 4

    Em seguida, criarei a mesma tabela, mas com a adição de uma coluna computada:

    CREATE TABLE T_WithCC
(
    C1 INT NOT NULL IDENTITY PRIMARY KEY,
    C2 AS 'Row #' + CONVERT(CHAR(8), C1, 1),
    Filler CHAR(2011) NOT NULL
);
GO

INSERT INTO T_WithCC(Filler) VALUES(REPLICATE('A', 2011));
GO 4

    Em seguida, garantirei que essas 8 linhas sejam gravadas no disco e, em seguida, limpe o cache do buffer e selecione todas as 8 linhas para que sejam puxadas para a memória:

    CHECKPOINT
DBCC DROPCLEANBUFFERS;

SELECT * FROM T;
SELECT * FROM dbo.T_WithCC;

    Por fim, usarei essa consulta que totalmente não roubei de Aaron Bertrand*

    páginas na memória por objeto

    A partir disso, você pode ver que há apenas uma página na memória para cada uma dessas duas tabelas. Se a coluna computada fosse armazenada na memória, ela teria empurrado as linhas da segunda tabela para uma segunda página.

    Você também pode ver o cálculo ocorrendo a cada vez no plano de execução real :

    calcular escalar no plano de execução real

    Se você precisar "armazenar em cache" colunas computadas não persistentes para consultas específicas, poderá criar um índice não clusterizado nelas. Dessa forma, eles são armazenados no índice, mas não na tabela base.

    *consulte Determinar o uso de memória do SQL Server por banco de dados e objeto e aqui está o código:

    ;WITH src AS
(
    SELECT
        [Object] = o.name,
        [Type] = o.type_desc,
        [Index] = COALESCE(i.name, ''),
        [Index_Type] = i.type_desc,
        p.[object_id],
        p.index_id,
        au.allocation_unit_id
    FROM sys.partitions AS p
        INNER JOIN sys.allocation_units AS au
            ON p.hobt_id = au.container_id
        INNER JOIN sys.objects AS o
            ON p.[object_id] = o.[object_id]
        INNER JOIN sys.indexes AS i
            ON o.[object_id] = i.[object_id]
            AND p.index_id = i.index_id
    WHERE
        au.[type] IN (1,2,3)
        AND o.is_ms_shipped = 0
)
SELECT
    src.[Object],
    src.[Type],
    src.[Index],
    src.Index_Type,
    buffer_pages = COUNT_BIG(b.page_id),
    buffer_mb = COUNT_BIG(b.page_id) / 128
FROM src
    INNER JOIN sys.dm_os_buffer_descriptors AS b
        ON src.allocation_unit_id = b.allocation_unit_id
WHERE b.database_id = DB_ID()
GROUP BY
    src.[Object],
    src.[Type],
    src.[Index],
    src.Index_Type
ORDER BY buffer_pages DESC;
    • 4

relate perguntas