I love questions. Most of all, I love questions I can answer. I spotted this question recently: How can I use Profiler to capture execution plans for queries over a certain duration?
Oh, that’s easy. You don’t use Profiler. You use Extended Events.
Extended events are just better than Profiler. Period. One of many things that is superior is the way in which the events are configured. Take for example query_post_execution_showplan. Here are the fields it captures:
This event will capture execution plans plus runtime metrics. It can easily be filtered on any of the fields listed, and you can even add the database_name field if you want.
So, to filter by duration is pretty simple:
CREATE EVENT SESSION ExecPlansDuration ON SERVER ADD EVENT sqlserver.query_post_execution_showplan (WHERE ([duration] > (1000000))) ADD TARGET package0.event_file (SET filename = N'new_session');
The only trick, and it’s not a trick, is knowing that the duration value is in microseconds. So the session defined above will only capture queries that run longer than one second.
I will add a caution here though. Whether you do something like this in Profiler/Trace or Extended Events, capturing execution plans is expensive. It will put a load on your system. Be very sure that you’re filtering the event well so you only capture what you really need. Read more about query profiling here.
Also, if you wanted to, you could add an Action to query_post_execution_showplan in order to capture the query text. So, in addition to being able to filter by object_id for procedures, you could filter by query or query snippet.
The key takeaway is that Extended Events simply offer more options and make those options easier to use. Rather than pound your head trying to figure out how to do something within Profiler, get started learning Extended Events. As you can see from the query above, implementing them is really simple. Add that tool to your toolbox.