November 15, 2009 at 3:22 pm
my server has very high CPU (between 90 - 100%) when certain processes run. However, if i profile what is going on with the server, SQL seems to be responding to all the sprocs being called in less than second. there is no blocking going on. what else can i look at that would help identify why the CPU is so high?
November 15, 2009 at 11:41 pm
Start with TaskMgr and make sure what's consuming the CPU from the 60,000 ft level, first.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2009 at 2:35 am
Are those processes related to SQL server or some other processes.
"Keep Trying"
November 16, 2009 at 5:07 am
yes, task manager reports that sqlservr.exe is using 99% of the CPU.
November 16, 2009 at 5:22 am
post the spec of your server.
you already seem to know which processes are causing the high cpu, so investigate the SQL behind them, and check out the health of the tables they work against.
This SQL identifies costliest queries by CPU
SELECT TOP 10
[Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END -
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;
---------------------------------------------------------------------
November 16, 2009 at 10:44 am
bell282 (11/15/2009)
my server has very high CPU (between 90 - 100%) when certain processes run. However, if i profile what is going on with the server, SQL seems to be responding to all the sprocs being called in less than second. there is no blocking going on. what else can i look at that would help identify why the CPU is so high?
sql server is capable of handling LOTS of requests in very quick succession. You are not seeing blocking, and no one is complaining of poor performance. Not sure what the problem is here. It could just be a busy server, right? now, I WILL note that if you are like every single entity I have come across there is probably a LOT that can be done with your server/schema/configuration/application/database to make things MORE efficient!! 😎
some common culprits of higher-than-necessary CPU: extended sprocs, CLR, cursors, UDFs, table variables, out-of-date statistics, missing indexes
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 16, 2009 at 5:10 pm
... and queries that try to do it all in a single statement... 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2009 at 5:21 pm
In an imaginary system with zero I/O latency (to disk or memory!) the ultimate capacity would probably be limited by CPU. Maybe this system has reached that state :laugh:
Seriously, though: I would add inappropriate parallelism to the list of potential culprits, together with forced loop joins (often seen together).
George's query is an excellent place to start. I would also check the signal_wait_time_ms column of sys.dm_os_wait_stats just to see whether runnable tasks are in fact already waiting for a scheduler.
Other fancy stuff can be done with ring buffers if you find that sort of thing interesting (I do): http://blogs.msdn.com/sql_pfe_blog/archive/2009/07/17/sql-high-cpu-scenario-troubleshooting-using-sys-dm-exec-query-stats-and-ring-buffer-scheduler-monitor-ring-buffer-in-sys-dm-os-ring-buffers.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 18, 2009 at 9:13 am
well, made some progress.. We have a table with about 300,000 records (and one of the columns is an image)... If we delete all the records out and re-run the process it completes in about 2 minutes (previously about 30 minutes)..
If i trace what is going on i see all the sprocs returning immed. (within .1 secodns!) and i don't see much network activity..
Any ideas why this table would cause any issues?
November 23, 2009 at 2:38 am
bell282 (11/18/2009)
(and one of the columns is an image)... If we delete all the records out and re-run the process it completes in about 2 minutes (previously about 30 minutes)..Any ideas why this table would cause any issues?
find the query that is used on this (and perhaps other) table(s) and do a "estimated execution plan" on it. check for things like table scan. if sql is doing a table scan and your image looks like a "normal" coloumn to sql server it has to read too much data to satisfy your query.
November 23, 2009 at 8:03 am
bell282 (11/18/2009)
well, made some progress.. We have a table with about 300,000 records (and one of the columns is an image)... If we delete all the records out and re-run the process it completes in about 2 minutes (previously about 30 minutes)..If i trace what is going on i see all the sprocs returning immed. (within .1 secodns!) and i don't see much network activity..
Any ideas why this table would cause any issues?
Has this been resolved now? You posted this follow-up on 11/18, 3 days after the original post. Just wondering if you have solved your high-CPU concern...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply