May 14, 2010 at 11:37 am
I rarely use MAXDOP option in our queriess but most of the time all our CPU's (8) hit 100% dut to the queries users execute. How do i check what is the MAXDOP value for my server and is it true that default value is 0 which means use all 8 cpu's.
Is there way to limit 90% usage of each CPU ?
May 14, 2010 at 11:48 am
You can see what MAXDOP is set at for the server by running the following:
sp_configure 'show advanced options', 1;
GO
sp_configure 'max degree of parallelism'
or in SSMS you can right click the servername>Properties>Advanced> Max Degree of Parallelism
A value of 0 is the default and will use all available CPU's for parallelism.
The only way to limit CPU utilization is through Resource Governor in SQL 2008 Ent. MAXDOP does not control how much CPU is utilized only how many.
May 14, 2010 at 12:31 pm
Do you have a reason to suspect parallelism is causing the high CPU? Expect to see a lot of waittype CXPACKET if parallelism is a problem
You would probably be better off investigating the highest CPU using queries and looking to tune them
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;
Another option with parallelism if this is an OLTP system is to look at 'cost threshold for parallelism' sp_configure option. the default of 5 for this can be too low. Test it out though.
---------------------------------------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply