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

use YourDatabaseName

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 usecounts field 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 inserted and 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

dbcc freeproccache

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:

exec uspGetEmployeeManagers

      @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:

exec uspGetEmployeeManagers

      @EmployeeID = 2

go

                Run the SELECT against sys.dm_exec_cached_plans again 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_handle and 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.