Planejar o tamanho do cache e a memória reservada

A razão pela qual o size_in_bytescampo do sys.dm_exec_cached_plansDMV, pelo menos em termos de "Planos Compilados", é maior que o CachedPlanSizeatributo do QueryPlannó no plano XML é porque um Plano Compilado não é a mesma coisa que um Plano de Consulta. Um Plano Compilado é composto por vários Objetos de Memória, cujo tamanho combinado equivale ao size_in_bytescampo. Portanto, a descrição de " Número de bytes consumidos pelo objeto de cache " que você encontrou na documentação é precisa; é apenas que é fácil interpretar mal o que significa "objeto de cache" dado o nome do DMV e que o termo "plano" tem vários significados.

Um Plano Compilado é um contêiner que contém várias informações relacionadas ao lote de consulta (ou seja, não apenas uma única instrução), uma (ou mais) dessas partes sendo o(s) plano(s) de consulta. Os planos compilados têm um objeto de memória de nível superior de MEMOBJ_COMPILE_ADHOC, que é a linha sys.dm_os_memory_objectsvinculada por meio do memory_object_addresscampo em ambos os DMVs. Este objeto de memória contém a tabela de símbolos, coleção de parâmetros, links para objetos relacionados, cache do acessador, cache de metadados TDS e possivelmente alguns outros itens. Planos compilados são compartilhados entre Sessões/Usuários que estão executando o mesmo lote com as mesmas configurações de Sessão. No entanto, alguns objetos relacionados não são compartilhados entre sessões/usuários.

Planos compilados também possuem um ou mais objetos dependentes que podem ser encontrados passando o plan_handle(in sys.dm_exec_cached_plans) para o sys.dm_exec_cached_plan_dependent_objectsDMF. Existem dois tipos de objetos dependentes: Plano Executável (Objeto de Memória = MEMOBJ_EXECUTE ) e Cursor (Objeto de Memória = MEMOBJ_CURSOREXEC ). Haverá 0 ou mais objetos Cursor, um para cada cursor. Haverá também um ou mais objetos de Plano Executável, um para cada Usuário executando o mesmo lote , portanto Planos Executáveis ​​não sãocompartilhada entre os usuários. Planos executáveis ​​contêm parâmetros de tempo de execução e informações de variáveis ​​locais, estado de tempo de execução, como a instrução atualmente em execução, IDs de objeto para objetos criados em tempo de execução (suponho que isso se refira a variáveis ​​de tabela, tabelas temporárias, procedimentos armazenados temporários etc.) , e possivelmente outros itens.

Cada instrução em um lote de várias instruções está contida em uma instrução compilada (objeto de memória = MEMOBJ_STATEMENT ). O tamanho de cada instrução compilada (ou seja, pages_in_bytes) dividido por 1024 deve corresponder aos CachedPlanSize="xx"valores dos <QueryPlan>nós no plano XML. As instruções compiladas geralmente têm um (possivelmente mais?) Planos de consulta de tempo de execução associados (objeto de memória = MEMOBJ_XSTMT ). Por fim, para cada plano de consulta em tempo de execução que seja uma consulta, deve haver um contexto de execução de consulta associado (objeto de memória = MEMOBJ_QUERYEXECCNTXTFORSE ).

Com relação às instruções compiladas, os lotes de instrução única não têm objetos separados de instrução compilada (ou seja, MEMOBJ_STATEMENT ) ou plano de consulta de tempo de execução (ou seja, MEMOBJ_XSTMT ). O valor para cada um desses objetos será armazenado no objeto Plano Compilado principal (ou seja , MEMOBJ_COMPILE_ADHOC ) e, nesse caso, o pages_in_bytesvalor desse objeto principal dividido por 1024 deve corresponder ao CachedPlanSizetamanho no <QueryPlan>nó do plano XML. Esses valores não serão iguais, no entanto, em lotes de várias instruções.

O size_in_bytesvalor pode ser derivado somando as entradas no sys.dm_os_memory_objectsDMV (os itens anotados acima em negrito), todos relacionados dm_os_memory_objects.page_allocator_addresspara esse Plano Compilado. O truque para obter o valor correto é primeiro obter o memory_object_addressfrom sys.dm_exec_cached_planspara um plano compilado específico e, em seguida, usá-lo para obter a linha MEMOBJ_COMPILE_ADHOCsys.dm_os_memory_objects correspondente com base em seu memory_object_addresscampo. Em seguida, pegue o page_allocator_addressvalor sys.dm_os_memory_objectsdessa linha e use-o para pegar todas as linhas sys.dm_os_memory_objectsque tenham o mesmo page_allocator_addressvalor. (Observe que esta técnica não funciona para os outros tipos de objetos em cache: Parse Tree , Extended Proc , CLR Compiled Proc e CLR Compiled Func.)

Usando o memory_object_addressvalor obtido de sys.dm_exec_cached_plans, você pode ver todos os componentes do Plano Compilado por meio da seguinte consulta:

DECLARE @CompiledPlanAddress VARBINARY(8) = 0x00000001DC4A4060;

SELECT obj.memory_object_address, obj.pages_in_bytes, obj.type
FROM   sys.dm_os_memory_objects obj
WHERE  obj.page_allocator_address = (
                               SELECT planobj.page_allocator_address
                               FROM   sys.dm_os_memory_objects planobj
                               WHERE  planobj.memory_object_address = @CompiledPlanAddress
                              )
ORDER BY obj.[type], obj.pages_in_bytes;

A consulta abaixo lista todos os Planos compilados sys.dm_exec_cached_plansjunto com o Plano de consulta e as instruções para cada lote. A consulta diretamente acima é incorporada à consulta abaixo via XML como o MemoryObjectscampo:

SELECT cplan.bucketid,
       cplan.pool_id,
       cplan.refcounts,
       cplan.usecounts,
       cplan.size_in_bytes,
       cplan.memory_object_address,
       cplan.cacheobjtype,
       cplan.objtype,
       cplan.plan_handle,
       '---' AS [---],
       qrypln.[query_plan],
       sqltxt.[text],
       '---' AS [---],
       planobj.pages_in_bytes,
       planobj.pages_in_bytes / 1024 AS [BaseSingleStatementPlanKB],
       '===' AS [===],
       cplan.size_in_bytes AS [TotalPlanBytes],
       bytes.AllocatedBytes,
       (SELECT CONVERT(VARCHAR(30), obj.memory_object_address, 1)
               AS [memory_object_address], obj.pages_in_bytes, obj.[type]
               --,obj.page_size_in_bytes
        FROM   sys.dm_os_memory_objects obj
        WHERE  obj.page_allocator_address = planobj.page_allocator_address
        FOR XML RAW(N'object'), ROOT(N'memory_objects'), TYPE) AS [MemoryObjects]
FROM   sys.dm_exec_cached_plans cplan
OUTER APPLY sys.dm_exec_sql_text(cplan.[plan_handle]) sqltxt
OUTER APPLY sys.dm_exec_query_plan(cplan.[plan_handle]) qrypln
INNER JOIN sys.dm_os_memory_objects planobj
        ON planobj.memory_object_address = cplan.memory_object_address
OUTER APPLY (SELECT SUM(domo.[pages_in_bytes]) AS [AllocatedBytes]
             FROM   sys.dm_os_memory_objects domo
             WHERE  domo.page_allocator_address = planobj.page_allocator_address) bytes
WHERE  cplan.parent_plan_handle IS NULL
AND    cplan.cacheobjtype IN (N'Compiled Plan', N'Compiled Plan Stub')
--AND cplan.plan_handle = 0x06000D0031CD572910529CE001000000xxxxxxxx
ORDER BY cplan.objtype, cplan.plan_handle;

Observe que:

• o TotalPlanBytescampo é apenas uma reafirmação do sys.dm_exec_cached_plans.size_in_bytescampo,
• o AllocatedBytescampo é a SOMA dos objetos de memória relacionados que normalmente correspondem TotalPlanBytes(ou seja size_in_bytes)
• o AllocatedBytescampo ocasionalmente será maior que TotalPlanBytes(ou seja size_in_bytes, ) devido ao aumento do consumo de memória durante a execução. Isso parece acontecer principalmente devido à recompilação (que deve ser evidente com o usecountscampo mostrando 1)
• o BaseSingleStatementPlanKBcampo deve corresponder ao CachedPlanSizeatributo do QueryPlannó no XML, mas somente ao usar um único lote de consulta.
• para lotes com várias consultas, deve haver linhas marcadas como MEMOBJ_STATEMENTem sys.dm_os_memory_objects, uma para cada consulta. O pages_in_bytescampo dessas linhas deve corresponder aos <QueryPlan>nós individuais do plano XML.

Recursos:

• Quais são os diferentes objetos em cache no cache do plano?
• 1.0 Estrutura do Cache do Plano e Tipos de Objetos em Cache
• 2.0 Sql_Handle e Plan_Handle explicados
• Documentação para sys.dm_exec_cached_plans
• Documentação para sys.dm_os_memory_objects
• Documentação para sys.dm_exec_sql_text
• Documentação para sys.dm_exec_query_plan

O Pro SQL Server Internals examinou esse problema.

dbcc freeproccache
GO
DECLARE
@SQL nvarchar (MAX)
,@I INT = 0
while @I < 1000
BEGIN
    SELECT @SQL = N'declare @C int;select @C=ID from dbo.Employees where ID='
           + CONVERT (nvarchar (10), @I);

    EXEC (@SQL);
    SELECT @I += 1;
END
GO


SELECT
p.usecounts, p.cacheobjtype, p.objtype, p.size_in_bytes, t.[text]
FROM
sys.dm_exec_cached_plans p CROSS apply
sys.dm_exec_sql_text (p.plan_handle) t
WHERE
p.cacheobjtype LIKE 'Compiled Plan%' AND
t.[text] LIKE '%Employees%'
ORDER BY
p.objtype DESC;

existem 1.000 planos armazenados em cache, cada um dos quais usa 32 KB de memória ou 32 MB no total. Como você pode imaginar , consultas ad-hoc em sistemas ocupados podem levar ao uso excessivo de memória cache do plano.

exec sys.sp_configure N'optimize for ad hoc workloads', N'1';
reconfigure with override;

com a configuração Otimizar para cargas de trabalho ad hoc habilitada, você veria o conteúdo do cache do plano. Como você pode ver, agora ele usa apenas 272 KB de memória, em vez dos 32 MB que usava antes