asked
How to find out what uses cpu other than SQL
Hi, I have an interesting situation where a SQL2008 server is experiencing increasing CPU utilization but it is not SQL server that is increasing. This server is dedicated to SQL and doesn't run SSIS, SSAS, or anything like that. We noticed this as we are tracking (Processor(_Total)\% Processor Time - (Process(sqlservr)\% Processor Time/#CPUs)) and normalizing this against workload (Batch Requests). I'm looking for theories as to what is going on here and suggestions for how to investigate - if possible. Right now the server seems stable and application performance is not suffering unduly (we have headroom) but if the trend of the past week continues it is only a matter of time before problems happen. Also, I cannot correlate any system changes to the onset of this pattern, not much has changed in months.
Process Monitor from SysInternals will break down every component that is using memory and or CPU. I am sure you could run this and write the counters to a log file. I have only used the GUI of it to track things real time. It is a free tool so check it out.
As @TRAD said i will go with Process Exec too. But if you want to get a snapshot of how SQL vs Non-SQL processes use CPU on your sever using TSQL you can use [below queries by Glen Berry][1]: -- Get CPU Utilization History for last 144 minutes (in one minute intervals) -- (SQL 2005 Only) DECLARE @ts_now bigint; SET @ts_now = (SELECT cpu_ticks / CONVERT(float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info); SELECT TOP(144) SQLProcessUtilization AS [SQL Server Process CPU Utilization], SystemIdle AS [System Idle Process], 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] 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 '% %') AS x ) AS y ORDER BY record_id DESC OPTION (RECOMPILE); -- This version works with SQL Server 2008 and SQL Server 2008 R2 only DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); SELECT TOP(144) SQLProcessUtilization AS [SQL Server Process CPU Utilization], SystemIdle AS [System Idle Process], 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] 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 N'% %') AS x ) AS y ORDER BY record_id DESC OPTION (RECOMPILE); [1]:
http://sqlserverperformance.wordpress.com/2011/04/11/april-version-of-sql-server-2008-diagnostic-queries/95
Hi and thanks for the responses. I hope we can use the process monitor and the query on production soon because other paths haven't shed any light. It seems the increased extra CPU is privileged mode, not user - the total of privileged time has trended up from about 2% to 6% over almost 3 weeks. Capturing privileged and user time for every process on the system only shows Services.exe using about 5% of total CPU most of which is privileged time, nothing else comes close. I have no idea what its baseline should be but will keep an eye on it going forward. Once again, if anyone has any thoughts as to what is going on here I'm all ears. I'll update here when I get some news.
