question

emmanuel avatar image
emmanuel asked

How can i tell what database or script is causing high CPU on the server

We have sql 2005 enterprise in a cluster and cant tell what process under sql is causing the cpu to get to 100%. is there a script to show what database is queuing or runnable or blocking.

thanks

sql-server-2005
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.

Kev Riley avatar image
Kev Riley answered

Finding what processes are consuming the CPU fall into 2 categories:

  • What is consuming it NOW!?

Adam Machanic has a great script over on his blog that I use a lot.
Ian Stirk also has a useful script over on the SSC main site.

After looking at those two, you may come up with a hybrid version of your own.....

  • Over time what has been the biggest consumer of CPU?

Here is where the DMVs come in very handy....
Not sure of the original author of this, but here's a snippet of a query I use

--Costliest queries by CPU:
SELECT TOP 10
    [Average CPU used] = total_worker_time / qs.execution_count
    ,[Total CPU used] = total_worker_time,
    [Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
    ,[Total IO] = (total_logical_reads + total_logical_writes)
    ,[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),
    last_execution_time as LastSeen
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
--more than 5 occurances
where qs.execution_count > 5
ORDER BY [Average CPU used] DESC;
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.

Steinar avatar image
Steinar answered

Execute sp_who2 to see what queries are running right now. Run it several times and save the output, then you should be able to get an idea of what is causing the CPU hog. Otherwise you could run activity monitor or even profiler to get more info. But be careful to run profiler on an already slow system, as that too takes resources!

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.

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.