Os planos Sql CACHESTORE_SQLCP ocupam > 38 GB após alguns dias.

Já estamos executando com a opção "otimizar para cargas de trabalho ad hoc" ativada. (Entity Framework e relatórios personalizados criam muitos ad hocs!)

SQL Server 2016 SE 3.00.2164.0.v1 no AWS RDS com espelhamento multi-AZ

Quando eu corro:

DBCC FREESYSTEMCACHE('SQL Plans');

ou

DBCC FREEPROCCACHE

ou

DBCC FREESYSTEMCACHE ('SQL Plans') WITH MARK_IN_USE_FOR_REMOVAL

ou

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

Parece que não esclarece:

SELECT TOP 1 type, name, pages_kb FROM sys.dm_os_memory_clerks ORDER BY pages_kb  desc

type                name        pages_kb
CACHESTORE_SQLCP    SQL Plans   38321048

Eu estava executando com o Query Store habilitado, mas desabilitei para ver se isso estava interferindo em alguma coisa, não pareceu ajudar, mas deixei de lado.

O que é realmente estranho também é

SELECT COUNT(*) FROM sys.dm_exec_cached_plans

é 1-3 ou mais (parece mostrar apenas as consultas em execução no momento), mesmo que toda essa memória esteja reservada, mesmo antes de eu tentar limpar qualquer coisa. o que estou perdendo?

CACHESTORE_SQLCP está ocupando mais de 60% de toda a memória disponível, o que é uma preocupação porque há esperas de memória acontecendo ocasionalmente. Além disso, tivemos que matar uma rotina DBCC CHECKDB durante o fim de semana que estava durando 4 horas porque memória insuficiente estava acumulando esperas (ela foi concluída instantaneamente sem erros com PHYSICAL_ONLY ativado).

Existe alguma maneira de recuperar essa memória (além de reinicializações noturnas!?)?

Atualização de comentários/respostas

Quando eu corro

SELECT * FROM sys.fn_my_permissions(NULL,NULL)

eu recebo

entity_name subentity_name  permission_name
server      CONNECT SQL
server      CREATE ANY DATABASE
server      ALTER ANY LOGIN
server      ALTER ANY LINKED SERVER
server      ALTER ANY CONNECTION
server      ALTER TRACE
server      VIEW ANY DATABASE
server      VIEW ANY DEFINITION
server      VIEW SERVER STATE
server      ALTER SERVER STATE
server      CREATE SERVER ROLE
server      ALTER ANY SERVER ROLE

Que inclui a ALTER SERVER STATEpermissão necessária.

A saída de DBCC FREEPROCCACHEé

Execução DBCC concluída. Se o DBCC imprimir mensagens de erro, entre em contato com o administrador do sistema.

Qual é a mensagem padrão. Outras funções DBCC não suportadas pelo RDS fornecem mensagens de erro sobre permissões.

(Um dia depois e depois de executar isso novamente, o SQL Plans ainda é 38.321.280 kb)

Atualização de comentários/respostas

SELECT pool_id, name, cache_memory_kb, compile_memory_kb FROM sys.dm_resource_governor_resource_pools

saídas:

pool_id name    cache_memory_kb  compile_memory_kb
1   internal    38368408         1168080

DBCC FREEPROCCACHE ('internal')não faz nada diferente

Atualizar

O log de erros do SQL registra o seguinte a cada vez:

2017-10-19 14:26:47.22 spid85 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

2017-10-19 14:26:47.22 spid85 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

2017-10-19 14:26:47.22 spid85 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

Atualizar

Há uma atualização de versão do mecanismo no RDS de 13.00.2164.0.v1 para 13.00.4422.0.v1 disponível. Embora esteja configurado para atualização automática de versão secundária, não parece nos manter atualizados com as últimas. Vai reiniciar e instalar isso neste fim de semana para ver se isso ajuda.