SQL Server Ring Buffer--Ring_buffer_scheduler_monitor get SQL

SQL Server Ring buffer--Ring_buffer_scheduler_monitor Gets the CPU utilization of the SQL process

The ring buffer stores information about the CPU utilization. This information is updated every minute. So you can track the CPU utilization at a given point in time in 4 hours and 15 minutes. The following output shows the CPU utilization of the SQL instance and the CPU utilization of other active processes. This will help us to analyze whether the SQL Server process consumes a large amount of CPU.

650) this.width=650; "title=" clip_image001 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image001 "src=" http://s3.51cto.com/wyfs02/M00/54/1A/wKiom1R4ES2wosplAAGfFYYma6Y106.jpg "height=" 226 "border=" 0 "/>

For SQL Server 2005:

declare  @ts_now  bigintselect  @ts_now  = cpu_ticks / convert (float, cpu_ Ticks_in_ms)  from sys.dm_os_sys_infoselect record_id,dateadd (ms, -1 *  (@ts_now  - [timestamp]),  getdate ())  as eventtime,sqlprocessutilization,systemidle,100 -  SystemIdle - SQLProcessUtilization as OtherProcessUtilizationfrom  ( Selectrecord.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,timestampfrom  (Select timestamp, convert (Xml, record)  as  Recordfrom sys.dm_os_ring_bufferswhere ring_buffer_type = n ' RING_BUFFER_SCHEDULER_MONITOR ' and record like  '%<systemhealth>% ') &nbsP;AS&NBSP;X)  as yorder by record_id desc 

For SQL Server 2008:

declare  @ts_now  bigintselect  @ts_now  = ms_ticks fromsys.dm_os_sys_infoselect  record_id, dateadd  (ms,  (Y.[timestamp] [email protected]_now),  getdate ())  as EventTime,SQLProcessUtilization,SystemIdle,100 - SystemIdle -  sqlprocessutilization as otherprocessutilizationfrom  (Selectrecord.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,timestampfrom  (Select timestamp, convert (XML,  record)  as recordfrom sys.dm_os_ring_bufferswhere ring_buffer_type = n ' Ring_buffer_scheduler_monitor ' and record like  '%<systemhealth>% ')  as x)  as  yorder by record_id&nBsp;desc 

Common SQL Scripts:

Select      record      ,cast (record AS  XML)       ,cast (record as xml). Value ('/record[1]/schedulermonitorevent [1]/systemhealth[1]/memoryutilization[1] ', ' int ')  as memutil      ,cast (Record as xml). Value ('/record[1]/schedulermonitorevent[1]/systemhealth[1]/pagefaults[1] ', ' int ')  as pagefaults       ,cast (Record as xml). Value ('/ Record[1]/schedulermonitorevent[1]/systemhealth[1]/workingsetdelta[1] ', ' int ')  as workingsetdelta         ,cast (Record as xml). Value ('/Record[1]/ Schedulermonitorevent[1]/systemhealth[1]/kernelmodetime[1] ', ' int ')  as KernelModeTime         ,cast (Record as xml). Value ('/record[1]/schedulermonitorevent[1]/ Systemhealth[1]/usermodetime[1] ', ' bigint')  as usermodetime        ,cast (Record as XML). Value ('/record[1]/schedulermonitorevent[1]/systemhealth[1]/processutilization[1] ', ' int ')  as  Processutilization        ,cast (Record as xml). Value ('/ Record[1]/schedulermonitorevent[1]/systemhealth[1]/systemidle[1] ', ' int ')  as SystemIdle       from sys.dm_os_ring_buffers     where       ring_buffer_type =  ' Ring_buffer_scheduler_monitor '

This article is from the "Dripping Stone Wear" blog, please be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1583973

SQL Server Ring Buffer--Ring_buffer_scheduler_monitor get SQL