SQL Server 2005 pergunta sobre cache de procedimento

Question

Servidor: SQL Server 2005 SP2 64 bits, 32 GB de memória. 2 instâncias do SQL Server em execução. A instância principal que estou usando tem 20 shows visíveis.

Temos uma situação em que aparece de vez em quando nosso cache de procedimento inteiro é limpo, o que, por sua vez, está forçando recompilações de procedimento armazenado (sp). Uma vez que o sp está no cache, tudo corre rápido por um tempo. Depois de algumas horas, ele é apagado do cache e tem que ser recompilado fazendo com que as coisas corram lentamente por pouco tempo.

Estou assistindo o cache usando:

SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.TEXT AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
GO

O DBCC FREEPROCCACHE nunca é chamado.

Se eu executar o DBCC MEMORYSTATUS, posso ver as TotalPages do cache de procedimentos como sendo cerca de 500k páginas. Isto vem a ser 3.9 Gigs alocados para o cache. Referenciamento: Planejar o cache no SQL Server 2008 (A seção sobre armazenamento em cache inclui o 2005 SP 2). Isso indica que o limite de pressão deve ser de 4,6 GB. (75% da memória de alvo visível de 0-4 GB + 10% de memória de alvo visível de 4 GB-64 GB + 5% de memória de alvo visível > 64 GB ... 3 GB + 1,6 GB = 4,6 GB)

Isto parece indicar que não devemos estar sob pressão de cache por outros 700 megas. Se as estatísticas estivessem mudando, o procedimento armazenado ainda deveria estar no cache e recompilado quando for executado novamente e verificar as estatísticas. Se este fosse o caso, eu esperaria que o cache permanecesse em tamanho quase constante.

Alguma idéia do que poderia estar causando o cache de procedimentos para esvaziar ou o que mais eu deveria ficar de olho para tentar encontrar a causa?

    

Answer 1

O max_server_memory está configurado na segunda instância? Ou, mais importante, a soma da configuração de memória máxima de duas instâncias é menor que a memória total no servidor? Se não, uma instância poderia roubar a memória da outra.