0

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?

1 Answer 1

3

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:

CREATE EVENT SESSION ExecPlansDuration
ON SERVER
    ADD EVENT sqlserver.query_post_execution_showplan
    (WHERE ([duration] > (10000)))
    ADD TARGET package0.event_file
    (SET filename = N'ExecPlansDuration');

Just know that capturing plans can be very expensive.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.