How To Find Long Running Queries in SQL Server Using Extended Events - Tutorial

In this SQL tutorial we look at creating an Extended Event to find long running queries within SQL Server.

Within a Live Production Environment we are constantly battling against poor running queries which will impact overall SQL Server performance. Historically we have used SQL Profiler, run a trace and identified any problematic stored procedures that are returned when we happen to be running our trace. With Extended Events we can create a Session that will just capture specific data if it fits within our chosen criteria.

In this video, I've used queries that run over 10 seconds. Ordinarily we would probably set that threshold to 30 seconds or a minute (the video would take far too long to do that and become quite boring so I've done you favour and reduced the threshold!!). Once we've identified these routines, we tune them and get them down to a more manageable level. Once we got rid of most of the items in the Extended Event session we can change the threshold to 30 seconds and tackle the next batch of queries. Over time we are going to see huge improvements on wait time, CPU and overall performance.

As with all performance tuning, there is very rarely one thing that resolves all our problems but we need to tackle one thing at a time - this event will give us a huge head start.

I hope you enjoy on performance tuning SQL Server tutorial, if so leave a comment below.

[surendrasiddhi]

Thanks for the great tutorial. It is very helpful.

[CarrigansGuitarClub]

That was very good - do you know of other ways to capture long running queries?

[hindzaafouri]

Hey, thank you for the tutorial, Is it possible to convert .xel files to .json please ?

[nickrandles]

Hi, thanks for the tutorial. Can you please explain why the time for the 11 second query is stored as 11052568? Is it recorded in microseconds rather than milliseconds?