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.
ThanksSQLJunkie (vsinha73@.gmail.com) writes:
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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:
Quote:
Originally Posted by
SQLJunkie (vsinha73@.gmail.com) writes:
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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...f67728586773e8b
Thanks again,
Vishal
SQLJunkie wrote:
Quote:
Originally Posted by
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:
Quote:
Originally Posted by
SQLJunkie (vsinha73@.gmail.com) writes:
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
