MAXDOP Advice

Question

MAXDOP Advice

Kwisatz78

SSChampion

Points: 12478
More actions
December 3, 2009 at 2:34 am

#137464

Hi all
I know this has been discussed on these forums before but I am a little all at sea with this at the moment.
I have read articles on setting MAXDOP = 1 at server level, and also articles on setting it to half number of processors. I understand also that you can provide hints to processes to aid parallelism (I am talking OLTP)
Yesterday I did a reset of the wait stats and since then in the view sys.dm_os_wait_stats CXPACKET is the second highest with a count of 3271668 with SOS_SCHEDULER_YIELD highest with a count of 5613210, are these value high?
As other posters have adhered to although setting the MAXDOP level may resolve the issue at hand, how can I test what impact it is having on other queries? Do I just profile the system on day 1 and group by time, change the MAXDOP and then profile the system again to compare differences, is it that easy or should I be looking at other details?
Sorry if I have been a bit vague I need some steer on this as to how to go about prooving a change to MAXDOP would be beneficial.
Thanks for any help

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Kwisatz78 (12/3/2009)
Yesterday I did a reset of the wait stats and since then in the view sys.dm_os_wait_stats CXPACKET is the second highest with a count of 3271668 with SOS_SCHEDULER_YIELD highest with a count of 5613210, are these value high?

Maybe.

Is your system OLTP or data warehouse?

How many cores do you have?

Are you seeing queries with high CXPacket waits or high SOS_Scheduler_yield waits?

If so, what are those queries doing and, for the queries with teh CXPacket waits, what are the threads of the query that don't have CXPacket waits waiting for?

Question is, are you having performance problems?

As other posters have adhered to although setting the MAXDOP level may resolve the issue at hand, how can I test what impact it is having on other queries?

By testing them out, preferably not on the production server.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kwisatz78 SSChampion Points: 12478 More actions · Answer 2

Hi Gila

Is your system OLTP or data warehouse?
How many cores do you have?

My system is OLTP and uses 4 cores. Currently MAXDOP is set to 0 with threshold of 5

Are you seeing queries with high CXPacket waits or high SOS_Scheduler_yield waits?
If so, what are those queries doing and, for the queries with teh CXPacket waits, what are the threads of the query that don't have CXPacket waits waiting for?

Ok this is where my understanding is weak, how can I see what queries have CXPACKET or SOS_Scheduler_yield waits? and further to that how do I then look at the details of the threads as you have stated?

Many thanks

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 3

Kwisatz78 (12/3/2009)
Hi Gila
Is your system OLTP or data warehouse?
How many cores do you have?
My system is OLTP and uses 4 cores. Currently MAXDOP is set to 0 with threshold of 5
Are you seeing queries with high CXPacket waits or high SOS_Scheduler_yield waits?
If so, what are those queries doing and, for the queries with teh CXPacket waits, what are the threads of the query that don't have CXPacket waits waiting for?
Ok this is where my understanding is weak, how can I see what queries have CXPACKET or SOS_Scheduler_yield waits? and further to that how do I then look at the details of the threads as you have stated?
Many thanks

A threshold of 5 is extremely low, especially for an OLTP system. I'd suggest bumping it up to a higher number. I use 25 as a starting point and then observe the system to see if we need to move it up or down.

The number is based on estimated costs from the execution plan. These numbers can be somewhat arbitrary causing you to get more parallel plans that you should.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Kwisatz78 (12/3/2009)
Ok this is where my understanding is weak, how can I see what queries have CXPACKET or SOS_Scheduler_yield waits? and further to that how do I then look at the details of the threads as you have stated?

Query sys.dm_exec_requests. That'll show you what's running. When a query runs in parallel, it'll have multiple rows (with the same session_id) in that DMV.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

GilaMonster (12/3/2009)
Kwisatz78 (12/3/2009)
Ok this is where my understanding is weak, how can I see what queries have CXPACKET or SOS_Scheduler_yield waits? and further to that how do I then look at the details of the threads as you have stated?
Query sys.dm_exec_requests. That'll show you what's running. When a query runs in parallel, it'll have multiple rows (with the same session_id) in that DMV.

Gail,

sys.dm_exec_requests will only show one row for a parallel query. This is where Bob Ward said that sysprocesses is still useful since it shows multiple rows, but you could join to sys.dm_os_tasks to see the parallel tasks that are executing for the session.

-- returns only one row for parallel request

select *

from sys.dm_exec_requests r

join sys.dm_exec_sessions s on r.session_id = s.session_id

where is_user_process = 1

-- returns multiple rows for parallel request

select *

from sys.dm_exec_requests r

join sys.dm_exec_sessions s on r.session_id = s.session_id

join sys.dm_os_tasks t on r.session_id = t.session_id

where is_user_process = 1

-- returns same multiple rows for parallel request

select *

from sysprocesses p

join sys.dm_exec_sessions s on p.spid = s.session_id

where is_user_process = 1

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Grant Fritchey (12/3/2009)
A threshold of 5 is extremely low, especially for an OLTP system. I'd suggest bumping it up to a higher number. I use 25 as a starting point and then observe the system to see if we need to move it up or down.
The number is based on estimated costs from the execution plan. These numbers can be somewhat arbitrary causing you to get more parallel plans that you should.

To help with determining where this option might be set, query the plan cache for existing parallel plans and see the cost associations to current plans that executed parallel.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES

(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT

query_plan as CompleteQueryPlan,

n.value('(@StatementText)[1]', 'VARCHAR(4000)') as StatementText,

n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') as StatementOptimizationLevel,

n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') as StatementSubTreeCost,

n.query('.') as ParallelSubTreeXML,

ecp.usecounts,

ecp.size_in_bytes

FROM sys.dm_exec_cached_plans as ecp

CROSS APPLY sys.dm_exec_query_plan(plan_handle) as eqp

CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') as qn(n)

WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1

I'd look at the high use count plans, and see if there is a missing index associated with those queries that is driving the cost up. If you can fix the high use plans having a couple of low use count plans that use parallelism doesn't have as much of an impact. The big problem is when you have a plan that is used constantly and it reduces concurrency.