RSS

Capture Server Activity from Sp_Who2

03 Jul

One problem I’ve always had is not having enough time to investigate a performance problem due to the fact that the system stored procedure Sp_Who2 only displays current activity. I had an issue with a problematic query that was running occasionally. Emails would be sent out about CPU usage spiking on a production transactional system but they would be short lived.  I needed a way to save some of the activity so I could use it later.

So I can up with some simple code to throw a snapshot of the results from sp_who2:

/**Replace [DatabaseName] with the database in which you want to create this Audit Table**/

use [DatabaseName]

create table results_spwho2
(spid int,
status varchar(max),
login varchar(max),
hostname varchar(max),
blkby varchar(max),
dbname varchar(max),
command varchar(max),
cputime int,
diskio varchar(max),
lastbatch varchar(max),
programname varchar(max),
spid2 varchar(max),
requestid varchar(max))

use master
insert into [DatabaseName].dbo.results_spwho2
exec sp_who2
/**Feel free to modify the where clause to pick up programs with certain names that are accessing your server that you may want to narrow down on**/

 select *
 from [DatabaseName].dbo.results_spwho2
 —WHERE programname LIKE ‘%%’
 order by cputime desc

/**If you find a problematic SPID in the table that you would like to analyze you can use the following code**/
/**Just remember, this will not be stored historically you will be pulling only current SPID information**/

declare @spid int
set @spid = 140

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = @spid
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

/**Cleanup if necessary**/
use [DatabaseName]
drop table results_spwho2
/**or**/
truncate Table [DatabaseName].dbo.results_spwho2
Advertisements
 

Tags: ,

One response to “Capture Server Activity from Sp_Who2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: