May 31, 2008 at 5:20 pm
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 and see what is the CPU Utilization at that instant.But I want is the % for the instant -
the same number see in Task Manager and PerfMon
June 1, 2008 at 1:41 am
Please share your inputs if you are able to analyse DBCC PERFMON.
What exactly we need to check. What are the values which shouldn't be high etc? Thanks.
M&M
June 1, 2008 at 3:58 am
i dont know of a way to do this directly through T-SQL, im sure there is a way tho! One way you could do this is set up a counter log that uses this counter and stores the results in a sql table, then you can query that table to get the latest counter out? would that do?
June 1, 2008 at 12:22 pm
Download SQL Server 2005 Performance Dashboard reports. The main report has a chart that shows CPU utilization.
If you really want to run a SQL statement that does this, then you can review how the chart is setup and the code it uses to determine the CPU utilization.
Note: this report uses an un-documented dynamic management view (sys.dm_os_ring_buffers). Here is a modified version of that code:
declare @ts_now bigint;
select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info;
with SystemHealth (ts_record, record)
as (select timestamp, convert(xml, record) as record
from sys.dm_os_ring_buffers
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '% %')
,ProcessRecord (record_id, SystemIdle, SQLProcessUtilization, ts_record)
as (select
record.value('(./Record/@id)[1]', 'int') as record_id
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization
,ts_record
from SystemHealth)
select top 15
record_id
,dateadd(ms, -1 * (@ts_now - ts_record), GetDate()) as EventTime
,SQLProcessUtilization
,SystemIdle
,100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
from ProcessRecord
order by record_id desc
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 2, 2008 at 1:17 am
I ran the script on my local sql 2005. Could you please explain briefly what this means. Is there anyway we could map these id's to the processes from sp_who2
record_id EventTime SQLProcessUtilization SystemIdle OtherProcessUtilization
----------- ----------------------- --------------------- ----------- -----------------------
34 2008-06-02 12:43:59.073 0 89 11
33 2008-06-02 12:42:59.050 0 90 10
32 2008-06-02 12:41:59.017 0 94 6
31 2008-06-02 12:40:58.997 0 90 10
30 2008-06-02 12:39:58.977 0 91 9
29 2008-06-02 12:38:58.953 0 91 9
28 2008-06-02 12:37:58.933 0 91 9
27 2008-06-02 12:36:58.903 0 90 10
26 2008-06-02 12:35:58.883 0 93 7
25 2008-06-02 12:34:58.863 0 92 8
24 2008-06-02 12:33:58.840 0 94 6
23 2008-06-02 12:32:58.817 0 92 8
22 2008-06-02 12:31:58.797 4 87 9
21 2008-06-02 12:30:58.773 0 87 13
20 2008-06-02 12:29:58.753 0 82 18
(15 row(s) affected)
M&M
June 2, 2008 at 1:33 am
from the look of Jeffs post it looks like the CPU usage is taken in snapshots, and they are the results you are seeing in the table. the ID's will not match to anything in sp_who2. you are looking at the CPU usage, at different points in time. thats my understanding of it anyway
June 2, 2008 at 10:04 pm
Animal Magic (6/2/2008)
from the look of Jeffs post it looks like the CPU usage is taken in snapshots, and they are the results you are seeing in the table. the ID's will not match to anything in sp_who2. you are looking at the CPU usage, at different points in time. thats my understanding of it anyway
I think you are correct - but, again - this is an undocumented dynamic view and the only thing I have seen about it states it is for internal use only. So, use it at your own discretion.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 3, 2008 at 8:20 am
a simple alternative might be
exec sp_monitor (from master database)
this will (without too much accuracy) give you % cpu utilisation between executes of the proc (amongst other things)
the first time you run it though you'll get an error as there's nothing to compare your values to
not accurate, but it is very simple
MVDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply