How to monitor activity in SSMS only with T-SQL?

Question

I use the following statement to monitor activity in SQL Server (This is out of some book)

SELECT 
    des.session_id ,
    des.status ,
    des.login_name ,
    des.[HOST_NAME] ,
    der.blocking_session_id ,
    DB_NAME(der.database_id) AS database_name ,
    der.command ,
    des.cpu_time ,
    des.reads ,
    des.writes ,
    dec.last_write ,
    des.[program_name] ,
    der.wait_type ,
    der.wait_time ,
    der.last_wait_type ,
    der.wait_resource ,
    CASE des.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'ReadUncommitted'
        WHEN 2 THEN 'ReadCommitted'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END AS transaction_isolation_level ,
    OBJECT_NAME(dest.objectid, der.database_id) AS OBJECT_NAME ,
    SUBSTRING(dest.text, der.statement_start_offset / 2,
    ( CASE WHEN der.statement_end_offset = -1
    THEN DATALENGTH(dest.text) ELSE der.statement_end_offset
    END - der.statement_start_offset ) / 2 + 1)
    AS [executing statement] ,
    deqp.query_plan
FROM sys.dm_exec_sessions des
LEFT JOIN sys.dm_exec_requests der
ON des.session_id = der.session_id
LEFT JOIN sys.dm_exec_connections dec
ON des.session_id = dec.session_id
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp
WHERE des.session_id <> @@SPID
ORDER BY 3

Some activities are not shown here: create index, alter table...

How to monitor all activities in SQL Server? (Activity Monitor/Profiler is no option - only plain T-SQL, no sysadmin - only view server state)

Answer 1

Not all actions have execution plans; changing the cross apply to outer apply will allow you to see these other items.

SELECT 
des.session_id ,
des.status ,
des.login_name ,
des.[HOST_NAME] ,
der.blocking_session_id ,
DB_NAME(der.database_id) AS database_name ,
der.command ,
des.cpu_time ,
des.reads ,
des.writes ,
dec.last_write ,
des.[program_name] ,
der.wait_type ,
der.wait_time ,
der.last_wait_type ,
der.wait_resource ,
CASE des.transaction_isolation_level
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'ReadUncommitted'
    WHEN 2 THEN 'ReadCommitted'
    WHEN 3 THEN 'Repeatable'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level ,
OBJECT_NAME(dest.objectid, der.database_id) AS OBJECT_NAME ,
SUBSTRING(dest.text, der.statement_start_offset / 2,
( CASE WHEN der.statement_end_offset = -1
THEN DATALENGTH(dest.text) ELSE der.statement_end_offset
END - der.statement_start_offset ) / 2 + 1)
AS [executing statement] ,
deqp.query_plan
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_requests der
ON des.session_id = der.session_id
LEFT JOIN sys.dm_exec_connections dec
ON des.session_id = dec.session_id
OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) dest
OUTER APPLY sys.dm_exec_query_plan(der.plan_handle) deqp
WHERE des.session_id <> @@SPID and des.is_user_process = 1
ORDER BY 3

Alternatively, I recommend looking at sp_whoisactive by Adam Machanic which is a stored procedure that gives a heap of information including length of execution, the plan, currently waiting tasks (what and duration)

Answer 2

Here's a few popular ways to do it:

sp_WhoIsActive

Pretty much the industry standard tool from Adam Machanic. Download it from WhoIsActive.com, install it in the master database (so you can call it from anywhere), and then run it whenever you want to know what's running now. Here's what it looks like:

Queries are sorted from longest-running to shortest. You can click on the sql_text or query_plan to see that stuff (note that the latter is only included when you run it with @get_plans = 1). It'll also show you who's blocking who, what machine the queries are coming from, and much more.

It's free, and it works with all currently supported versions of SQL Server. It's used by thousands of database professionals all over the world. I've used it myself in production all over the place, and never had a problem with it.

sp_BlitzWho

Erik Darling wrote this because he's really into query tuning, and he wanted to see more details than sp_WhoIsActive provides. Starting with SQL Server 2012, Microsoft has added a ton of cool DMV info like memory grant utilization, and Adam hasn't been updating sp_WhoIsActive to leverage it. You can get sp_BlitzWho from the public Github repo or from our First Responder Kit. Here's what it looks like:

At first, it basically looks like sp_WhoIsActive, but here's some of the additional columns it shows:

• Parallelism - last_dop, min_dop, max_dop, last_reserved_threads, min_reserved_threads, last_used_threads, and more
• Memory grants - last_grant_kb, min_grant_kb, max_grant_kb, last_used_grant_kb, min_used_grant_kb, max_used_grant_kb, last_ideal_grant_kb, and more
• Resource Governor - workload_group_name, resource_pool_name

The data can be pretty overwhelming - there's a lot of it - so start with Adam's sp_WhoIsActive, and only graduate to sp_BlitzWho if you need to do live query tuning.

sp_BlitzFirst

I wrote sp_BlitzFirst to go a little deeper and ask, "WHY is the server slow right now?" It takes a snapshot of a bunch of dynamic management objects, waits 5 seconds, takes another snapshot, and compares the difference between the two to figure out what's happening now. You can get it from our site or from the public Github repo. Here's what it looks like:

It gives you a prioritized list of server issues, and URLs to copy/paste into your browser to learn more about each problem. For example, in the screenshot above, the server's slowing down because some meatball is running a backup right now. You can click on the "ClickToSeeDetails" line to see, uh, details:

Backup of StackOverflow database (93GB) is 0.896017% complete, 
has been running since Sep 22 2017  5:19AM. 
Login: Brent  
Over the last 60 days, the full backup usually takes 4 minutes.

This way, you can give this tool to less-experienced folks like your help desk and have them do some initial triage.