|
|
Posted by SQLJunkie on 01/03/07 17:06
Thanks everyone for reading this and your responses. I was able to find
the correct solution in another Google post:
DECLARE
@CPU_BUSY int
, @IDLE int
SELECT
@CPU_BUSY = @@CPU_BUSY
, @IDLE = @@IDLE
WAITFOR DELAY '000:00:01'
SELECT
(@@CPU_BUSY - @CPU_BUSY)/((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) *
1.00) *100 AS CPUBusyPct
This solution was posted by Gert-Jan Strik on Tues, Jan 14 2003 6:55
PM.
Here is the URL for the thread:
http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/a6bc2eb3cee9c18d/cf67728586773e8b?lnk=st&q=%40%40CPU_BUSY&rnum=8&hl=en#cf67728586773e8b
Thanks again,
Vishal
SQLJunkie wrote:
> Thanks for your quick response Erland. I ran the following script but I
> don't think this is the correct value. But I cannot find anything
> meaningful???
>
> DECLARE
> @CPUBusy1 bigint
> , @CPUBusy2 bigint
> , @TimeTicks1 bigint
> , @TimeTicks2 bigint
>
> SELECT
> @CPUBusy1 = @@CPU_BUSY
> , @TimeTicks1 = @@TIMETICKS
>
> WAITFOR DELAY '0:00:01'
>
> SELECT
> @CPUBusy2 = @@CPU_BUSY
> , @TimeTicks2 = @@TIMETICKS
>
> SELECT
> @CPUBusy1 AS CPUBusy1
> , @CPUBusy2 AS CPUBusy2
> , @CPUBusy2 - @CPUBusy1 AS CPUDiff
> , @TimeTicks1 AS TimeTicks1
> , @TimeTicks2 AS TimeTicks2
> , @TimeTicks2 - @TimeTicks1 AS TimeTicksDiff
>
> Thanks for your time and help!
>
>
> Vishal
>
>
> Erland Sommarskog wrote:
> > SQLJunkie (vsinha73@gmail.com) writes:
> > > I have spent a good amount of time scouring google groups but this is
> > > all I have found:
> > > SELECT
> > > (CAST(@@CPU_BUSY AS float)
> > > * @@TIMETICKS
> > > / 10000.00
> > > / CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
> > > CPUBusyPct
> > > FROM
> > > master..SysProcesses AS SP2
> > > WHERE
> > > SP2.Cmd = 'LAZY WRITER'
> > >
> > > Problem is this gives me total amount of time CPU in %) has been busy
> > > since the server last started. What I want is the % for the instant -
> > > the same number we see in Task Manager and PerfMon.
> >
> > Performance counters are in sysperfinfo on SQL 2000 and
> > sys.dm_os_performance_counters on SQL 2005, but I could find the item
> > you are looking for in these views.
> >
> > But I saw in Books Online for SQL 2005 that these values are cumultative. To
> > get the present value, sample with some interval. I guess you could to
> > the same: query @@CPU_BUSY twice with a second or so in between.
> >
> >
> >
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> >
> > Books Online for SQL Server 2005 at
> > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> > Books Online for SQL Server 2000 at
> > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|