Sysprocesses Tracker

In developing some Server/Application baselines it is good to know some details about  who and what connects to the database server. The sysprocesses system table contains some good data for tracking down what is up on your SQL Servers.

I set out to create a VBScript that would go out and grab results from sysprocesses  from all  my SQL Servers on demand or on a schedule.

First step was to create a table to collect the data. So I hopped on my test server and created  the following table:

CREATE TABLE [dbo].[sysprocessTracker](
[spid] [int] NULL,
[status] [varchar](250) NULL,
[runid] [int] NULL,
[nt_username] [varchar](250) NULL,
[hostname] [varchar](250) NULL,
[blocked] [varchar](50) NULL,
[dbid] [varchar](50) NULL,
[cmd] [varchar](200) NULL,
[cpu] [int] NULL,
[physical_io] [int] NULL,
[memusage] [int] NULL,
[last_batch] [varchar](100) NULL,
[program_name] [varchar](200) NULL,
[nt_domain] [varchar](200) NULL,
[net_library] [varchar](200) NULL,
[login_time] [datetime] NULL,
[lastwaittype] [varchar](200) NULL,
[servername] [varchar](200) NULL,
[capturetime] [datetime] NULL,
[loginame] [varchar](100) NULL
) ON [PRIMARY]

The table represents the basic output from the master.dbo.sysprocesses table with a few tweaks.

  1. I added a column called servername. I set this during runtime of the script in the server loop. I add it  to my table so I can later query by server name.
  2. Capture Time. I added this column to the table to know when I captured the master.dbo.sysprocesses results.

I referenced a server loop in step 1. To run the script against all servers I setup a little server text file  with a list of my servers in the text file. When I add or remove a server I update the list.

Format of the Text File:

SERVER1
SERVER2
TESTSERVER1

After the setup was complete I wrote the script to go out and execute the master.dbo.sysprocesses query.

set fso = CreateObject(“Scripting.FileSystemObject”)
set f = fso.OpenTextFile(“C:\scripts\sysprocessTracker\servers+.txt”,1,True)
set counterFile = fso.OpenTextFile(“C:\scripts\sysprocessTracker\runcounter.txt”,1,True)
set logfile = fso.OpenTextFile(“C:\Scripts\sysprocessTracker\logfile.txt”,2,True)

counter = counterFile.ReadLine

counterFile.Close

dbs=”server=servername;DRIVER={SQL Server};database=dbname;Trusted_Connection=yes”
Set db = createobject(“adodb.connection”)
db.ConnectionString = dbs
db.Open

while not f.AtEndOfStream

lineArray = Split(f.ReadLine, “|”, -1)
serverName = lineArray(0)

ssql = “insert into sysprocessTracker (servername,capturetime,runid,spid,status,nt_username,hostname,blocked,dbid,cmd,cpu,physical_io,memusage,last_batch,program_name,nt_domain,net_library,login_time,lastwaittype,loginame) SELECT ‘” & servername & “‘,GETDATE(), ” & counter & “,spid,RTRIM(status),RTRIM(nt_username),RTRIM(hostname),RTRIM(blocked),dbid,RTRIM(cmd),cpu,physical_io,memusage,last_batch,RTRIM(program_name),RTRIM(nt_domain),RTRIM(net_library),login_time,RTRIM(lastwaittype),RTRIM(loginame) FROM OPENROWSET(‘SQLOLEDB’,’Server=” & serverName & “;Trusted_Connection=Yes;Database=Master’,’select spid,sp.status,nt_username,hostname,blocked,sd.name as dbid,cmd,cpu,physical_io,memusage,last_batch,program_name,nt_domain,net_library,login_time,lastwaittype,loginame from master..sysprocesses sp, master..sysdatabases sd where sp.dbid = sd.dbid’) ”
On Error Resume Next
db.Execute(ssql)
If Err.Number <> 0 Then
logfile.WriteLine(“error connecting to: ” & servername & ” — ” & Now)
Err.Clear

End if
wend

set counterFile = fso.OpenTextFile(“C:\scripts\sysprocessTracker\runcounter.txt”,2,True)
newcount = counter + 1
counterFile.WriteLine(newcount)
counterFile.Close

ssql = “select count(*) from sysprocessTracker where capturetime < GETDATE() – 2”
set rs = db.Execute(ssql)
if not rs.EOF then
logfile.Writeline(“Rows Deleted: ” & rs(0))
end if

‘put this in here to keep the table cleaned up. You can remove this if you want to view more than 2 days worth of history
ssql = “delete from sysprocessTracker where capturetime < GETDATE() – 2”
db.Execute(ssql)

db.Close
logfile.close


Update:

The script will loop through my server text file and then insert the results of the sysprocesses call into a table. The  most difficult part was working out the INSERT command based on results on the OPENROWSET command. That took a few rounds but worked out well.  Initially I tried creating a temp table then selecting the temp table into a recordset, parsing the recordset and creating the insert. I had some problems  with temp table scope so went this route after some google searching. The single insert seems a lot more efficient and works well.

Update 2:

Added a run counter in my script. Easier to track the specific runs. I started off by using the time stamp but soon realized that the timestamp resolves down to the millisecond and at times  the query of sysprocesses spanned a few milliseconds. Made it tough to group the captures so now I just use the runid column.

What to do with the info? Some sample queries:

–How many users connected, avg CPU and Diskio based on each capture
select count(spid) ConnectedUsers, avg(cpu) AvgCPU,avg(physical_io) AVGDiskIO, capturetime from sysprocessTracker where servername = ‘SERVER1’ group by servername, capturetime

–Which command is executed most often, which db is used the most, who are the users logged in, what programs are used the most, etc.
select avg(spid) ConnectedUsers, avg(cpu) AvgCPU,avg(physical_io) AVGDiskIO from sysprocessTracker where servername = ‘SERVER1’ group by servername
select count(cmd), cmd from sysprocessTracker where servername = ‘SERVER1’ group by cmd
select count(dbid), dbid from sysprocessTracker where servername = ‘SERVER1’ group by dbid
select count(nt_username),nt_username,capturetime from sysprocessTracker where servername = ‘SERVER1’ group by capturetime,nt_username
select count(program_name),program_name,capturetime from sysprocessTracker where servername = ‘SERVER1’ group by capturetime,program_name

Advertisements

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