March 27, 2008 at 11:48 am
Intermittently our Sql 2005 server uses 60%+ of CPU - solid. Windows Task manager shows that much. I have tried to use Sql 2005 Profiler (with the Tuning template) but it doesn't identify CPU usage on a per "job" basis. So I can't tell which sql job (triggered by a usr app) is hogging the CPU.
Any thoughts much appreciated,
barkingdog
March 27, 2008 at 1:40 pm
The activity monitor in SSMS can help, but since it doesn't have deltas, you may find it difficult to use for this purpose.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2008 at 3:04 pm
a basic profiler will tell you this, include cpu cycles, reads, writes and duration - just look for what does the big numbers, it'll all be linked - usually it's high io = high cpu = high duration.
If your trace is swamped filter at >100k io or 100k cpu cycles etc.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 27, 2008 at 3:43 pm
This two step process should get what you're looking for. The CPU column is cumulative, so if the job is started using sp_start_job then that should be a pretty good indication of CPU usage. You'd want to run this when you observe the spike in Task Manager since the query is using information about currently running processes.
-- Step 1: Find the job consuming the most CPU; based on Activity Monitor.
SELECT
[Process ID] = p.spid,
[CPU] = p.cpu,
[Application] = p.program_name,
[User] = p.loginame,
[Status] = p.status,
[Command] = p.cmd
FROM master.dbo.sysprocesses p, master.sys.dm_exec_sessions s WITH (NOLOCK)
WHERE p.spid = s.session_id
AND p.program_name LIKE '%Job%'
ORDER BY p.cpu DESC
-- example output
-- 'SQLAgent - TSQL JobStep (Job 0x87AC45A67B0AB443B3FEAA3466BA5237 : Step 1)
-- Step 2: Get the friendly job name from the binary value.
SELECT name
FROM msdb.dbo.sysjobs
WHERE job_id = CONVERT(uniqueidentifier,0x87AC45A67B0AB443B3FEAA3466BA5237)
March 27, 2008 at 3:56 pm
Provided your instance has been running long enough for adequate stats - say a few days - the following query will yield the top 10 most CPU-intensive queries since last restart. This might help you find the culprit(s):
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;
Taken from: http://msdn2.microsoft.com/en-us/magazine/cc135978.aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 27, 2008 at 4:47 pm
Also, it may be that Max. degree of parallelism (MAXDOP) is set to an excessively high value on your instance.
Run the following query to get the top 10 wait types for your system - again make sure your instance was not restarted too recently as this data resides exclusively in memory:
SELECT TOP 10
[Wait type] = wait_type,
[Wait time (s)] = wait_time_ms / 1000,
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
/ SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;
taken from: http://msdn2.microsoft.com/en-us/magazine/cc135978.aspx
Is CXPACKET ranked among the top bottlenecks in your system?
If so, you may need to lower your MAXDOP value to a value of say 2.
If currently MAXDOP = 0, then you are likely overutilizing parallelism in your instance, which may in turn be the cause for the CPU issues you are seeing.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 27, 2008 at 7:01 pm
I am amazed by the depth of responses and useful ideas. Thanks to everyone.
Bow wow!
Barkingdog
March 28, 2008 at 5:13 pm
as long as your client tools have sp2 applied there are some custom reports which may help you http://www.grumpyolddba.co.uk/monitoring/Creating%20Custom%20Reports%20for%20the%20DBA.htm
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply