The Procedure Cache

                The procedure cache is the portion of memory that SQL Server stores execution plans, to include query plans and execution contexts.  The life cycle of a statement executing starts with SQL Server first checking the procedure cache to see if a corresponding execution plan exists.  This caching technique allows the database engine to ensure the fastest amount of time for execution.  If SQL Server finds the execution plan, then it will reuse that plan without recompiling the statement at hand.  But if the database engine does not find a plan for the statement it will generate a new plan and store it in the procedure cache.
                In order to get some further details on what is stored in the procedure cache, there are a handful of useful DMVs to use in the search.  For instance, if you wanted to retrieve all of the stored procedure execution plans for a particular database, you could do this:
— substitute ‘YourDatabaseName’ with the desired db
useYourDatabaseName
go
select
      db_name(st.dbid) as database_name,
      object_name(st.objectid) as name,
      p.size_in_bytes / 1024 as size_in_kb,
      st.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where p.objtype = ‘proc’
and st.dbid = db_id()
                Something very interesting (and to further fortify the beauty of stored procedures and SQL Server’s ability to reuse stored procedure exec plans) is to notice the use counts on Ad Hoc plans:
select
      p.size_in_bytes / 1024 as size_in_kb,
      p.usecounts,
      s.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) s
where p.objtype = ‘adhoc’
                SQL Server increments the usecountsfield every time it reuses that specific plan and is a good indicator of plan recycling.
Plan Recompilation
                There are many things that may cause plans to be recompiled.  There is a very detailed list (that I will summarize below) as a great reference from BOL.  Plans will be recompiled during the following occurances:
·         table or view changes by the referencing query plan text
·         index changes used by the referencing query (to include a dropped index)
·         statistics updates used by the referencing plan
·         sp_recompile execution
·         large number of changes to keys
·         significant growth of the number of rows in the insertedand deleted dynamic tables on tables with triggers
·         utilizing the WITH RECOMPILE option when executing a stored procedure
Working Example
            Here’s a working example using our trusty AdventureWorks database (with SQL Server 2008 R2).  Let’s first run the below query just to clear the proc cache and start from scratch (I know this goes without saying, but please do this testing on your test machine nowhere near production):
use AdventureWorks
go
— start from scratch
dbccfreeproccache
select
      db_name(st.dbid) as database_name,
      object_name(st.objectid) as name,
      p.plan_handle,
      p.size_in_bytes / 1024 as size_in_kb,
      p.objtype,
      p.usecounts,
      st.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where st.dbid = db_id()
and p.objtype = ‘proc’
The result set should be empty.  Now, just for the sake of getting a plan cached, run the following code:
execuspGetEmployeeManagers
      @EmployeeID = 1
go
                Now by running the same query from above (utilizing sys.dm_exec_cached_plans) WITHOUT the DBCC FREEPROCCACHE call (we don’t want to clear the cache this time), we should see results that look like this (fields omitted for brevity):
                name:                   uspGetEmployeeManagers
                plan_handle:     0x0500090076146E6C40C1389E000000000000000000000000
                usecounts:          1
                Go ahead and run the stored procedure again:
execuspGetEmployeeManagers
      @EmployeeID = 2
go
                Run the SELECT against sys.dm_exec_cached_plansagain and you’ll see results that look like this:
                name:                   uspGetEmployeeManagers
                plan_handle:     0x0500090076146E6C40C1389E000000000000000000000000
                usecounts:          2
                So the only thing that has changed is the usecounts counter has been incremented by one.  The plan_handle is the same and this just proves that SQL Server has reused this cached plan the second time we executed uspGetEmployeeManagers.
                Now what we want to do is cause a recompilation.  We will do this through the use of the system stored procedure sp_recompile:
exec sp_recompile ‘uspGetEmployeeManagers’
go
                And now when we gather our plan data again, we’ll see changed parameters like this, with a completely new plan_handleand a usecounts that starts back at one:
                name:                   uspGetEmployeeManagers
                plan_handle:     0x0500090076146E6C4061439E000000000000000000000000
                usecounts:          1
                In this blog post I have briefly explained what the procedure cache is to include what is stored there and when plans will be recompiled.  With the above example, there are endless ways to see this in action.  The useful source code for this blog post can be found here: BufferPool_PlanCache_StoredProcsForDB.sql.  If there are any comments, questions, or issues please feel free to leave a comment or email me at sqlsalt@gmail.com.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s