|
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.
(comments are locked)
|
|
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.
(comments are locked)
|
|
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: -- Get CPU Utilization History for last 144 minutes (in one minute intervals) -- This version works with SQL Server 2008 and SQL Server 2008 R2 only @DaniSQL - I notice the above has note for SQL 2005 ONLY. Do you have a modified version for 2008?
Apr 18 '11 at 09:18 AM
Noonies
@Noonies two queries are listed above and the second half of the query is for 2008/2008R2
Apr 18 '11 at 10:43 AM
DaniSQL
i edited to break the queries to two now.
Apr 18 '11 at 10:46 AM
DaniSQL
@DaniSQL - Thank you for providing the SQL 2008 version. :) I needed this.
Apr 19 '11 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 '11 at 02:39 AM
boojieboi
(comments are locked)
|

