Free, simple code to find out what SQL statements are running slow in SQL Server right now

Note: This blog post is from 2015. Some content may be outdated--though not necessarily. Same with links and subsequent comments from myself or others. Corrections are welcome, in the comments. And I may revise the content as necessary.

Wouldn't it be nice to know, at any moment (such as when things are going badly), just what queries (or stored procedures or commands) were running in the database at that point in time?

Well here's good news: if you're running SQL Server, the following SQL query will show you just that: the currently running SQL statement(s) and some additional details about each query including their duration, their database name, the program executing the SQL, the session id, and much more.

For more (including why things like Activity Monitor's "recent expensive queries", sp_who2, and others aren't the answer, as well as what to do if you may be unable to run this code), please read on. (I also mention the classic sp_whoisactive as another solution, and have updated this post since its 2015 writing to mention the newer "active expensive queries" in Activity Monitor, which you may find in later versions of SQL Server, both of which you can consider in addition to the below.)

The code for SQL Server

Here is the code, with discussion to follow (click the "view raw" button at the bottom of the box to more easily copy the code):

This is a very fast-running and powerful tool to understand what, if anything, is currently running in SQL Server.

Note a couple of lines offered as comments (starting with --). The first just comments what this code does (and note that I use it to keep this line itself from showing as one of the "running queries").

Near the bottom (in the WHERE clause), if you somehow had too many results from this query and wanted to focus only on the queries related to some specific table, you could simply remove the two dashes and change the value in 'yourtablename'.

Note also that you could swap out the:

with

to get the total elapsed time column in seconds rather than milliseconds. Choose whichever you may prefer to use.

Because this code relies on "data management views" or DMV feature of SQL Server 2005, this code runs only on that version or above (and perhaps not if you're running in SQL Server 2000 compatibility mode. If your server is that far behind, you have many other reasons you should consider updating, but this is not the place to debate/discuss that.)

Finally, note that while you might think first to run this sort of SQL in SQL Server Management Studio (SSMS), and that's ok (use it's File>Save feature to easily store and reuse it), but note that there's no reason you couldn't also run it right from within CFML within a CFQUERY or its cfscript equivalent, which may be even easier for some folks to use readily and as needed (as long as CF is not bogged down for some reason and can talk to the SQL Server, of course).

What if you can't run this code?

I said above that the code is based on DMVs, and perhaps the user you are running your SQL as may not have permission to run those DMVs. In that case, you won't be able to run this unless you can get the permissions added or change to using a user who has the permissions.

Another problem may be that you're on a hosted DB server (or server managed by others in your organization), where you would not only never be given the permissions to run this code, but you may also be told that you can't see the info that this SQL exposes because it provides SQL that OTHERS beside you (and your app) may be executing. There may still be a possible work-around for this.

First, you may be able to get your host or DBA to create a stored procedure where they put in this code and set it to allow you to run it, since it's a static set of SQL that they can know does nothing more than share the info that it offers.

Second, if they'd be concerned about you seeing the queries run for others, they could use a WHERE clause (as I discussed above) that limits it to show only requests against your own database(s).

Third, there is a problem with limiting the SQL that way because sometimes the problem is not the queries running against YOUR databases but those running against OTHERS. (They could be holding up the whole SQL Server instance, in some cases.) For this situation, it would be great if the folks creating the SP would ALSO show you at least WHETHER any other SQL statements are running for OTHER databases. They can withhold from THAT second query the columns above for the SQL statement, DB name, user, etc.)

If they'd do that, then you could call that SP from CF (whether via a cfstoredproc or cfquery). You'd want to make sure it works when all's well, of course (and it may show nothing running). But then when things start slowing, you could run it and see if it showed you a) your own requests and/or b) any others that may be holding up the server.

You may wonder if you "could" run a CF template executing that query when things were hungup. It depends on whether the number of running/hanging CF requests reached the value of "max simult requests" in tour CF Admin "request tuning" page. Even if the request ran, it's possible that the DB itself could be hungup and so may not respond. That would itself be useful diagnostic info, for you and the DB server host/dba.

Of course, to know for sure if and how many CF requests are running, you need a monitoring tool to tell you that. I have written and spoken many times in the past about monitors like the CF Enterprise Server monitor, FusionReactor, and SeeFusion.

And sometimes, this proves that a problem you see is NOT due to slow queries

Another benefit of code like this is that we can use it not only to report WHAT SQL or SP calls are running, but it can also be helpful to determine (with certainty) whether ANY SQL or SP calls are currently running.

Sometimes when a CF monitoring tool might report a request(s) waiting for "long-running queries", a tool like this (which you can trust) may report that in fact there ARE NO long-running queries. In that case, the problem must be something else, but masking as a "query" problem.

It could be in the network BETWEEN CF (or your app server) and SQL Server, or it may be a problem on the SQL Server machine that is preventing it from running queries at all (such as running out of memory, or out of CPU, or perhaps having a problem with the NIC, or the virtual NIC if a VM, or perhaps over-allocation of resources to the VM by the hypervisor, and so on).

It could be high CPU on CF or the box, preventing jdbc connection pooling from being able to happen, and so on.

The point is, this tool can help explain first and most simply whether there ARE or ARE NOT long-running queries, when a monitor on the CF side says that one or more CF requests are "waiting" for the database to respond.

Why use this versus "Activity Monitor", sp_who, etc?

A natural question will arise to long-time SQL Server users: "well Charlie, why don't you just have people open the SSMS Activity Monitor to see its list of slow queries, and call it a day?" Or what about built-in tools like sp_who and sp_who2?

To clarify, Activity Monitor is available in most editions of SSMS and is accessed by right-clicking on the DB server in top of the object explorer on the left of SSMS, once connected. It's a useful tool in its own right, so I do indeed point people to it for various reasons.

But the reason I don't find it as helpful for this particular problem (show me the currently running SQL queries) is twofold: first, while its "recent expensive queries" tab does seem to show "your slowest queries", it's based on averages. I've seen times when it did not show a currently running slow query, simply because the same query had run many times quickly before, and so the "average" was too low for it to rank in the list.

Update: Since posting this in 2015, I should note that in later versions of SSMS add a new "Active Expensive Queries" option, below "Recent Expensive Queries", which would seem to help alleviate the need of this code above. Sadly, it does not work on all versions of SQL Server (for instance, when I connect to my SQL Server 2008 R2 instance, the "Active Expensive Queries" feature doesn't appear at all). If anyone knows what version of SSMS added it, and what versions of SQL Server support it, please do share.

And second, while its "processes" tab does show all current threads, it only depicts slowness in terms of the "wait time" column. That won't always (indeed, seems rarely to) be the same as total time to run the query, which IS reflected in the duration column shown in the SQL above. Why doesn't Process Monitor simply offer such a duration column? I don't know, but it does NOT!

Another minor annoyance is that you can't see the actual SQL statement without right-clicking the Activity Monitor "processes" interface on the desired query to see its "details". Plus, it shows ALL threads, whether running a query or not, which makes it cluttered, and while it has sort and filter abilities, it's rather clumsy.

(And I'm not the only one to complain about it. SQL Server troubleshooting maven Brent Ozar is no fan of Activity Monitor, either. He's more of a fan of Adam Mechanic's sp_whoisactive, which is another tool to consider, though I find its output a bit cumbersome compared to what I share below. Still, it's more powerful and you may wan to consider it seriously also.)

Finally (and most important to some), some people may either not have or can't open Activity Monitor (due to privileges or edition differences). Again the SQL above can be run from within CFML, so one need not even have a connection to the SQL Server itself (as is required of SSMS). They can run this against any CF DSN which points to a SQL Server database.

And while there may be other tools out there (free or commercial) which also focus on SQL Server monitoring, I've not found any that so readily and easily (and inexpensively and without doubt) simply list the *currently running queries*. Many focus (like Activity Monitor) either on long "waits" or on queries that are slow "on average" over time, neither of which is always that useful, for the kind of problem this code solves.

(That said, tracking performance data over time has its real advantages and I recommend you do consider also such tools for DB monitoring, as I point to as a category of my CF411 site, specifically Database/SQL Monitoring Tools.)

Where I got the original version(s) of this code

Finally, I should note that I did not come up with this code myself, though I did tweak it based on a couple of references which offered variants of it which were very close to each other. Each offered something the other didn't, and I added still more.

One was from a blog entry of the the classic SQL Authority blog of Pinal Dave. The other was on p67 of the book, "Performance Tuning with SQL Server Dynamic Management Views", by Louis Davidson and Tim Ford, available in print and as a free ebook from Redgate. My sincere thanks to them both.

And if you don't follow Pinal Dave's blog, you're really missing out. He's the Ben Nadel of the SQL Server world :-), generating great content with wonderful discussion and code formatting, nearly every day. He's also a past CF guy!

And as for the RedGate book, see my high regards for another in that line of free ebooks, as well as a pointer to the rest of them, in my last blog entry.

Hope all that's helpful. Let me know how it works for you, and of course any problems, as well as any tweaks you may offer to improve it still further.

PS Here is a StackOverflow post showing similar code for Oracle. And for Mysql, you can use "show processlist".

For more content like this from Charlie Arehart:

• Signup to get his blog posts by email:
• Follow his blog RSS feed
• View the rest of his blog posts
• View his blog posts on the Adobe CF portal

Need more help with problems?

• If you may prefer direct help, rather than digging around here/elsewhere or via comments, he can help via his online consulting services
• See that page for more on how he can help a) over the web, safely and securely, b) usually very quickly, c) teaching you along the way, and d) with satisfaction guaranteed