Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Procedure Cache empties spontaneously even on an idle machine

During the course of the SQL Server running, there is a condition that arises that the procedure cache spontaneously empties and the procedure cache hit ratio starts to decline to 0. Queries are still processed and cached, but then it will empty. The number of plans never really reaches anything high during this period. The challenge here is that there is no event that I can pinpoint that will help to cause this to happen. It seems that with time it happens, and the only recourse to get it to stop is to restart the SQL services. This has happened in an AWS Virtual machine and on a local SQL Server.

5 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

1 comment

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
An error occurred while saving the comment
  • Srinivasan Raju commented  ·   ·  Flag as inappropriate

    This seems to happen when USERSTORE_TOKENPERM hits a certain value.

    Windows 2012 R2 Standard with 96 GB Memory
    SQL Server 2014 Standard SP2 CU10 with 64 GB (Max Server Memory)

    Under this configuration, the plan cache empties spontaneously when the USERSTORE_TOKENPERM size grows to around 5 GB.
    No more than a few entries are stored and sometimes it is 0, indicating that its just not removing unused/old plans, it is completely clearing the plan cache.

Feedback and Knowledge Base