Skip Navigation LinksHome > Categories > Code from a Category

What Query Plans are in SQL Server's Memory?



User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 1499
Add Date: 12/09/2013
In this article we will learn about what query plans are in sql server's memory.
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:
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.


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:

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.

Happy Programming!!!

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.