<div>
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. &nbsp;Also check the cost threshold for parallelism; the default is too low, which causes excessive CPU usage also.
</div>


<div>
Running Standard Edition, also noticed that its on SP1 and current SP is SP3. Worth upgrading?<br />
<br />
Where would I locate this setting you have mentioned?
</div>


<div>
EXEC sp_configure 'cost threshold for parallelism'<br />
<br />
That's an advanced option; if advanced options are not on, we'll have to add code to allow you to see it.<br />
<br />
SQL Server has a standard report on &quot;Top 10 CPU Usage&quot;, average and total. &nbsp;Right-click on the instance in SSMS, select &quot;Reports&quot;, &quot;Standard Reports&quot;, etc.<br />
<br />
And/or you can run this query:<br />
<br />
SELECT TOP 50<br />
&nbsp;[Average CPU used] = total_worker_time / qs.execution_count,<br />
&nbsp;[Total CPU used] = total_worker_time,<br />
&nbsp;[Execution count] = qs.execution_count,<br />
&nbsp;[Individual Query] = SUBSTRING (qt.text,qs.statement_star<wbr />t_offset/2<wbr />, <br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(CASE WHEN qs.statement_end_offset = -1 <br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; THEN LEN(CONVERT(NVARCHAR(MAX),<wbr /> qt.text)) * 2 <br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE qs.statement_end_offset END - <br />
qs.statement_start_offset)<wbr />/2)<br />
,[Parent Query] = qt.text<br />
,DatabaseName = DB_NAME(qt.dbid)<br />
FROM sys.dm_exec_query_stats qs<br />
CROSS APPLY sys.dm_exec_sql_text(qs.sq<wbr />l_handle) as qt<br />
ORDER BY [Average CPU used] DESC;
</div>


<div>
EXEC sp_configure 'cost threshold for parallelism'<br />
<br />
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62<br />
The configuration option 'cost threshold for parallelism' does not exist, or it may be an advanced option.
</div>


<div>
EXEC sp_configure 'show advanced options', 1<br />
RECONFIGURE<br />
EXEC sp_configure 'cost threshold for parallelism'<br />
EXEC sp_configure 'show advanced options', 0<br />
RECONFIGURE
</div>


<div>
name &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;minimum &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;maximum &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;config_value &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;run_value<br />
cost threshold for parallelism &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;32767 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5<br />
<br />
What does this mean?
</div>


<div>
<div class="content wysiwyg-content">
Hi,<br />
<br />
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:<br />
<br />
select * from sys.dm_exec_query_stats order by total_worker_time desc<br />
select * from sys.dm_exec_query_stats order by execution_count desc<br />
select * from sys.dm_exec_query_plan(0x0<wbr />6000500217<wbr />85620D0657<wbr />54C0100000<wbr />0010000000<wbr />0000000000<wbr />0000000000<wbr />0000000000<wbr />0000000000<wbr />0000000)<br />
<br />
However what I do not know, what is best practice to investigate this. Any help on this matter will be appreciated.<br />
<br />
Regards,<br />
<br />
introlux
</div>
</div>


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

SQL Server 11.0.3401 - CPU 100% - HELP

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Windows Server 2012 is the server version of Windows 8 and the successor to Windows Server 2008 R2. Windows Server 2012 is the first version of Windows Server to have no support for Itanium-based computers since Windows NT 4.0. Windows Server 2012, now in its second release (Windows Server 2012 Release 2) includes Foundation, Essentials, Standard and Datacenter, and does not support IA-32 or IA-64 processors.

Windows Server 2012

The Microsoft Server topic includes all of the legacy versions of the operating system, including the Windows NT 3.1, NT 3.5, NT 4.0 and Windows 2000 and Windows Home Server versions.