CPU usage by an instance with TSQL

• Roust_m

  SSCoach

  Points: 17410

  More actions

  November 28, 2016 at 6:09 pm

  #319343

  Hi,

  I have 4 SQL Server 2014 Web Edition instances running on 32 core AWS server.

  I want to take snapshots every minute of what % of the total CPU on the server is taken by each instance and log it into a table.

  Is it possible via TSQL?

  Thanks.

• joeroshan

  SSChampion

  Points: 10377

  More actions

  November 29, 2016 at 3:33 am

  #1914887

  Hi

  I found the below query from performance dashboard tool. You can try using that

  declare @ms_now bigint

  select @ms_now = ms_ticks from sys.dm_os_sys_info;

  select top 50 record_id,

  dateadd(ms, -1 * (@ms_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 '%<SystemHealth>%') as x

  ) as y

  order by record_id desc

  ----------------------------------------------------------------------------------------------------------------------------------------------------
  Roshan Joe

  Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  November 29, 2016 at 5:45 am

  #1914915

  Joe's solution involves connecting to each instance on the server individually. You may be happy with that, or you might prefer to connect to one of them and use xp_cmdshell to run these two commands:

  wmic path Win32_PerfFormattedData_PerfProc_Process where "Name Like 'sqlservr%'" Get PercentProcessorTime, IDProcess

  wmic path Win32_Process where Caption='sqlservr.exe' Get CommandLine, ProcessID

  You can join the two result sets together to get the processor utilisation for each instance. This relies on your SQL Server service account having the necessary permissions to run the WMIC commands.

  John