Finding Long-Running Queries

Peter Schott has an Extended Event to find long-running queries:

This has been something I’ve wanted to investigate for a while now. I’ve know you could use Profiler and set up server-side traces to capture long-running events, but was curious how to do the same with Extended Events. I then came across this post from Pinal Dave ( b | t ) that pointed me in the right direction. I followed along with the guidelines he was suggesting and was having trouble finding the “Duration” filter. Turns out I had a bit too much selected in my filtering options or perhaps the Wizard was giving me fits seeing it, but I eventually selected just the Batch Completed or RPC Completed events to see and set the Duration filter. The one change that I’d make from Dave’s script is to set the duration to 500,000 because Duration in SQL 2012 is in microseconds, not milliseconds. I also want queries longer than 5 seconds to start.

Click through for the script.

Related Posts

Shredding Extended Event XML

Dave Mason shows us how you can use T-SQL to shred XML coming from extended events sessions: Querying the data of an Extended Events session has never been easy. My XEvent sessions typically store event data in a target file, which means using sys.fn_xe_file_target_read_file. To get something of value, you need to shred the event data […]

Read More

Monitoring Entity Framework

Grant Fritchey loves Entity Framework: Yes, Entity Framework will improve your job quality and reduce stress in your life. With one caveat, it gets used correctly. That’s the hard part right? There is tons of technology that makes things better, if used correctly. There are all sorts of programs that make your life easier, if […]

Read More

Categories

October 2016
MTWTFSS
« Sep Nov »
 12
3456789
10111213141516
17181920212223
24252627282930
31