November 29, 2010 at 4:51 am
I want to capure & save the execution plan only for long running queries.I want to use this execution plan for later analysis. Is it possible to capture execution plan only for long running queries using server side trace
Thanks & Regards
Deva
November 29, 2010 at 5:55 am
You could, but you would better use DMVs for this task.
In this code sample I captured the top 20 queries by worker time, but you could easily change it filtering just for queries that exceeed what you decide is a "long" running time.
SELECT TOP 20
query_stats.total_worker_time,
query_stats.statement_text,
query_plan
FROM (SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset / 2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
) as query_stats
CROSS APPLY sys.dm_exec_query_plan(query_stats.plan_handle) as QP
ORDER BY 1 DESC ;
Hope this helps
Gianluca
-- Gianluca Sartori
November 29, 2010 at 6:16 am
Interesting question. I don't think that you can. If you look at the events that capture any form of execution plan, none of them allow for the duration column, so you wouldn't be able to apply a filter there. But, even if you could, capturing execution plans through trace events is a highly costly process. You need to be very, very, judicious in using it. If you could filter to only get long running queries, that might be enough, but it might not. You'd want to have a very good idea up front that you won't be slamming the server by attempting it.
I'd go with the DMV approach outlined above. It'll be much safer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2010 at 10:31 pm
Since i can't use this dmv's for following reason
1) When sql server services stop data in dmv will be cleared
2) When i use DLL statement on the particular object that information will be removed from dmv's
November 30, 2010 at 12:58 am
You could take a look at performance data collector:
http://www.simple-talk.com/sql/learn-sql-server/sql-server-2008-performance-data-collector/
-- Gianluca Sartori
November 30, 2010 at 6:07 am
deva21v (11/29/2010)
Since i can't use this dmv's for following reason1) When sql server services stop data in dmv will be cleared
Yes, but how often is that occurring? In production systems, hopefully not often, and, assuming it's on a scheduled & controlled basis, make sure you collect the metrics before hand
2) When i use DLL statement on the particular object that information will be removed from dmv's
I don't understand what you mean here.
You can use third party tools like Idera's Diagnostic Manager or Confio Ignite to capture long running queries, but those tools don't also capture the execution plans. You're still going to have to go to the DMVs to get them (or generate them by re-running the query, not always a favorable approach).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply