Capture Execution Plans Only For Long Running Queries

SQL Server
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)))…
Read More