What Statements are Running in my SQL Server Database

Sometimes you need to know what statements are running in your SQL Server database right now.  I've often had this need when someone shows up at me desk and says something like "Something is going on!  Your app is performing really slowly right now!".

In situations like this, there is a need to triage what is going on with the app.  Is it application code?  What about the web server, is the CPU spiked there?  What about the database?  Do we have some long running queries that are making things appear to be stuck?  Once we figure out what tier is responsible for the problem, we can drill down further to find and fix specifically what is going on.

To that end, one of the SQL Server queries I keep around is one that will query the SQL Server DMV's and report back all of the statements that are running right now in the database.  For any database that is in use, this query will always return some rows, because generally there is always some process somewhere that is running an application.  But you can also use this query to look for any long running or blocked statements, so lets take a look at the query.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

SELECT [DatabaseName] = db_name(rq.database_id), s.session_id, rq.status, [SqlStatement] = SUBSTRING (qt.text,rq.statement_start_offset/2, (CASE WHEN rq.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE rq.statement_end_offset END - rq.statement_start_offset)/2), [ClientHost] = s.host_name, [ClientProgram] = s.program_name, [ClientProcessId] = s.host_process_id, [SqlLoginUser] = s.login_name, [DurationInSeconds] = datediff(s,rq.start_time,getdate()), rq.start_time, rq.cpu_time, rq.logical_reads, rq.writes, [ParentStatement] = qt.text, p.query_plan, rq.wait_type, [BlockingSessionId] = bs.session_id, [BlockingHostname] = bs.host_name, [BlockingProgram] = bs.program_name, [BlockingClientProcessId] = bs.host_process_id, [BlockingSql] = SUBSTRING (bt.text, brq.statement_start_offset/2, (CASE WHEN brq.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), bt.text)) * 2 ELSE brq.statement_end_offset END - brq.statement_start_offset)/2) FROM sys.dm_exec_sessions s INNER JOIN sys.dm_exec_requests rq ON s.session_id = rq.session_id CROSS APPLY sys.dm_exec_sql_text(rq.sql_handle) as qt OUTER APPLY sys.dm_exec_query_plan(rq.plan_handle) p LEFT OUTER JOIN sys.dm_exec_sessions bs ON rq.blocking_session_id = bs.session_id LEFT OUTER JOIN sys.dm_exec_requests brq ON rq.blocking_session_id = brq.session_id OUTER APPLY sys.dm_exec_sql_text(brq.sql_handle) as bt WHERE s.is_user_process =1 AND s.session_id <> @@spid AND rq.database_id = DB_ID() -- Comment out to look at all databases ORDER BY rq.start_time ASC;

This query is using the sys.dm_exec_sessions view in conjunction with the sys.dm_exec_requests view to get all of the current running statements in SQL Server.  I am restricting this to the current database (line 39), but you can easily comment out this line to look database wide.

With just these two views, we do have some useful information like what time this statement started executing (line 13), how long it has been running so far (line 12), how much CPU it has consumed so far (line 14) and how many logical reads it has performed so far (line 15).  If we see a statement that has been running for a long time and/or has already consumed a lot of CPU and IO resources, then this is casuse for further investigation.

I also like to get some information about who is running a SQL statement that might be taking a long time, and that is what lines 8 through 11 do.  You would be amazed at how often someone logs into a production database from their desk and runs an ad-hoc query in the middle of the day from management studio or even Excel and has no idea that they are causing a performance impact on the database.  Columns like this help to pinpoint what process is running a statement that might be consuming a lot of resources.

Then, I'm pulling in some other useful information that we can look at in our result set.  Bringing in the sys.dm_exec_sql_text (line 30) allows us to include the statement that is running in the result set, which of course if a statement is taking a long time you want to know what the statement is.  We also use sys.dm_exec_query_plan (line 31) to pull in the execution plan for the statement.  If something is taking a long time, you probably want to just be able to click through and see the execution plan, so this lets us do that.

Finally, sys.dm_exec_requests contains a column called blocking_session_id.  If this statement is currently blocked by another, this value will be non-zero.  By joining this value back to the sys.dm_exec_seesions and sys.dm_exec_requests views a second time (lines 32-36), you can get information about the blocker (lines 21-26), like the blocking SQL and information about the program running that SQL.  Sometimes, you may catch a statement that is blocked when you run this query, but if you run this query twice in a row and you see the same blocker, you know you have a problem.

All in all, this query gives you the ability to get a good pulse on what is happening in your database right now.  When you are in the middle of troubleshooting a performance problem, this is very useful, because at a glance, you can determine if the problem you are facing is in the database tier and if so, probably get a pretty good idea of what might be the cause.  So keep this query handy, and next time you need to know what is happening right now inside of SQL Server, you will be prepared.