snippetsqlMinor

How to get SQL Server CPU and Memory read and write usage within two weeks?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

readsqlandwritewithinusagegettwoweeksmemory

Problem

Is there any a quick way or quick script on how to get the CPU and Memory usage within a time range (at least two weeks)? I did my research earlier and it seems that the procedures available seems complicated. Any help will be appreciated.

Solution

You can query sys.dm_os_ring_buffers DMV to extract information from it. Please note sys.dm_os_ring_buffers is Undocumented Command but safe to use.

NOTE: These queries are tested on from SQL Server 2008 R2 to 2014. I have not tested them on 2008 or 2005.

To get information about SQL Server memory usage you need to query sys.dm_os_ring_buffers and filter out for "RING_BUFFER_RESOURCE_MONITOR". Below is the sample query. If the query only produces results for current date and you want for previous date you can use GETDATE() -1 instead of GETDATE() in the query

;with RingBufferMemoryMonitoring
as 
(SELECT 

 x.value('(//Record/MemoryRecord/MemoryUtilization)[1]', 
       'bigint') 
       AS Memory_utilization_Percentage ,
       x.value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 
       'bigint') as [TotalPhysicalMemory_KB] ,
       x.value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 
       'bigint') as [AvailPhysicalMemory_KB] ,    
x.value('(//Record/@time)[1]', 'bigint')                                     
 as [Record_Time] 
FROM  (SELECT Cast (record AS XML) 
 FROM   sys.dm_os_ring_buffers 
 WHERE  ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS R(x)
 ) 
 select RingBufferMemoryMonitoring.*, Dateadd (ms, RingBufferMemoryMonitoring.[record_time] - SI.ms_ticks, Getdate()) AS 
       Notification_time
      from  RingBufferMemoryMonitoring
 cross join sys.dm_os_sys_info SI

Below is another query,I took the query from This Blog, to get CPU utilization history. If you want for previous date you can use GETDATE() -1 instead of GETDATE() in the query

DECLARE @ts_now bigint 

SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info 

SELECT top 20 record_id, EventTime,  

  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization,  

  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization 

FROM  

( 

  SELECT  

    record.value('(Record/@id)[1]', 'int') AS record_id, 

    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime, 

    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2, 

    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 ,  

    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2, 

    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2 

  FROM ( 

    SELECT timestamp, CONVERT (xml, record) AS record  

    FROM sys.dm_os_ring_buffers  

    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR' 

      AND record LIKE '%%') AS t 

) AS t 

ORDER BY record_id desc

If you want to read more about how to extract information from Sys.dm_os_ring_buffers please read Inside Sys.dm_os_ring_Buffers

Code Snippets

;with RingBufferMemoryMonitoring
as 
(SELECT 

 x.value('(//Record/MemoryRecord/MemoryUtilization)[1]', 
       'bigint') 
       AS Memory_utilization_Percentage ,
       x.value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 
       'bigint') as [TotalPhysicalMemory_KB] ,
       x.value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 
       'bigint') as [AvailPhysicalMemory_KB] ,    
x.value('(//Record/@time)[1]', 'bigint')                                     
 as [Record_Time] 
FROM  (SELECT Cast (record AS XML) 
 FROM   sys.dm_os_ring_buffers 
 WHERE  ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS R(x)
 ) 
 select RingBufferMemoryMonitoring.*, Dateadd (ms, RingBufferMemoryMonitoring.[record_time] - SI.ms_ticks, Getdate()) AS 
       Notification_time
      from  RingBufferMemoryMonitoring
 cross join sys.dm_os_sys_info SI

DECLARE @ts_now bigint 

SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info 

SELECT top 20 record_id, EventTime,  

  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization,  

  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization 

FROM  

( 

  SELECT  

    record.value('(Record/@id)[1]', 'int') AS record_id, 

    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime, 

    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2, 

    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 ,  

    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2, 

    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2 

  FROM ( 

    SELECT timestamp, CONVERT (xml, record) AS record  

    FROM sys.dm_os_ring_buffers  

    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR' 

      AND record LIKE '%<SystemHealth>%') AS t 

) AS t 

ORDER BY record_id desc

Context

StackExchange Database Administrators Q#121177, answer score: 2

Revisions (0)

No revisions yet.