Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

CPU Usage using T-SQL Expand / Collapse
Author
Message
Posted Monday, September 6, 2010 1:20 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 26, 2011 8:46 AM
Points: 197, Visits: 1,274
MarkusB (12/1/2008)
What exactly are you looking for?
The CPU usage by SQL server in general or the CPU usage for a query?

You can find some information about the general CPU usage in sys.dm_os_ring_buffers. But be aware that you only get info about the last 4 hours or so.
Here's an example.
	DECLARE @ts_now BIGINT
SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_info

SELECT record_id,
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
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 '% %') AS x
) AS y
ORDER BY record_id DESC

For CPU usage by query you can query sys.dm_exec_query_stats. But again you will only get info about query plans which are in cache.

SELECT TOP 10 (total_worker_time * 1.0) / 1000000 AS CPU_Utilized_in_Seconds, text,* 
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)




Is there any script for SQL 2000 as above?


Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
Post #980921
Posted Tuesday, September 7, 2010 3:11 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 4:31 AM
Points: 93, Visits: 169
MarkusB , Thanks a lot for your query.
Post #981935
Posted Monday, December 3, 2012 4:04 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:04 PM
Points: 126, Visits: 500
Lost and have been chasing this CPU query down for several days THANKS!!!


Post #1392175
Posted Tuesday, April 23, 2013 9:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 10, 2014 7:48 AM
Points: 77, Visits: 90
Excellent script Markus!!! Huge help with what I was wanting to do.

I made some minor tweaks to it to make it compatible with SQL 2008. I thought I'd post it back to save the next guy a few minutes. Again, thanks Markus for posting this script.

/***************************************************************************************************************
Description: This script will display CPU usage on the server. The data comes from
dm_os_ring_buffers which only stored data for the past 4 HOURS. Within the ring buffers, data is averaged
at the minute level. There are variables limit the results to a smaller time window and for hiding the
details.

Sources:
http://www.sqlservercentral.com/Forums/Topic611107-146-2.aspx
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/abbf67ab-fc8b-4567-b6d4-1c605bc9866c/
***************************************************************************************************************/

/* Variables */
DECLARE
@StartTime DATETIME = '01/01/1900 00:00:00'
,@EndTime DATETIME = '01/01/2100 23:59:59'
,@ShowDetails BIT = 1 -- 1 = True, 0 = False




/* Script Begin */
/* Be careful modifying anything below */


-- Find the timestamp for current server time
DECLARE @ts_now BIGINT
SELECT @ts_now = cpu_ticks / (cpu_ticks / ms_ticks)
FROM sys.dm_os_sys_info;

DECLARE @Results TABLE
(record_ID BIGINT NOT NULL
,EventTime datetime NOT NULL
,SQLProcessUtilization tinyint NOT NULL
,SystemIdle tinyint NOT NULL
,OtherProcessUtilization tinyint NOT NULL
)

INSERT INTO
@Results
(
record_ID
,EventTime
,SQLProcessUtilization
,SystemIdle
,OtherProcessUtilization
)
SELECT
record_id
,DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime
,SQLProcessUtilization
,SystemIdle
,100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
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 '% %'
AND DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) BETWEEN @StartTime AND @EndTime
) AS x
) AS y


--Return details
IF @ShowDetails = 1
BEGIN
SELECT
record_ID
,EventTime
,SQLProcessUtilization
,SystemIdle
,OtherProcessUtilization
FROM @Results
END


--Return average
SELECT
AVG(SQLProcessUtilization)
,MIN(EVENTTIME) StartTime
,MAX(EVENTTIME) EndTime
FROM
@Results





Post #1445522
Posted Wednesday, April 24, 2013 2:44 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:13 AM
Points: 4,432, Visits: 4,167
Hi Jason,

nice work.


Markus Bohse
Post #1445806
Posted Friday, June 28, 2013 10:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 2:26 PM
Points: 3, Visits: 220
SQL 2008...

declare @a as bigint, @b as bigint

select @a=sum(cntr_value)
from sys.dm_os_performance_counters
where object_name = 'SQLServer:Resource Pool Stats' and cntr_type = 537003264

select distinct @b=cntr_value
from sys.dm_os_performance_counters
where object_name = 'SQLServer:Resource Pool Stats' and cntr_type = 1073939712

select cast(@a as float) / cast(@b as float)*100 as [cpu]
go
Post #1468622
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse