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)',
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