CODEWITHSUNDEEP
sql-serversql-server-2008
ilyabreev
ilyabreev

Reputation: 634

SQL Server 2008 plan cache is almost always empty

In order to investigate query plan usage I'm trying to understand what kind of query plan is stored in the memory.

Using this query:

SELECT objtype AS 'Cached Object Type', 
COUNT(*) AS 'Numberof Plans', 
SUM(CAST(size_in_bytes AS BIGINT))/1048576 AS 'Plan Cache SIze (MB)', 
AVG(usecounts) AS 'Avg Use Counts' 
FROM sys.dm_exec_cached_plans 
GROUP BY objtype  
ORDER BY objtype

I got almost empty plan cache structure. almost empty plan cache.

There is 128Gb of RAM on the server and ~20% is free. SQL Server instance is not constrained by memory.

Yes basically I have Adhoc queries (not parameterized, not stored procedures). But why SQL Server empties the query plan cache so frequent? What kind of issue do I have?

Upvotes: 3

Views: 1005

Answers (3)

George K
George K

Reputation: 103

I had the same issue just about a week ago and also posted several questions. Even though I have not actually found the answer to the problem I 've got some insight on the process. And silly as it sounds SQL Server service restart helped but raised another problem - the recovery process continued for 4 hours. Seems like a pretty large transaction was in place...

empty-plan-cache-problem

Almost empty plan cache

Almost empty plan cache

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453067

If the server isn't under memory pressure then some other possibilities from the plan caching white paper are below.

Are any of these actions scheduled frequently? Do you have auto close enabled?

The following operations flush the entire plan cache, and therefore, cause fresh compilations of batches that are submitted the first time afterwards:

  1. Detaching a database
  2. Upgrading a database to SQL Server 2005
  3. Upgrading a database to SQL Server 2008
  4. Restoring a database
  5. DBCC FREEPROCCACHE command
  6. RECONFIGURE command
  7. ALTER DATABASE ,,, MODIFY FILEGROUP command
  8. Modifying a collation using ALTER DATABASE … COLLATE command

The following operations flush the plan cache entries that refer to a particular database, and cause fresh compilations afterwards.

  1. DBCC FLUSHPROCINDB command
  2. ALTER DATABASE … MODIFY NAME = command
  3. ALTER DATABASE … SET ONLINE command
  4. ALTER DATABASE … SET OFFLINE command
  5. ALTER DATABASE … SET EMERGENCY command
  6. DROP DATABASE command
  7. When a database auto-closes
  8. When a view is created with CHECK OPTION, the plan cache entries of the database in which the view is created are flushed.
  9. When DBCC CHECKDB is run, a replica of the specified database is created. As part of DBCC CHECKDB's execution, some queries against the replica are executed, and their plans cached. At the end of DBCC CHECKDB's execution, the replica is deleted and so are the query plans of the queries posed on the replica.

The following sp_configure/reconfigure operations also clear the procedure cache:

  • access check cache bucket count
  • access check cache quota
  • clr enabled
  • cost threshold for parallelism
  • cross db ownership chaining
  • index create memory
  • max degree of parallelism
  • max server memory
  • max text repl size
  • max worker threads
  • min memory per query
  • min server memory
  • query governor cost limit
  • query wait
  • remote query timeout
  • user options

Upvotes: 2

ilyabreev
ilyabreev

Reputation: 634

Finally, only instance restart solved my problem. Now plan cache looks more healthy.

Upvotes: 2

Related Questions