question

boojieboi avatar image
boojieboi 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.
sql-server-2008sqlcpu
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL avatar image
DaniSQL answered
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
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@DaniSQL - I notice the above has note for SQL 2005 ONLY. Do you have a modified version for 2008?
0 Likes 0 ·
@Noonies two queries are listed above and the second half of the query is for 2008/2008R2
0 Likes 0 ·
i edited to break the queries to two now.
0 Likes 0 ·
@DaniSQL - Thank you for providing the SQL 2008 version. :) I needed this.
0 Likes 0 ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.