She can dig it!
D Sharon Pruitt

In this new series of posts, I plan to investigate the SQL Server plan cache.  We’ll start with a little introduction of the plan cache and move into ways in which it can be used to find out what is happening on your SQL Server.

The Plan Cache

But what exactly is the plan cache?

The plan cache is a portion of memory in SQL Server which contains information on previously executed SQL statements and stored procedures.  It stores this information to allow for the re-use of execution plans.  This helps the SQL Server instance run more efficiently by storing information on how it previously executed SQL statements and stored procedures.

With the release of SQL Server 2005, the contents of the plan cache were made available through Dynamic Management Views (DMVs).  Through the DMVs information and statistics on plan use, SQL statements, and the SHOWPLAN XML can be accessed.

Plan Cache DMVs

The primary DMV for accessing the plan cache is sys.dm_exec_cached_plans.  This DMV provides one row of information for every query plan that is cached.  Some useful information available through this DMV is (and this list does not cover all of the columns):

• plan_handle (varbinary(64)) – Identity value for the plan.  This value is stored in memory and is only a valid reference for the plan while the plan resides in the plan cache.
• usecounts (int) – Number of times this cache object has been used since its inception.  This count is often useful when ranking plans.
• cacheobjtype (nvarchar(34)) – The type of object in the cache.  This can be any of the following types; Compiled Plan, Compiled Plan Stub, Parse Tree, Extended Proc, CLR Compiled Func, or CLR Compiled Proc.  The cacheobjtype that will be most often queried in this series will be Compiled Plan.
• objtype (nvarchar(16)) – The type of the object in the plan was created for.  This can be a stored procedure, prepared statement, ad-hoc query, trigger, etc.

With the plan_handle from sys.dm_exec_cached_plans, the plan cache can be queried further through the use of the DMV sys.dm_exec_query_plan.  This one is actually a Dynamic Management Function (DMF) and it accepts the plan_handle as a parameter.  In this DMF, the following columns will be of the most use:

• query_plan (xml) – The SHOWPLAN XML that was created when the query was executed.  There will be a single plan per batch executed.
• objectid (int) – ID value identifying the object for the query plan.
• dbid (int) – ID value identifying the database context for the query plan.

Similar to this is the DMF is sys.dm_exec_text_query_plan.  This also accepts the plan_handle as the first parameter.  The second and third parameters are statement_start_offset and statement_end_offset  These can be used to return the portions of a full query plan.  This is useful when the execution for a single statement is needed.

The primary columns of use here will be:

• query_plan (nvarchar(max)) – The SHOWPLAN XML that was created when the query was executed.  There will be a single plan per batch executed.
• objectid (int) – ID value identifying the object for the query plan.
• dbid (int) – ID value identifying the database context for the query plan.

Notice that the data type for query_plan has changed between the DMFs from xml to nvarchar.  This is because some execution plans can at times be too complex to be returned through sys.dm_exec_query_plan.  The query plan is returned as nvarchar in sys.dm_exec_text_query_plan to get around these limitations.

Plan Cache Wrap-Up

What will we be able to do with this information?

Well, for that you’ll need to wait for the next posts in this series.  This is just the starter post with the aim of providing a base of knowledge to start from.

The availability of the plan cache through the DMVs can be a powerful tool.  As we’ll see in some upcoming posts, this information can be used to uncover potentially painful performance problems.  Through the DMVs, this wealth of information is easily accessed and always available.  There won’t be a need to “turn it on” when a performance issue is suspected, it’s just there for you.

While it sounds all perfect, always remember that the plan cache is a portion of the SQL Server memory.  Stop the service or the server and the information is gone.  Stop running a query for a while and the plan will expire and flush out of the procedure cache.  Also, plans aren’t always perfect it displaying all of the information when a query is executed.  Execution plans for user defined functions aren’t included in the plans that call them.

Finally, as always, please leave a comment if there are questions or corrections.