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(0x0600050021785620D065754C0100000001000000000000000000000000000000000000000000000000000000)

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

Regards,

introlux
introluxTechincal DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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 DirectorAuthor 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 DBACommented:
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;
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

introluxTechincal DirectorAuthor 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 DBACommented:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'cost threshold for parallelism'
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
introluxTechincal DirectorAuthor Commented:
name      minimum      maximum      config_value      run_value
cost threshold for parallelism      0      32767      5      5

What does this mean?
Scott PletcherSenior DBACommented:
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ãoMSSQL Senior EngineerCommented:
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 AdminCommented:
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.
Scott PletcherSenior DBACommented:
Or set it to 40 or 50.  But 5 is definitely too low.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.