filtering sp_who2 results

Posted by Professionaluser under Sql Server category on | Points: 40 | Views : 162
The overhead of sp_who2 is listing out all the sessions connected to SQL Server. Here its difficult to analyse the result set of sp_who2. We will feel that it would be good to have the filters on sp_who2 result set.... Below query helps us in that way..

CREATE TABLE #sp_who2Result (SPID INT,Status VARCHAR(255),
Login VARCHAR(255),HostName VARCHAR(255),
BlockedBySPID VARCHAR(255),DBName VARCHAR(255),
Command VARCHAR(255),CPUTime INT,
DiskIO INT,LastBatch VARCHAR(255),
ProgramName VARCHAR(255),SPID2 INT,
INSERT INTO #sp_who2Result EXEC sp_who2
FROM #sp_who2Result
-- Add any filtering of the results here :
WHERE DBName <> 'master' -- exclude system DBs if you want
and BlockedBySPID <>' .' -- returns blocking sessions
-- Add any sorting of the results here :
ORDER BY DBName ASC -- sort result to understand them in better way

DROP TABLE #sp_who2

Comments or Responses

Login to post response