February 27, 2003 at 7:44 am
Can someone tell me how I to interpret the CPU figures that are in the sysprocesses table and sp_who2 results? I assume that the higher the number, the more CPU that process is using. But is there a way to translate it in to some percentage figure as we see in perfmon?
Thanks very much in advance.
March 3, 2003 at 12:00 pm
This was removed by the editor as SPAM
May 15, 2003 at 1:33 am
Maybe if you compared cpu from sysprocesses with before&after values of the global variables @@idle and @@cpu_busy (in millisecs) you might figure it out.
EG if you can get a quiet box, select @@cpu_busy before the process, select cpu, @@cpu_busy from sysprocesses after the process, then
@@cpu_busy2 - @@cpu_busy1 = sysprocesses.cpu x (something)
May 15, 2003 at 7:45 pm
@@CPU_BUSY + @@IO_BUSY + @@IDLE represents the total number of CPU ticks. You can fairly accurately represent a CPU busy percentage as:
(CPU_BUSY * 100) / (CPU_BUSY + IO_BUSY + IDLE + 1)
The + 1 prevents division by zero errors. Also note that you'll need to implement that formula properly, as I wanted to show the formula as simply as possible.
As a side note, the information in sp_monitor is affected by the number of processors in the system. If it is a quad processor box, you may see 396% idle (on a very lightly loaded machine). On a dual proc box, it may be 198% idle. The max percentage is (num_procs * 100) for cpu, i/o, and idle, however their sum will add up to (num_procs * 100) or very, very near it.
David R Buckingham, MCDBA,MCSA,MCP
May 16, 2003 at 4:03 am
Thank you very much for your replies. I will try your suggestions.
May 16, 2003 at 4:08 am
Hi,
here's a stored procedure I use to collect Server Statistics. It's a modification of something I found somewhere else
CREATE procedure dbo.pactldbmonitor
as
declare @last_rundatetime
declare @now datetime
declare @cpu_busy int
declare @io_busyint
declare @idleint
declare @pack_receivedint
declare @pack_sentint
declare @pack_errorsint
declare @connectionsint
declare @total_readint
declare @total_writeint
declare @total_errorsint
declare @oldcpu_busy int
declare @intervalint
declare @mspertickint
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'pactldbmonitor')
return (1)
end
select @mspertick = convert(int, @@timeticks / 1000.0)
select
@now = getdate(),
@cpu_busy = @@cpu_busy,
@io_busy = @@io_busy,
@idle = @@idle,
@pack_received = @@pack_received,
@pack_sent = @@pack_sent,
@connections = @@connections,
@pack_errors = @@packet_errors,
@total_read = @@total_read,
@total_write = @@total_write,
@total_errors = @@total_errors
select
@oldcpu_busy = cpu_busy, @last_run = sampletime
FROM
svrmonitor
WHERE
id=(SELECT MAX(id) FROM svrmonitor)
begin
insert into svrmonitor
(sampletime,
lastrun,
cpu_busy,
io_busy,
idle,
pack_received,
pack_sent,
connections,
pack_errors,
total_read,
total_write,
total_errors
)
values
(
@now,
@last_run,
@cpu_busy,
@io_busy,
@idle,
@pack_received,
@pack_sent,
@connections,
@pack_errors,
@total_read,
@total_write,
@total_errors
)
end
return (0)
GO
This one runs within a job once a day. Maybe it helps.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 16, 2003 at 4:59 am
I just received something that might be worth taking a look at
Category: Stored Procedures
Level: Intermediate
Description: Display information similar to sp_who2, but has the same columns as the process manager in Enterprise manager. It also displays the command being executed
Complete source code is at:
http://www.planet-source-code.com/vb/default.asp?lngCId=676&lngWId=5
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 16, 2003 at 10:17 am
DrBuckingham,
Please tel me if this is what you had in mind. I tried the following:
set nocount on
declare @cpu_busy int
declare @IO_BUSY int
declare @IDLE int
declare @percent decimal
set @cpu_busy = (select @@CPU_BUSY)
set @IO_BUSY = (select @@IO_BUSY)
set @IDLE = (select @@IDLE)
select @cpu_busy
select @IO_BUSY
select @IDLE
set @percent = (select (@CPU_BUSY * 100)/(@CPU_BUSY + @IO_BUSY + @IDLE + 1))
select @percent
But I'm getting a result of 0. I know that's not correct.
Thank you very much.
May 19, 2003 at 1:37 am
Since I'm currently investigating where time is spend in SQLServer (I'm used to the abundance of timing and wait event info in Oracle..) I found the @@CPU_TIME is *not* as the manual says: 'time spend in milliseconds since (SQL) startup' But more like cpu time in ticks as in @@TIMETICKS, since startup.
This at least comes close to the time spend according to the OS in the sqlserver-threads, and to the sum(cpu) of sysprocesses.
Be also aware of the fact that if you logoff your session that your data has gone from sysprocesses, and the sum(cpu) doesn't compare anymore to total_cpu spend.
I'll do some research on the other two (IO_BUSY and IDLE) but I guess it's also in ticks, not in milliseconds..
good luck,
Mario
May 19, 2003 at 1:57 am
quote:
set nocount ondeclare @cpu_busy int
declare @IO_BUSY int
declare @IDLE int
declare @percent decimal
But I'm getting a result of 0. I know that's not correct.
Declare variables as float and you get a result<>0
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 19, 2003 at 10:28 pm
As a5xo3z1 suggested, use float instead of int or use CAST. Personally, I'd use floats like the following:
SET NOCOUNT ON
DECLARE @CPU_Busy AS float,
@IO_Busy AS float,
@Idle AS float,
@Percent AS decimal (6,2)
SELECT @CPU_Busy = @@CPU_BUSY,
@IO_Busy = @@IO_BUSY,
@IDLE = @@IDLE,
@Percent = (@CPU_Busy * 100) / (@CPU_Busy + @IO_Busy + @Idle + 1)
SELECT @CPU_Busy AS 'CPU Ticks',
@IO_Busy AS 'IO Ticks',
@Idle AS 'Idle Ticks',
@Percent AS 'Percent Busy'
SET NOCOUNT OFF
The following demonstrates the use of CAST in a single statement:
SELECT @@CPU_BUSY AS 'CPU Ticks', @@IO_BUSY AS 'IO Ticks', @@IDLE AS 'Idle Ticks',
CAST((CAST(@@CPU_BUSY AS float) * 100) / (@@CPU_BUSY+@@IO_BUSY+@@IDLE+1) AS decimal(6,2)) AS 'Percent Busy'
David R Buckingham, MCDBA,MCSA,MCP
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply