[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 use), they may wonder if (or have tools which suggest that) their CF requests are being held up waiting for some long-running query to run in the database.

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.

(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

Following is the code, and then some discussion of it:

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 had way 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 you're 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).

And sometimes, it proves that a problem is NOT due to slow queries

Another benefit of a tool 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 that it'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.

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).

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 S

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