solving sql server problems for millions of dbas and developers since 2006

Identify and resolve SQL Server problems BEFORE they happen with SQL diagnostic manager

Top SQL Server Tools

SQL compliance manager

SQL Sentry Performance Advisor for SQL Server

SQL safe backup

SQL diagnostic manager

Session State Settings for Cached SQL Server Query Plans

By: Tim Ford | Read Comments (2) | Print

Tim is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

Related Tips: More

Problem
A cached query plan will not be used if the query is different than the original query used to generate the cached plan - makes sense - but what if the query is identical? Well even then the query optimizer may not used the cached query plan. Why? Because all the session settings for the current query request must match those from the first time the query plan was generated and cached. So the question is how can you determine what settings are associated with the cached plan? The answer is in the SQL Server Dynamic Management Objects.

Solution
I've spoken quite a bit as of late on the DMOs. These views and functions constructs exist as an abstraction layer for the system tables that are now protected inside of the resource database. The DMOs that accomplish the goal we're looking at examining today are the following:

sys.dm_exec_cached_plans - Provides a listing of all query plans still residing in cache.
sys.dm_exec_query_plan - This is a Dynamic Management Function that allows you to pass in a plan_handle (think of it as a unique identifier for a execution plan) as a parameter and return an xml link for the graphical execution plan. When you click on the link, the graphical execution plan opens as a separate tab within Microsoft SQL Server Management Studio. Pretty neat, eh?
sys.dm_exec_plan_attributes - This is the key DMV for accomplishing the goal we're setting forth. It exposes all session-level settings (attributes as they are referred to in this DMV) for a query plan.

Let's look at the query that puts all of this together:

SELECT DEQP.query_plan, DEPA.attribute, DEPA.value 
FROM sys.dm_exec_cached_plans DECP 
  CROSS APPLY sys.dm_exec_query_plan(DECP.plan_handle) AS DEQP 
  CROSS APPLY sys.dm_exec_plan_attributes(DECP.plan_handle) AS DEPA 
WHERE DECP.plan_handle = (0x06001201B3316A154003213A0200000000000000000000)

You may wonder how to determine the plan handle you're interested in querying against. There are many options and in my case I hard-coded the value based upon a specific value I returned from a targeted query against sys.dm_exec_requests for a query that was behaving poorly. The nice thing is that the DMV structure is so extensible. I could easily return results like above for a specific user (for example) by using the following query:

SELECT SDEQP.query_plan, SDEPA.attribute, SDEPA.value  
FROM sys.dm_exec_cached_plans SDECP 
   INNER JOIN sys.dm_exec_requests SDER ON SDECP.plan_handle = SDER.plan_handle 
   INNER JOIN sys.dm_exec_sessions SDES ON SDER.session_id = SDES.session_id 
   CROSS APPLY sys.dm_exec_query_plan(SDECP.plan_handle) AS SDEQP 
   CROSS APPLY sys.dm_exec_plan_attributes(SDECP.plan_handle) AS SDEPA 
WHERE SDES.login_name = 'DOMAIN\User'

The join structures are quite straight-forward and with the advent of IntelliSense technology in SSMS 2008 (or by use of SQL Prompt for example, which provides the same functionality) the joins usually present themselves. The results in this case are the same, but there is no need for me to do the initial leg work of identifying the exact plan_handle first. You can tailor the query to fit your needs going forth on your own.

Now that you know what session settings are associated with the cached plan, you structure the session state to match the cached plan and take advantage of performance gains that come with plan re-use.

Next Steps

Read more tips by the author here.
Interested in Dynamic Management Objects? Here are some more tips at MSSQLTips.com on that subject.
Query and Performance Tuning tips are accessible via this link.

Related Tips: More | Become a paid author

Last Update: 9/2/2009

Share:

Free SQL Server Learning

Comments and Feedback:

Wednesday, December 15, 2010 - 5:07:08 PM - Joshua Guttman

How about bringing up the obvios? These people should be using stored procedures, not cached prepared dynamic sql. SQL Server was designed from the ground up to work a certain way. Yes, there are nice features out there that make things easy for the lazy developer, but they can also get you in a lot of trouble. Look up deadlocks if you like dynamic sql so much. Seriously, if you go against the design goals of SQL Server, you will get burned.

Friday, January 27, 2012 - 8:13:17 AM - Chuck Hottle

What my colleagues and I can't get a handle on is what the actual performace impact of having different SET options. For example, the most common difference that we see is ARITHABORT between SSMS and .Net connections. Just because these two executions will have different plans doesn't explain why sometimes the one with ARITHABORT off gets a bad plan. It shouldn't really affect anything, right. This is a common 'fast in SSMS, slow in application scenario'.

Sponsor Information

"SQL diagnostic manager delivers response in minutes, not hours!"

SQL Backup Pro was the smartest kid at school. Head of the class for compression, encryption and centralized management. Discover why.

Make the most out of SQL Server - Guaranteed Results - Innovative SQL Server DBAs

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

The SQL Server Security THREAT - It’s Closer Than You Think