August 12, 2010 at 2:28 am
I've written a performance test harness to test as bunch of reports. These reports each have an associated SP which generates different queries using dynamic SQL depending on the parameters passed in, and executed using sp_executesql.
Additionally the performance test harness uses dynamic sql for generating the parameter list and executing the reports SP.
What I want to do is look at the query plan for the particular query that is running in the report at that time. I've tried using something like:
SELECT *
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS plan
However after this not returning anything for 15 mins I had to kill this.
If I run the following though I can see exactly what query is running
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
After searching around I found this BOL article
http://msdn.microsoft.com/en-us/library/ms189747(SQL.90).aspx
It says:
•If a Transact-SQL batch or stored procedure contains a call to a user-defined function or a call to dynamic SQL, for example using EXEC (string), the compiled XML Showplan for the user-defined function is not included in the table returned by sys.dm_exec_query_plan for the batch or stored procedure. Instead, you must make a separate call to sys.dm_exec_query_plan for the plan handle that corresponds to the user-defined function.
Basically is there anyway of being able to generate the plan for the current running query that has been generated using dynamic SQL?
This is driving me nuts.
Thanks,
Paul.
August 12, 2010 at 4:06 am
OK it turns out this does work. It's just taking an age to return the plan.
I stopped my testharness which had been running on the same query overnight
Ran DBCC freeproccache
Ran my SP directly and was able to generate the plan for the dynamic SQL query.
Does anyone know why the query for returning the plan might be taking so long??
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply