[Looking for Charlie's main web site?]

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

Often when people are trying to troubleshoot seeming problems in ColdFusion (or whatever app server you may use), they may wonder if (or have tools which suggest that) their CF requests are being held up waiting for a query to run in the database, which seems hung up.

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 what to do if you may be unable to run this code, as well as something similar for Mysql, please read on.

Briefly for Mysql users...

First, if you're running MYSQL, you may know that you can get pretty much the same info with SHOW PROCESSLIST. Or if you want to do it as SQL, you can use SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep'. Sadly, it's just not that simple in SQL Server, it seems, thus the need for this entry.

The code for SQL Server

Here is the code, with discussion to follow:

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:

der.total_elapsed_time as 'total_elapsed_time (ms)',

with

cast(cast(der.total_elapsed_time as float) / 1000 as decimal(10,3)) as 'total_elapsed_time (secs)',

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"?

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?" (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 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.

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) simply list the current running queries. Sure, some may track the above over time, which would be helpful, but many do focus (like Activity Monitor) either on long resource 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. (But no question, tracking data over time has its real advantages and I recommend you do consider also such tools, 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 long been a fan of CF!

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.

Comments
+1 for DMVs - we actually use this in conjunction with FusionReactor to monitor our servers as FR only captures up to the JDBC connector and nothing more.
Great post and kudos to Pinal Dave who has the best one liner quotes! "there's no silver bullet to performance" and "database optimisation is a journey" haha
# Posted By Joey Daly | 2/10/15 11:01 PM
@Joey, amen, and thanks for the kind regards. :-)
# Posted By charlie arehart | 2/11/15 7:52 PM
Hi, This is great but it does not show me the host name nor the program name, any ideas why please? We have some spurious queries running on our server and we cannot find which program or job is running them.
# Posted By Angela Brettell | 7/20/15 2:22 PM
Angela, you say, "it does not show me the host name nor the program name, any ideas why please? "

To be clear, if you look at the code, those ARE columns in the SELECT statement. When you say "it does not show them", what do you mean?

Are they blank for you? If so, I'm afraid I have no answers for you. This is just SQL code pulling from a MS-provided DMV. You'd need to take it up with them (or do some searching) to find why those values are blank in your situation. (They are not blank in mine.)

Sorry I can't help more.
# Posted By charlie arehart | 7/20/15 2:40 PM
Good info. Great example. Let me introduce other material that may be good for net community http://www.sqlmvp.or...
# Posted By johnson | 11/7/16 3:20 AM
Johnson, I don't see how that post offers any more meaningful insight into the subject of *identifying slow SQL queries*. It's focused rather on WHY queries may be slow, and with rather generic tips at that. (And the couple of brief mentions of how to find slow queries re also themselves quite brief and not nearly as targeted as what I found and share here.)

So the link you share is not really directly relevant, but since it's a blog post and not a link to a product or product-oriented resource, I won't delete it. Maybe someone may get some value from it or other posts there.
# Posted By Charlie Arehart | 11/7/16 6:51 AM
Copyright ©2018 Charlie Arehart
Carehart Logo
BlogCFC was created by Raymond Camden. This blog is running version 5.005.
(Want to validate the html in this page?)

Managed Hosting Services provided by
Managed Dedicated Hosting