February 9, 2012 at 5:47 am
Hi There,
I need an SQL that could get the current CPU usage in SQL 2005, in 2008 it's easy but i can't get it in 2005.
Thanks in advance
Nader
February 9, 2012 at 9:27 am
What do you mean with "CPU usage"?
How do you get that in 2008?
-- Gianluca Sartori
February 10, 2012 at 6:53 am
Are you using dynamic management objects from 2008? Most of them are in 2005, so you can just reuse them.
SELECT * FROM sys.dm_os_performance_counters ;
for example, works in 2005.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2012 at 2:22 pm
Hi There,
I use the following sql in SQL2008
SELECT
(cast(CAST (A.cntr_value1 AS NUMERIC) / CAST (B.cntr_value2 AS NUMERIC) as decimal(18,3)))*100
FROM
(
SELECT cntr_value AS cntr_value1
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Resource Pool Stats'
and counter_name = 'CPU usage %'
) AS A
,
(
SELECT cntr_value AS cntr_value2
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Resource Pool Stats'
and counter_name = 'CPU usage % base'
) AS B
when i tried using it on 2005 , i didn't find those objects and counter names.
Thanks for your reply
Nader
February 14, 2012 at 2:51 am
This will work for 2005:
DECLARE @ts_now bigint;
SELECT @ts_now = cpu_ticks / CONVERT(float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info
SELECT TOP(10) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
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], [timestamp]
FROM (
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 '%<SystemHealth>%') AS x
) AS y
ORDER BY record_id DESC;
February 14, 2012 at 2:53 am
Thanks for your help.
i will try that.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply