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.
Query_post_execution_showplan
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.
Conclusion
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.
Great post!
So, are you saying that merely having the Extended Event defined and monitoring for this event is the expensive part? Or, is capturing the output when the event is encountered the expensive part? It sounds like the latter situation, but I wanted to be sure.
Please forgive my nube question. I am relatively new to SQL Server.
Never apologize for asking me questions. I live for them. Thank you!
The expensive part of the operation is capturing the execution plan itself. That process, especially capturing an execution plan plus runtime metrics, involves quite a bit of computing power to pluck the plan out of cache and capture all the metrics within the plan, as it executes. I recommend running this type of capture on a very limited basis with lots and lots of filtering so you only capture exactly what you need.
A secondary expense, but one worth noting, is that execution plans are big. Lots of XML. So, capturing lots of plans through Trace or Extended Events, means tons of I/O and tons of storage. Again, filtering and limited run times would be your friend here.
I hope that helps. Never hesitate to ask me questions, please.
Grant, I know in the past this method was looked down upon because it would capture too much information during every execution as per Jonathan Kehayias’s blog post. Has this issue been resolved with lightweight query execution statistics profiling?
I’m sorry. It’s some kind of issue with Idera and I just have no idea if it has been fixed or not. Sorry. As for extended events and execution plan capture, I would still, always, be cautious about what and how we capture plans. It is expensive to capture plans, trace or extended events. The nice thing about extended events, as this post shows, you can easily filter what gets captured.