Hi, If you are unsure of what process is taking CPU time, you can use % processor time for the processes to see processor time taken by the process. Or you can use the task manager to see the no of CPU cycles used by all the processes when the processor usage is high. If you think there is msdb acticvity is going on you can run the query below to see if any query executing on the sql sever when the processer activity is high. SELECT [Spid] = session_Id , ecid , [Database] = DB_NAME(sp.dbid) , [User] = nt_username , [Status] = er.status , [Wait] = wait_type , [Individual Query] = SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) ,[Parent Query] = qt.text , Program = program_name , Hostname , nt_domain , start_time FROM sys.dm_exec_requests er INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt WHERE session_Id > 50 -- Ignore system spids. AND session_Id NOT IN (@@SPID) -- Ignore this current statement. ORDER BY 1, 2 Thanks, VinayVinay Valeti

Hi, As Vinay Stated try that Query and aslo try this to check on which database you have your sessions and cpu usage high. select spid,name,kpid,waitresource,waittype,lastwaittype,dbid,cpu,physical_io,memusage,login_time,hostname,program_name from sys.sysprocesses sp cross join sys.databases db where sp.dbid=db.database_id order by cpu desc Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://myspeakonbi.blogspot.com/

Hello, OS Name Microsoft(R) Windows(R) Server 2003 Enterprise x64 Edition Version 5.2.3790 Service Pack 2 Build 3790 Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Enterprise Edition (64-bit) We have a database server where the CPU increases over time, and this is alleviated by restarting SQL Server Agent (not SQL Server service). As far as I am aware of, there are no jobs that are running when we restart it. But for some reason, the SQL Server Agent restart alleviates it. Any ideas why this is so? We are concerned about the CPU increase over time. Is this perhaps related to data accumulating in msdb database? Thanks!Thanks.

hi, What do you mean by CPU Increase ? is it the I\O Buffers,CPU Usage,PF Usage ?Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://myspeakonbi.blogspot.com/

Hi Rakesh, It is CPU Usage. Thanks.

Hi, If you are unsure of what process is taking CPU time, you can use % processor time for the processes to see processor time taken by the process. Or you can use the task manager to see the no of CPU cycles used by all the processes when the processor usage is high. If you think there is msdb acticvity is going on you can run the query below to see if any query executing on the sql sever when the processer activity is high. SELECT [Spid] = session_Id , ecid , [Database] = DB_NAME(sp.dbid) , [User] = nt_username , [Status] = er.status , [Wait] = wait_type , [Individual Query] = SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) ,[Parent Query] = qt.text , Program = program_name , Hostname , nt_domain , start_time FROM sys.dm_exec_requests er INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt WHERE session_Id > 50 -- Ignore system spids. AND session_Id NOT IN (@@SPID) -- Ignore this current statement. ORDER BY 1, 2 Thanks, VinayVinay Valeti

Hi, As Vinay Stated try that Query and aslo try this to check on which database you have your sessions and cpu usage high. select spid,name,kpid,waitresource,waittype,lastwaittype,dbid,cpu,physical_io,memusage,login_time,hostname,program_name from sys.sysprocesses sp cross join sys.databases db where sp.dbid=db.database_id order by cpu desc Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://myspeakonbi.blogspot.com/

Thanks everyone!Thanks.

The issue is still persistent. CPU still climbs over time and the only way to alleviate it is thru SQL Agent restart. We also captured running processes using sp_whoisactive and the queries running when we see the issue are always different, so it cannot be the same query causing the issue. For now, we have disabled SQL Agent all together, and we put all the SQL jobs on task manager to isolate the issue. However, we noticed that the CPU issue still continues to climb and the only way to get it down is thru SQL Agent. Also, we are regularly cleaning our msdb tables, per below: Table - row count backupfile 5314 backupmediaset 3071 backupmediafamily 2736 sysmail_mailitems 2696 backupset 2657 backupfilegroup 2657 sysmail_log 2293 sysjobactivity 328 MSdbms_datatype_mapping 325 sysjobhistory 296 MSdbms_map 248 MSdbms_datatype 141 syssessions 136 syscategories 21 syssubsystems 11 sysmail_send_retries 11 sysalerts 10 Any ideas?Thanks.

Can you run this query and tell me what are the top wait types? WITH Waits AS (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s, 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK' ,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE' ,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT' ,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT' ,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')) SELECT W1.wait_type, CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s, CAST(W1.pct AS DECIMAL(12, 2)) AS pct, CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); Use the query below to see what queries are taking CPU time. SELECT TOP(25) qt.[text] AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count AS [Execution Count], ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()), 0) AS [Calls/Second], ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS [AvgElapsedTime], qs.max_logical_reads, qs.max_logical_writes, DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache] FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt WHERE qt.[dbid] = DB_ID() -- Filter by current database ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE); Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

Hi Vinay, Result from 1st query: wait_type - wait_time_s - pct - running_pct PAGEIOLATCH_SH 24843497.22 69.44 69.44 WRITELOG 4878072.16 13.64 83.08 PAGEIOLATCH_EX 2033600.56 5.68 88.76 CXPACKET 1586206.61 4.43 93.19 SOS_SCHEDULER_YIELD 571460.22 1.60 94.79 BACKUPIO 451103.33 1.26 96.05 LATCH_EX 267251.14 0.75 96.80 OLEDB 261740.17 0.73 97.53 MSQL_XP 246974.44 0.69 98.22 PAGELATCH_SH 160804.94 0.45 98.67 LCK_M_IX 77045.89 0.22 98.89 ASYNC_NETWORK_IO 72349.84 0.20 99.09 Result from 2nd query (sp_name taken out): total worker time - avgworkertime - execution count - calls/second - avgelapsedtime - max_logical_reads - max_logical_writes - age in cache 87110744132 119 729431478 406 552 8 0 29905 84412752026 91890 918627 0 100807 521 0 29880 70746029232 11013 6423410 3 11917 3196 0 29904 56948283573 155 366128837 204 3805 19 0 29905 42522306607 112 378069090 210 528 8 0 29904 33465135225 202 165259518 92 1962 13 0 29904 31047476442 286 108312801 60 388 133 10 29904 30322158092 9629 3148736 1 95284 111654 0 29874 24885688554 234 106148927 59 6310 15 0 29905 22133534137 161 137020233 76 168 7 0 29904 21831151516 239 91084874 50 1193 13 0 29904 21446900239 144 148501958 82 204 7 0 29904 20882153948 208 100180508 55 1062 15 0 29904 19256898385 122 157708452 88 1664 4 0 29865 19242542036 244 78812233 43 652 16 0 29904 19109745145 229 83342961 46 267 127 10 29904 18186216126 242 75049350 41 6597 13 0 29905 17359204093 220 78816516 43 606 12 0 29904 17267888833 137 126029183 70 141 2 0 29904 16538800551 131 126029164 70 135 2 0 29904 14575031166 210 69179409 38 2603 15 0 29904 14128136721 255 55338028 30 9053 15 0 29905 13889699345 242 57188082 31 7142 17 0 29905 13546666995 2832 4783363 2 2853 130 0 29904 12578674743 2030 6196125 3 36403 80424 0 29905 Your help is very much appreciated!Thanks.

Hi, Your Top 3 Wait times are IO waits,next CXPACKET and SOS_SCHEDULER_YIELD are CPU related.CXPACKET which is caused mostly by parallel queries.At this point i am not sure why CPU activity is high.You can try disabling parallel queries for a period and see if your CPU times are ok. I have couple of questions though. 1. Any of those stored procedures that are in the list.have you noticed them running when you have high CPU activity? 2.High CPU activity,is it intermittent.I mean once the activity is high,will it come down after a period of time and stays stable for some time before it goes up again? 3.you said you have jobs running,does any of the stored procs you see in max worker time are called from Jobs. 4.When you CPU activity high,Did you check the task manages and all the CPU cycles are consumed by SQL server process? Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer