Using Recommendations for MAXDOP and Max Memory During Installation

Question

Using Recommendations for MAXDOP and Max Memory During Installation

iLearnSQL

Say Hey Kid

Points: 705
More actions
September 1, 2020 at 12:00 am

#3782061

Comments posted to this topic are about the item Using Recommendations for MAXDOP and Max Memory During Installation

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

I'm not sure where the 70-30 recommendation actually came from but I have do disagree with it on large systems. I can't believe it's not a total waste to give the operating system (for example) 30% of a half terabyte of RAM, which works out to giving the OS 150GB of RAM. Of course, I could be wrong but someone would have to show me with some kind of real proof that 30% is a good idea on such a machine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

p.s. I don't know who left you a rating of "1" but it wasn't me. I generally don't rate articles and I certainly wouldn't rate one so low without an explanation. I think it's horribly selfish of someone to rate with a 1 or a 2 without explaining why because such low ratings do no one any good unless the person giving it explains why.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erik Mus Newbie Points: 2 More actions · Answer 3

Thank you for the scripts, a few remarks:

It seems you have to execute the query on the server where the instance resides;
Executing the script from a CMS will only work for the instance on the server you're logged on to;
Try running the script on a SQL Server Express Edition!
That Edition will only support serial execution but the script could easily advise a MAXDOP > 1.
Ergo: it looks not taking the SQL Server Edition into account.

Erik Mus Newbie Points: 2 More actions · Answer 4

Thank you for the scripts, a few remarks:

It seems you have to execute the query on the server where the instance resides;

Executing the script from a CMS will only work for the instance on the server you're logged on to;

Try running the script on a SQL Server Express Edition!

That Edition will only support serial execution but the script could easily advise a MAXDOP > 1.

Ergo: it looks not taking the SQL Server Edition into account.