x

capturing showplan XML in traces/profiler for ONLY CPU greater than 999

I have a database that has a lot of adhoc sql being run against it, so i don't want to capture all execution plans. Only want those plans that are consuming lots of CPU.

Is there an elegant way to do this? I cannot seem to filter out all the showplans for insignificant queries

more ▼

asked Nov 30, 2009 at 10:27 PM in Default

Nick Kavadias gravatar image

Nick Kavadias
476 3 3 7

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

No, there's no way to filter what you capture using trace events. What you can do though is to pull the execution plan out of the cache. You can query the dynamic management object sys.dm_exec_query_stats. That will give you aggregate information on the queries that have been running on the system. You can use this to determine which queries you're interested in, the longest running, most frequently called, etc. That comes with a plan_handle that you can then use to query against the dynamic management object sys.dm_exec_query_plan to get the execution plan for the longest running queries.

If you have a lot of ad hoc queries running, you might want to automate this to run every 1/2 hour or some other interval since these queries are cache dependent.

Also, since you're looking at ad hoc queries, you can use the query_hash in sys.dm_exec_query_stats as a means of combining queries that are similar to get better information. Of course, if you do that, you will need to worry about having X number of query plans returned, so there might be a bit of a balancing act needed there.

more ▼

answered Nov 30, 2009 at 11:42 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
100k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x41
x34
x18
x5
x3

asked: Nov 30, 2009 at 10:27 PM

Seen: 2183 times

Last Updated: Nov 30, 2009 at 11:46 PM