SQL Server 11.0.3401 - CPU 100% - HELP
Hi,
We are having odd issues where the CPU usage is increased to 100%, the process is SQL service. I have tried to investigate what could be the cause by using the following commands:
select * from sys.dm_exec_query_stats order by total_worker_time desc
select * from sys.dm_exec_query_stats order by execution_count desc
select * from sys.dm_exec_query_plan(0x0
However what I do not know, what is best practice to investigate this. Any help on this matter will be appreciated.
Regards,
introlux
We are having odd issues where the CPU usage is increased to 100%, the process is SQL service. I have tried to investigate what could be the cause by using the following commands:
select * from sys.dm_exec_query_stats order by total_worker_time desc
select * from sys.dm_exec_query_stats order by execution_count desc
select * from sys.dm_exec_query_plan(0x0
However what I do not know, what is best practice to investigate this. Any help on this matter will be appreciated.
Regards,
introlux
Scott Pletcher
If on Enterprise Edition, check the 'max degree of parallelism'; it's possible that large tasks are using all cpus unless that value has been adjusted. Also check the cost threshold for parallelism; the default is too low, which causes excessive CPU usage also.
ASKER
Running Standard Edition, also noticed that its on SP1 and current SP is SP3. Worth upgrading?
Where would I locate this setting you have mentioned?
Where would I locate this setting you have mentioned?
EXEC sp_configure 'cost threshold for parallelism'
That's an advanced option; if advanced options are not on, we'll have to add code to allow you to see it.
SQL Server has a standard report on "Top 10 CPU Usage", average and total. Right-click on the instance in SSMS, select "Reports", "Standard Reports", etc.
And/or you can run this query:
SELECT TOP 50
[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_star
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),
ELSE qs.statement_end_offset END -
qs.statement_start_offset)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sq
ORDER BY [Average CPU used] DESC;
That's an advanced option; if advanced options are not on, we'll have to add code to allow you to see it.
SQL Server has a standard report on "Top 10 CPU Usage", average and total. Right-click on the instance in SSMS, select "Reports", "Standard Reports", etc.
And/or you can run this query:
SELECT TOP 50
[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_star
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),
ELSE qs.statement_end_offset END -
qs.statement_start_offset)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sq
ORDER BY [Average CPU used] DESC;
ASKER
EXEC sp_configure 'cost threshold for parallelism'
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
The configuration option 'cost threshold for parallelism' does not exist, or it may be an advanced option.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
The configuration option 'cost threshold for parallelism' does not exist, or it may be an advanced option.
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'cost threshold for parallelism'
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
RECONFIGURE
EXEC sp_configure 'cost threshold for parallelism'
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
ASKER
name minimum maximum config_value run_value
cost threshold for parallelism 0 32767 5 5
What does this mean?
cost threshold for parallelism 0 32767 5 5
What does this mean?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.