SQLServerWiki

“The Only Thing That Is Constant Is Change”

Simple technique to collect sysprocesses data.

Posted by database-wiki on April 11, 2016

Problem Description:

We needed to collect sysprocesses (3 times) at about 5 minute interval to compare
CPU, IO, Blocking by program_name to determine if Replication Agents were being
blocked.

For example, if CPU/IO time on Publisher/Distributor was Low, but High on
Subscriber, Subscriber was working. If Subscriber had High CPU, but low IO, it may
be blocked or stuck in a loop.

Resolution:

–****************
–Data Collection
–****************

–Create scratch database to hold Sysprocesses data
— on both the Publisher/Distributor and the Subscriber
Create Database sysprocesseslog
go

–Creates Sysprocesses tracking Table
select getdate() as “RunDateTime”,* into sysprocesseslog.dbo.SysprocessesTracking
from master..sysprocesses

–At 5 or 10 minute interval
–Insert appends new stats to Sysprocesses tracking table

DECLARE @iCounter int
DECLARE @numRuns int

— Determine how many loops based on wait time
SET @iCounter = 1
SET @numRuns = 4

WHILE @iCounter < @numRuns
BEGIN
SET @iCounter = @iCounter + 1
Insert sysprocessesRUN.dbo.SysprocessesTracking
select getdate() as “RunDateTime”,*
from master..sysprocesses
WAITFOR DELAY ’00:05:00′ — wait 5 mins before continuing
END

–****************
–Analysis
–****************

–Look at the run times for each Agent
–Return all rows from SysprocessesTracking table
Select * from sysprocesseslog.dbo.SysprocessesTracking
order by program_name desc, RunDateTime desc

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: