SQL SERVER – Representing sp_who2 with DMVs

3 years ago

During the recent Comprehensive Database Performance Health Check, I had a very interesting situation where my client wanted to capture the details of the sp_who2 in a table. Let us learn how we can do that with the help of the DMVs.

When you run sp_who2 is an undocumented stored procedure that displays the current session ids and their activities. While sp_who2 is very friendly and easy to use most of the time, it is not straightforward to store the data of the stored procedure to the table.

There are two different workarounds to the problem statement of storing the data of sp_who2 into the table. The first one is to create a temporary table manually first and running the sp_who2 frequently to store the data in the table. This is not straight forward and often users find it cumbersome.

Here is the second alternative which is my preferred way as I can now represent sp_who2 with DMVs. Let us see how we can do that.

Here is the script which is built with DMVs and produces similar results as sp_Who2.

SELECT  spid,
        sp.[status],
        loginame [Login],
        hostname, 
        blocked BlkBy,
        sd.name DBName, 
        cmd Command,
        cpu CPUTime,
        physical_io DiskIO,
        last_batch LastBatch,
        [program_name] ProgramName     
FROM master.sys.sysprocesses sp 
INNER JOIN master.sys.sysdatabases sd ON sp.dbid = sd.dbid
WHERE spid > 50 -- Filtering System spid
ORDER BY spid

The only change between the script here and sp_who2 is a WHERE condition which filters out all the system sp_id with a value less than 50. You can additionally put additional filters or even add more columns to the query to adjust it to your need.

If you want to store the data directly into the table, you can also use INTO #TempTable command between the FROM and the first INNER JOIN.

Let me know what you think of this blog post and if you want me to create a SQL in Sixty Seconds post for this video. If you have a similar script that you may find will be helpful to users, please do share it with me and I will post it on the blog with due credit to you.

Reference: Pinal Dave (https://blog.sqlauthority.com)