Max degree of parallelism

Question

Post reply

Max degree of parallelism

Viewing 15 posts - 16 through 30 (of 30 total)

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

The server is running OK. No hiccups yet. 🙂 Touch wood. More details regarding the system.

Cache Hit ratios

Buffer 100%, Plan(SQL) 55%, Plan (Objects) 100%.

No IO Waits (Very less).

The Stats are updated daily. The Indexes are maintained regularly. We do everything we can but there is always room for improvement. Even if it is tiny, I will take that. From the projection I have seen for 2012 and 2013, we will be tripling the traffic to our web site. That means more DB load.

We are planning to upgrade our HW next year.

-Roy

Paul White SSC Guru Points: 150457 More actions · Answer 2

Ninja's_RGR'us (7/26/2011)

That bugs me that you don't see anything for the DegreeOfParallelism either. Looks like there's something I don't understand in there (either the caching or the XML query). :unsure:

That attribute is only present in 'actual' plans. The plan cache stores plans that do not have specific runtime information - they are used (hopefully) for many executions.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150457 More actions · Answer 3

Roy Ernest (7/26/2011)

The server is running OK. No hiccups yet. 🙂 Touch wood. More details regarding the system.

Sounds like it's not broken = may not need fixing 😉

From the projection I have seen for 2012 and 2013, we will be tripling the traffic to our web site. That means more DB load. We are planning to upgrade our HW next year.

The peak load statistics you provided earlier indicated that you could handle roughly double the load right now (40% peak CPU) - all things being equal. I might feel happier keeping the relatively low utilization now, in anticipation of future needs (hardware does not always arrive on time). Also, think of all you are doing to help the environment by not stressing the A/C units in the server room so much :w00t:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 4

Ninja's_RGR'us (7/26/2011)

ALZDBA (7/26/2011)

See what happens when you rise the cost threshold for parallisme and be surprised, like I was.

Good or bad surprised? What and how did you trace the changes?

I just sampled the waits before and after. (cleared the stats after modification to have a fresh start)

cxpacket waits dropped well and stabelized to far less as before.

OLTP aps didn't complain they got served better.

So to me it seems this modification was a good guess and worth the try.

Parallelism had been restricted to 4 ( 16 way ) + treshold raised to 45.

So : less queries apply for parallelism and parallelism itself doesn't starve my cpus.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

Now here is one of the reason why I raised the question of MAXDOP. I never did like the idea of giving MAXDOP 1 on the server level. At least one year back I did advice one OP not to give a blanket MAXDOP 1 but to see if it is really hurting. But I was told that it was a stupid advice and you should give MAXDOP 1 for all OLTP Servers (NUMA or NON NUMA architecture)

-Roy

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

SQLkiwi (7/26/2011)

Ninja's_RGR'us (7/26/2011)

That bugs me that you don't see anything for the DegreeOfParallelism either. Looks like there's something I don't understand in there (either the caching or the XML query). :unsure:

That attribute is only present in 'actual' plans. The plan cache stores plans that do not have specific runtime information - they are used (hopefully) for many executions.

Figured as much. Thanks for saving me the research.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Roy Ernest (7/26/2011)

Now here is one of the reason why I raised the question of MAXDOP. I never did like the idea of giving MAXDOP 1 on the server level. At least one year back I did advice one OP not to give a blanket MAXDOP 1 but to see if it is really hurting. But I was told that it was a stupid advice and you should give MAXDOP 1 for all OLTP Servers (NUMA or NON NUMA architecture)

I never say always when it comes to general settings like this. It sounds like a bad idea but I'm sure there are "some" cases where it could be a good idea.

Paul White SSC Guru Points: 150457 More actions · Answer 8

Roy Ernest (7/26/2011)

Now here is one of the reason why I raised the question of MAXDOP. I never did like the idea of giving MAXDOP 1 on the server level. At least one year back I did advice one OP not to give a blanket MAXDOP 1 but to see if it is really hurting. But I was told that it was a stupid advice and you should give MAXDOP 1 for all OLTP Servers (NUMA or NON NUMA architecture)

Shades of grey. True, for a 100% OLTP load, where the number of concurrently runnable batches are sufficient to keep all available cores nicely humming along, and parallelism confers no elapsed-time benefits, server DOP 1 can make sense. Thing is, relatively few systems are 100% OLTP. Those with just a handful of things that would genuinely benefit from parallelism, can use the query-hint option. As soon as the workload becomes more mixed, if the serial load is not enough to keep the cores busy, and if there are queries that make effective use of parallelism (this is most systems now), server DOP 1 makes much less sense. So, guess what? It depends.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 9

Roy Ernest

SSC-Dedicated

Points: 38780

July 26, 2011 at 3:12 pm

#1358868

Thx for all the input. I appreciate it very much. I have two action points from this.

I will have to look at the missing indexes and I also know that I have to be a bit more careful when doing reviews of the DB objects.

-Roy

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

SQLkiwi (7/26/2011)

I do want to respond to the comments about CXPACKET though. Very few people understand what CXPACKET truly means (and I do not number storage engine experts among them). For almost all intents and purposes, CXPACKET should be ignored. It is a consequence of running a parallel query, nothing more. People do tend to see CXPACKET 'waits' and assume they mean something similar to PAGEIO_LATCH* or whatever - but they really really don't. Yes, if you run fewer parallel queries, and/or fewer parallel regions, you will see CXPACKET numbers decline. No surprises there. It does not mean your server has become more efficient, or that response time has improved, or much of anything else.

I'd love to read more about this. Anyone got a good article link handy?

suri.yalamanchili SSCommitted Points: 1645 More actions · Answer 11

suri.yalamanchili

SSCommitted

Points: 1645

July 27, 2011 at 11:14 am

#1359520

I previously had to change the DOP settings. For OLTP MS recommends DOP to be 1. I think you should monitor the setting usng DMV's for more than one value and then decide the best.

Paul White SSC Guru Points: 150457 More actions · Answer 12

Ninja's_RGR'us (7/26/2011)

I'd love to read more about this. Anyone got a good article link handy?

I humbly suggest this:

http://www.simple-talk.com/content/article.aspx?article=1250

A detailed discussion of CXPACKET will be in a future instalment. There's also some good information here:

http://technet.microsoft.com/en-us/library/gg415714.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

SQLkiwi (7/27/2011)

Ninja's_RGR'us (7/26/2011)

I'd love to read more about this. Anyone got a good article link handy?

I humbly suggest this:

http://www.simple-talk.com/content/article.aspx?article=1250

A detailed discussion of CXPACKET will be in a future instalment. There's also some good information here:

http://technet.microsoft.com/en-us/library/gg415714.aspx