x

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.
more ▼

asked Apr 18, 2011 at 02:55 AM in Default

boojieboi gravatar image

boojieboi
21 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
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.
more ▼

answered Apr 18, 2011 at 05:17 AM

Tim gravatar image

Tim
36.4k 39 41 139

(comments are locked)
10|1200 characters needed characters left

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

answered Apr 18, 2011 at 07:27 AM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

@DaniSQL - I notice the above has note for SQL 2005 ONLY. Do you have a modified version for 2008?
Apr 18, 2011 at 09:18 AM Noonies
@Noonies two queries are listed above and the second half of the query is for 2008/2008R2
Apr 18, 2011 at 10:43 AM DaniSQL
i edited to break the queries to two now.
Apr 18, 2011 at 10:46 AM DaniSQL
@DaniSQL - Thank you for providing the SQL 2008 version. :) I needed this.
Apr 19, 2011 at 10:06 AM Noonies

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.
Apr 21, 2011 at 02:39 AM boojieboi
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1853
x742
x14

asked: Apr 18, 2011 at 02:55 AM

Seen: 2246 times

Last Updated: Apr 18, 2011 at 02:55 AM