SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Detecting Performance Issues With Sysprocesses


Detecting Performance Issues With Sysprocesses

Author
Message
Terry Troisi
Terry Troisi
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 143
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tTroisi/detectingperformanceissueswithsysprocesses.asp
webtekkie
webtekkie
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 362

Here is a script I wrote which uses Terry's technique, and expands on it. It gets all the sysprocesses into a temp table, then waits for (default) five seconds. It then checks sysprocesses again and calculates the change in the cpu column. It returns the results, in order of the cpu usage descending (the process that uses the most clock cycles is at the top of the list) and then, using a cursor it loops through the top few results running dbcc inputbuffer against them and returns the output.

Put this in the master database, and run when required.

Merry Christmas everybody,

Martin

CREATE proc sp_CPUByProcess
@TimePeriod varchar(8) = '00:00:05'
as
set nocount on
--drop table #sysprocessUsage
create table #sysprocessUsage (cpu int, spid smallint, cpuIncrease int)
set nocount on
insert into #sysprocessUsage (cpu, spid)
select
cpu,
spid
from
master..sysprocesses
waitfor delay @TimePeriod
update
#sysprocessUsage
set
cpuIncrease = sp.cpu - spu.cpu
from
master..sysprocesses sp
inner join #sysprocessUsage spu
on sp.spid = spu.spid
select
spu.cpuIncrease,
sp.*
from
master..sysprocesses sp
inner join #sysprocessUsage spu
on sp.spid = spu.spid
where
spu.cpuIncrease > 0
order by
sp.cpu - spu.cpu desc
compute
sum(cpuIncrease)
declare @spid int, @cpuIncrease int
declare @qry nvarchar(50)
declare c cursor for
select cpuIncrease, spid from #sysprocessUsage where cpuIncrease > 0 and spid <> 0 order by cpuIncrease desc
for read only
open c
fetch next from c into @cpuIncrease, @spid
while @@fetch_status = 0
begin
 set nocount on
exec ('print ''DBCC INPUTBUFFER FOR SPID ' + @spid + '''')
 select @qry = 'dbcc inputbuffer(' +  rtrim(convert(char(5),@spid)) + ')'
exec( @qry )
 fetch next from c into @cpuIncrease, @spid
end
close c
deallocate c




Robert Sterbal
Robert Sterbal
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 1

This script is handy. Thanks for posting it.

- Robert


John Langston
John Langston
Right there with Babe
Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)

Group: General Forum Members
Points: 750 Visits: 526

I have had folks come to me in a panic, but on more than one occasion, when I have tried to run a SELECT from sysprocesses in a new QA session, find that my query doesn't run immediately as has been described in the article.

Attempts to connect via EM also hang. Has anyone else observed this behavior?

John L.





Brian Munier
Brian Munier
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 80

Yes, I have had that problem. In fact Enterprise Manager will occasionally timeout and fail. I have to guess that it is contention and locking preventing the select.

I'll bet that the query hint NOLOCK would help see the following:

select * from master.dbo.sysprocesses WITH (NOLOCK)

select * from master.dbo.sysprocesses WITH (NOLOCK)
where status = 'runnable'
order by CPU
desc


Terry Troisi
Terry Troisi
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 143

I like your script grasshopper - you have learned well at the masters feet!

There is a product that uses an enhanced version of this that's pretty slick that I use at work. It can be used to view the most costly queries based on cpu, duration, i/o. etc. It basically uses this logic to pull from sysprocesses and record to another DB.

It can be found at www.highwiredev.com


hot2use
hot2use
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 71

Hi there

Ummm, I picked up on one statement and created a sproc for the master database. It returns all the basic information for locked and blocked processes in the sysprocesses table OR buffer information for a specific SPID.

CREATE PROCEDURE [dbo].[spchecklocks]
-- ==================================================================
-- Author......: John Ness / Bühler AG / Uzwil / Switzerland
-- Date........: 01-Aug-2005
-- Version.....: 1.1
-- Server......: UZN487
-- Database....: master
-- Name........: spchecklocks
-- Owner.......: dbo
-- Table.......:
-- Type........: Stored Procedure
-- Description.: Grabs all the possible information for locked,
-- blocked and waiting processes or for a specific
-- SPID
-- The @loginname and @srvname parameters are unused
--
-- History.....: 01-Aug-2005 1.0 JN First created
-- 07-Dec-2005 1.1 JN Modified seperators
--
-- Editor......: UltraEdit 11.10a (using Syntax Highlighting)
-- Tabstop Values = 4
-- ==================================================================
 --input variables
@spid int = null,
@loginname varchar(50) = '',
@srvname varchar(20) = ''
AS

begin
/* Turn off double quotes for text strings */
set quoted_identifier off

/* Dont return the count for any statment */
set nocount on

/* Declare variables used only in sproc */
declare @sqlh binary(20)
declare @sqlstmt nvarchar(1000)

/* Add a point to the server name */
if @srvname <> ''
begin
set @srvname = @srvname + '.'
end

/* Display all currently locked processes */
print '==================================================='
print ' Currently Locked Processes'
print '==================================================='
set @sqlstmt = 'select * from ' + @srvname +
'master..sysprocesses where spid in (select req_spid from ' + @srvname +
'master..syslockinfo where req_spid in (select spid from ' + @srvname +
'master..syslocks )) and (open_tran = 1 or blocked != 0 or waittype != 0x0000)'
exec sp_executesql @sqlstmt

/* If @spid was supplied list various input and output buffers */
if @spid <> '' and @spid is not null
begin

print '==================================================='
print ' Summary for the following SPID : ' + cast(@spid as varchar(10))
print '==================================================='
print ' '
print ' '

print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
print ' Input Buffer'
print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
set @sqlstmt = 'dbcc inputbuffer (' + cast(@spid as varchar(10)) + ')'
exec sp_executesql @sqlstmt
print ' '
print ' '

print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
print ' Handles for Input Buffer'
print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
create table #temp_jtprochandle (
sql_handle binary(20))
-- The following statement is on one line

set @sqlstmt = 'insert into #temp_jtprochandle (sql_handle) select sql_handle from master..sysprocesses where spid = ' + cast(@spid as varchar(10)) + ''
-- The one-liner ends here

exec sp_executesql @sqlstmt
select @sqlh = sql_handle from #temp_jtprochandle
select text from ::fn_get_sql(@sqlh)
drop table #temp_jtprochandle
print ' '
print ' '

print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
print ' Output Buffer'
print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
set @sqlstmt = 'dbcc outputbuffer (' + cast(@spid as varchar(10)) + ')'
exec sp_executesql @sqlstmt
print ' '
print ' '

print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
print ' SP_Who'
print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
set @sqlstmt = 'sp_who ' + cast(@spid as varchar(10)) + ''
exec sp_executesql @sqlstmt
print ' '
print ' '

print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
print ' SP_Who2'
print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
set @sqlstmt = 'sp_who2 ' + cast(@spid as varchar(10)) + ''
exec sp_executesql @sqlstmt
print ' '
print ' '

print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
print ' Lock Info'
print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
set @sqlstmt = 'sp_lock ' + cast(@spid as varchar(10)) + ''
exec sp_executesql @sqlstmt
print ' '
print ' '

end

print '==================================================='
print ' End of Output'
print '==================================================='

set quoted_identifier on
set nocount off

end
GO

I'm still working on it and hope to be able to add server and login specific information. I created it because of a problem with an application that kept locking up on me. It helped the software developer pinpoint their issue(s).

hot2use




__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.

DBA-640728
DBA-640728
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6382 Visits: 2005
does the status always have to be 'runnable'? i have seen some processes with high CPU as 'suspended', could these processes also cause performance issues?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search