What Query Plans are in SQL Server's Memory?

By Bill Graziano on 25 July 2007 | Tags: Memory Tuning

SQL Server memory is primarily used to store data (buffer) and query plans (procedure cache). In this article I'll show how much memory is allocated to the procedure cache (RAM). I'll explain how to determine what plans are in the cache and how often they're used.

SQL Server stores the procedure cache in 8KB data pages. You can use the dynamic management view sys.dm_os_memory_cache_counters to provide a summary of how the cache is allocated using this query:

SELECT  TOP 6
	LEFT([name], 20) as [name],
	LEFT([type], 20) as [type],
	[single_pages_kb] + [multi_pages_kb] AS cache_kb,
	[entries_count]
FROM sys.dm_os_memory_cache_counters 
order by single_pages_kb + multi_pages_kb DESC

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

name                 type                             cache_kb        entries_count
-------------------- -------------------- -------------------- --------------------
SQL Plans            CACHESTORE_SQLCP                   669880                 9613
Object Plans         CACHESTORE_OBJCP                    14120                   97
Bound Trees          CACHESTORE_PHDR                      3288                   63
SchemaMgr Store      USERSTORE_SCHEMAMGR                  2544                    0
mssqlsystemresource  USERSTORE_DBMETADATA                 2080                  241
sqlteam              USERSTORE_DBMETADATA                  728                  302
. . . . .

On the server that hosts SQLTeam.com this query actually returns 75 rows. The server has 2GB of RAM with 1GB allocated to SQL Server 2005 Express Edition. This article will focus on the first three rows in this query. These are:

CACHESTORE_OBJCP. These are compiled plans for stored procedures, functions and triggers.
CACHESTORE_SQLCP. These are cached SQL statements or batches that aren't in stored procedures, functions and triggers. This includes any dynamic SQL or raw SELECT statements sent to the server.
CACHESTORE_PHDR. These are algebrizer trees for views, constraints and defaults. An algebrizer tree is the parsed SQL text that resolves the table and column names.

Notice that there are very few compiled plans for stored procedures (CACHESTORE_OBJCP). This should work out to about one plan per active stored procedure on the server. Also notice that there are lots and lots of plans for dynamic SQL on the server (CACHESTORE_SQLCP). The forum software on the site doesn't use stored procedures. The SQL statements it generates go into the SQL plan cache.

You can monitor the number of data pages in the plan cache using Performance Monitor (PerfMon) using SQLServer:Plan Cache object with the Cache Pages counter. There are instances for SQL Plans (CACHESTORE_SQLCP), Object Plans (CACHESTORE_OBJCP) and Bound Trees (CACHESTORE_PHDR).

We can see individual cache entries by using the sys.dm_exec_cached_plans dynamic management view.

SELECT usecounts, cacheobjtype, objtype, plan_handle
FROM sys.dm_exec_cached_plans

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

  usecounts cacheobjtype     objtype              plan_handle
----------- ---------------- -------------------- --------------------------------------------------
      23133 Compiled Plan    Proc                 0x05000600E969C702B861712D000000000000000000000000
       6193 Compiled Plan    Adhoc                0x060005009D7DB935B841F141000000000000000000000000
       6192 Compiled Plan    Trigger              0x05000500E9DB0F6BB8E13359000000000000000000000000
       6192 Compiled Plan    Proc                 0x0500050047E57235B8C1560F000000000000000000000000
       6097 Compiled Plan    Adhoc                0x06000500992FE330B8C16E59000000000000000000000000
       6082 Compiled Plan    Adhoc                0x06000500EFE7B508B861CD0D000000000000000000000000
       5818 Compiled Plan    Trigger              0x050005003FB5C870B8614559000000000000000000000000
       4864 Compiled Plan    Prepared             0x06000500F264B12FB8412F2B000000000000000000000000
       4850 Compiled Plan    Prepared             0x0600050017A8CC01B8812B48000000000000000000000000
       3385 Compiled Plan    Proc                 0x05000500099CB87DB801A449000000000000000000000000
       884 Parse Tree        View                 0x0500FF7FC1010000C800130D000000000000000000000000
. . . . .

This query lists the most used query plans. In includes plans for stored procedures, adhoc or dynamic SQL, triggers, prepared SQL and views. If we want to see the SQL associates with these plans like need to use the sys.dm_exec_sql_text dynamic management function like this:

select  TOP 100
	objtype, 
	p.size_in_bytes,
	LEFT([sql].[text], 100) as [text]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts DESC

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

objtype size_in_bytes text
------- ------------- ------------------------------------------------------------------------------------
Proc           172032 CREATE PROC [dbo].[subtext_GetSingleEntry] ( @ID int = NULL, @EntryName nvarchar(...
Adhoc           24576 SELECT F_PRIVATEFORUMS, F_SUBJECT, F_PASSWORD_NEW  FROM FORUM_FORUM  WHERE FORUM...
View            73728 CREATE VIEW [dbo].[sqlteam_Article_View] AS SELECT bbo.sqlteam_Article.ArticleID,...
Proc          4251648 CREATE PROCEDURE [BMP_proc_RetrieveBannerFromZoneSite] (@ZoneID [int],@SiteID ...
Proc           425984 CREATE PROC [dbo].[sqlteam_Article_Read_ByPageName] @PageName VARCHAR(200) AS ...

I manually pulled a few rows from the result set to highlight the type of results this query generates. Notice that the compile query plans are BIG. One listed in over 4MB. There many of them that are roughly 400KB. If you do a little math on the first result set in this article you'll see the average SQL plan is just under 70KB and the average stored procedure plan is 145KB. Stored procedures tend to encapsulate more complex statements so that doesn't surprise me.

This should give you enough information to go through your query plans and see what's being used, what's rarely used and how much space they take up.