SQLServerCentral / Discuss Content Posted by Terry Troisi / Article Discussions / Article Discussions by Author / Detecting Performance Issues With Sysprocesses / Latest Posts

RE: Detecting Performance Issues With Sysprocesses

DBA-640728 — Fri, 12 Feb 2010 22:55:22 GMT

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?

RE: Detecting Performance Issues With Sysprocesses

hot2use — Tue, 03 Jan 2006 02:51:00 GMT

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 endGO

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

RE: Detecting Performance Issues With Sysprocesses

Terry Troisi — Thu, 29 Dec 2005 14:36:00 GMT

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

RE: Detecting Performance Issues With Sysprocesses

Brian Munier — Thu, 29 Dec 2005 08:32:00 GMT

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 CPUdesc

RE: Detecting Performance Issues With Sysprocesses

John Langston — Tue, 27 Dec 2005 05:24:00 GMT

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.

RE: Detecting Performance Issues With Sysprocesses

Robert Sterbal — Mon, 26 Dec 2005 08:38:00 GMT

This script is handy. Thanks for posting it.

- Robert

RE: Detecting Performance Issues With Sysprocesses

webtekkie — Mon, 26 Dec 2005 05:09:00 GMT

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 oninsert into #sysprocessUsage (cpu, spid)select  cpu, spidfrom master..sysprocesses

waitfor delay @TimePeriod

update #sysprocessUsageset cpuIncrease = sp.cpu - spu.cpufrom 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.spidwhere spu.cpuIncrease > 0order by sp.cpu - spu.cpu desccompute sum(cpuIncrease)

declare @spid int, @cpuIncrease intdeclare @qry nvarchar(50)

declare c cursor for select cpuIncrease, spid from #sysprocessUsage where cpuIncrease > 0 and spid <> 0 order by cpuIncrease descfor read onlyopen c

fetch next from c into @cpuIncrease, @spidwhile @@fetch_status = 0begin

 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, @spidend

close cdeallocate c

Detecting Performance Issues With Sysprocesses

Terry Troisi — Wed, 30 Nov 2005 15:07:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tTroisi/detectingperformanceissueswithsysprocesses.asp