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

refs:

http://www.carehart.org/blog/client/index.cfm/2015/2/10/show_running_sql_server_queries

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:

-- Who is running what at this instantSELECT dest.text AS [Command text] ,der.total_elapsed_time as 'total_elapsed_time (ms)',DB_NAME(der.database_id) AS DatabaseName ,der.command , des.login_time , des.[host_name] , des.[program_name] , der.session_idFROM sys.dm_exec_requests derINNER JOIN sys.dm_exec_connections decON der.session_id = dec.session_idINNER JOIN sys.dm_exec_sessions desON des.session_id = der.session_idCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS destWHERE des.is_user_process = 1 and-- next clause hides this query itself from list of running queriesdest.text not like '-- Who is running what%'-- if you want to limit the results to only queries for a given DB, uncomment next line-- and DB_NAME(der.database_id) = 'yourtablename'ORDER BY [total_elapsed_time (ms)] DESC

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 thatthere'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 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.

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, specificallyDatabase/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 mylast 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.