Plan Cache Erased Recently

Whenever you pass in a query, SQL Server has to turn your beautifully written T-SQL into an execution plan – a way of picking which indexes to use and in which order. It caches those plans in memory to reduce CPU requirements later if that T-SQL shows up again.

According to our diagnostics, your server’s execution plan cache was recently erased, so SQL Server is having to build new execution plans for incoming queries. This causes slower query startups, higher CPU usage, and less-relevant data from monitoring tools (since SQL Server has amnesia.)

Possible causes include:

The operating system recently restarted
The SQL Server service restarted or failed over to another node
Someone ran DBCC FREEPROCCACHE
Someone ran an ALTER DATABASE that changed a database’s settings, removing its plans from the cache
The server came under extreme memory pressure from queries with high memory grants

If you got a warning about USERSTORE_TOKENPERM, you can run one of these queries to verify, and check out this awesome round up post by sql.sasquatch to learn more about how to fix it.

/* 2012+ */
SELECT pages_kb / 1024.0 / 1024.0 AS pages_gb
FROM   sys.dm_os_memory_clerks
WHERE  type = 'USERSTORE_TOKENPERM'
AND    name = 'TokenAndPermUserStore';

/* 2008 and 2008R2 */
SELECT single_pages_kb / 1024.0 / 1024.0 AS pages_gb
FROM   sys.dm_os_memory_clerks
WHERE  type = 'USERSTORE_TOKENPERM'
AND    name = 'TokenAndPermUserStore';

/* 2012+ */

SELECT pages_kb / 1024.0 / 1024.0 AS pages_gb

FROM sys.dm_os_memory_clerks

WHERE type = 'USERSTORE_TOKENPERM'

AND name = 'TokenAndPermUserStore';

/* 2008 and 2008R2 */

SELECT single_pages_kb / 1024.0 / 1024.0 AS pages_gb

FROM sys.dm_os_memory_clerks

WHERE type = 'USERSTORE_TOKENPERM'

AND name = 'TokenAndPermUserStore';

For personalized help with getting the right answers for your server, we’ve got SQL Critical Care®.