CPU History in SQL Server 2008 R2

How to query last 2 months CPU history using T-SQL in SQL Server 2008 R2?
VijayAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Do you have anything Monitioring setup already?
Please try this query if it helps-

declare @ts_now bigint 
select @ts_now = ms_ticks from
sys.dm_os_sys_info
select record_id,dateadd (ms, (y.[timestamp] -@ts_now), CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 101)) 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 '%<SystemHealth>%') as x
) as y
order by record_id desc 

Open in new window

From-https://www.sqlservercentral.com/Forums/Topic1417194-1550-1.aspx
0
 
David Johnson, CD, MVPOwnerCommented:
Unless you've setup this monitoring previously then there is no way to query for items in the past
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The counters are kept only from the last time SQL Server service restarted.
It's your SQL Server instance working the last 2 months with suffering a restart?
0
 
VijayAuthor Commented:
Good information.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.