Using sql profiler, we trace all slow queries (filter on duration/reads) to see where we can optimise.
Events used:
- RPC:Completed
- SQL:BatchCompleted
Filter on Duration.
If I add the event ShowPlan XML, then I cannot filter on the duration of the underlying query, creating a huge load as hundreds/thousands of queries arrive each second
How to only capture the execution plan for the entries that match the filter duration/reads of the captured queries from the other events?
Best Answer
The easiest way would be to stop using Profiler and go to Extended Events. The query_post_execution_showplan event has duration built right in and you can use that to filter capture quite neatly. Here's a simple example:
Just know that capturing plans can be very expensive.