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.

Uma vez por semana, nas últimas 5 semanas, na mesma hora do dia (de manhã cedo, pode ser baseado na atividade do usuário quando as pessoas estão começando a usá-lo), o SQL Server 2016 (AWS RDS, espelhado) começa a expirar muito consultas.

UPDATE STATISTICS em todas as tabelas sempre corrige isso imediatamente.

Após a primeira vez, fiz com que atualizasse todas as estatísticas em todas as mesas todas as noites (em vez de semanalmente), mas ainda aconteceu (cerca de 8 horas após a atualização das estatísticas, mas não todos os dias).

Na última vez, habilitei o Repositório de Consultas para ver se conseguia encontrar qual era o plano de consulta/consulta específico. Acho que consegui resumir em um:

Depois de encontrar essa consulta, adicionei um índice recomendado que estava faltando nessa consulta não usada com frequência (mas que toca em muitas tabelas usadas com frequência).

O plano de consulta ruim estava fazendo uma varredura de índice (em uma tabela com apenas 10 mil linhas). Outros planos de consulta que retornavam em milissegundos costumavam fazer a mesma varredura. O plano de consulta mais recente, após criar o novo índice, faz apenas buscas. Mas mesmo sem esse índice, 99% das vezes, ele estava retornando em alguns milissegundos, mas depois, semanalmente, demorava > 40 segundos.

• Ruim que expira: http://brentozar.com/pastetheplan/?id=rymaWt56e
• Planos anteriores que não expiram: http://brentozar.com/pastetheplan/?id=HyN7ftcpe
• Novo plano com novo índice: http://brentozar.com/pastetheplan/?id=ryLuGKcag

Isso começou a acontecer depois de mudar para o SQL Server 2016 a partir de 2012.

DBCC CHECKDB não retorna erros.

1. O novo índice resolverá o problema, fazendo com que ele nunca mais escolha o plano ruim?
2. Devo "forçar" o plano que funciona bem agora?
3. Como posso garantir que isso não aconteça com outra consulta/plano?
4. Isso é um sintoma de um problema maior?

Índices que acabei de adicionar:

CREATE NONCLUSTERED INDEX idx_AppointmetnAttendee_AttendeeType
ON [dbo].[AppointmentAttendee] ([UserID],[AttendeeType])

CREATE NONCLUSTERED INDEX [idx_appointment_start] ON [dbo].[Appointment]
(
    [ProjectID] ASC,
    [Start] ASC
)
INCLUDE (   [ID],
    [AllDay],
    [End],
    [Location],
    [Notes],
    [Title],
    [CreatedByID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Texto completo da consulta:

https://pastebin.com/Z5szPBfu (gerado por LINQ, posso/devo ser capaz de otimizar as colunas selecionadas, mas deve ser irrelevante para este problema)