[Looking for Charlie's main web site?]

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

[....Continue Reading....]

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. :-)
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.
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.
Copyright ©2024 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