Michael J. Swart

So I got a problem referred to me yesterday where a colleague wrote:

This [query] seemed quite odd to me at first… I ran SQL profiler to catch the exact sproc that was being called when the timeout occurred and ran it manually on the DB… it produced no results and ran in under 5s…

Sure enough I also had trouble reproducing the bad performance. It turns out this sproc was building a query dynamically and running it. Somehow it selected different plans based on the context of an app, or the context of a query window.

But I finally figured out how to call the sproc from a query window with the plan taken from the application. Long story short, I forced the plan on the query as described here

Short story long:

First step is to find the sql handle and the plan handle being used by the application (add whatever filters you need):

SELECT TOP 10 st.TEXT, er.sql_handle, er.plan_handle
FROM sys.dm_exec_requests er
CROSS apply sys.dm_exec_sql_text st(er.sql_handle)
ORDER BY er.start_time ASC

Then paste the following query in to your query window. This is the syntax for the USE PLAN option.

<query text> 
OPTION (USE PLAN N' 
<query plan>
') 

Replace the <query text> above with the text that you got from the first query above. Replace the <query plan> with the plan you get from the sys.dm_exec_query_plan() function. Don’t forget to change each single quote into two single quotes.

What does this help you do?
It helps reproduce problems so that you can dig deeper into the execution of a query. This is mostly useful when the query plan isn’t enough. You might want the query plan as well as the performance statistics. Or maybe you want to take this query and run it on a different dev machine. In my case, it helped me track down which cost estimates in the plan were out of whack and ultimately which statistics were out of date.