SQL SERVER – Inserting sp_who2 Into a Table

Earlier this week I blogged about SQL SERVER – Representing sp_who2 with DMVs and lots of people asked me that I should also have included the script for method 1 discussed in the blog post. Let us learn a method of inserting sp_who2 into a table.

CREATE TABLE #sp_who2 (SPID INT, Status VARCHAR(255),
      Login  VARCHAR(255), HostName  VARCHAR(255),
      BlkBy  VARCHAR(255), DBName  VARCHAR(255),
      Command VARCHAR(255), CPUTime INT,
      DiskIO INT, LastBatch VARCHAR(255),
      ProgramName VARCHAR(255), SPID1 INT,
      REQUESTID INT);
INSERT INTO #sp_who2 
EXEC sp_who2
SELECT      *
FROM        #sp_who2
-- WHERE       DBName <> 'master' -- Add Filter
ORDER BY    SPID ASC;
DROP TABLE #sp_who2;

Well, that’s it. You can use the script above and add necessary filters as well as order to get the necessary results. Additionally, you can also store the values in a table for a longer period and analysis them later on. While inserting sp_who2 results into the table looks simple, I personally still prefer the method to use DMV during my Comprehensive Database Performance Health Check.

Here are a few recent blog posts on the same topic which you may find interesting:

• Slow Running Query – SQL in Sixty Seconds #146
• Sleeping vs Suspended Process – SQL in Sixty Seconds #122
• Recent Execution of Stored Procedure – SQL in Sixty Seconds #118

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.