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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
As always, you are THEE one and only man! Thanks.