3

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.

1
  • What procedures available are you referring to?
    – user507
    Commented Nov 16, 2015 at 6:16

1 Answer 1

2

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 '%<SystemHealth>%') 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

1
  • 1
    +1 for,these queries are tested on from SQL Server 2008 R2 to 2014. I have not tested them on 2008 or 2005. Commented Nov 16, 2015 at 11:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.