Capture CPU Utilization in TSQL

[SQLJunkie]

Happy New Year everyone!

I would like to capture CPU Utilization % using TSQL. I know this can
be done using PerfMon but I would like to run TSQL command (maybe once
every 5 minutes) and see what is the CPU Utilization at that instant so
that I can insert the value in a table and run reports based on the
data.

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.

Any help would be appreciated.

Thanks

[Erland Sommarskog]

SQLJunkie (vs******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[SQLJunkie]

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 (vs******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[SQLJunkie]

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....67728586773e8b

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 (vs******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx