SQL Server 11.0.3401 - CPU 100% - HELP

introlux
introlux used Ask the Experts™
on
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(0x0600050021785620D065754C0100000001000000000000000000000000000000000000000000000000000000)

However what I do not know, what is best practice to investigate this. Any help on this matter will be appreciated.

Regards,

introlux
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.
introluxTechincal Director

Author

Commented:
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?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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_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;
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

introluxTechincal Director

Author

Commented:
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'cost threshold for parallelism'
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
introluxTechincal Director

Author

Commented:
name      minimum      maximum      config_value      run_value
cost threshold for parallelism      0      32767      5      5

What does this mean?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
It means vastly too many queries are using more than one CPU.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'cost threshold for parallelism', 80
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
We are having odd issues where the CPU usage is increased to 100%, the process is SQL service.
Always or during some periods only?
Try to run sp_who2 next time it occurs this behavior and check if any number different that 0 appears in the blocked column.

Running Standard Edition, also noticed that its on SP1 and current SP is SP3. Worth upgrading?
Having your SQL Server up to date is a best practice. Even SP3 doesn't correct this particular issue for sure is correcting many others that you didn't notice yet. So, yes, it's worth applying it and check if the issue still persists.
Marcin StrzodaSQL Server DB Admin
Commented:
Using the value 80 for cost threshold for parallelism forces sql server to run almost all queries to run on single thread only. I would suggest to set max degree of parallelism  to 8 as mentioned before and verify if do you have fresh column statistics and defragmented indexes in the database on which the query causing high CPU usage was running.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Or set it to 40 or 50.  But 5 is definitely too low.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial