DB MAXDOP Settings

Question

Post reply

DB MAXDOP Settings

Henrico Bekker

One Orange Chip

Points: 27652
More actions
March 21, 2017 at 12:11 am

#336163

Comments posted to this topic are about the item DB MAXDOP Settings
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ken SSCoach Points: 17191 More actions · Answer 1

Cool option. Yet another reason to upgrade to 2016.

Stewart "Arturius" Campbell SSC Guru Points: 72634 More actions · Answer 2

Ken Wymore - Tuesday, March 21, 2017 8:00 AM
Cool option. Yet another reason to upgrade to 2016.

For sure.
Nice question, thanks Henriko

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

bretknoll SSC Enthusiast Points: 106 More actions · Answer 3

Please excuse me if this obvious, but what are primary and secondary databases? Are we talking about databases involved in HA? Are these only Always On HA or does it include Log Shipping? If you are able to answer this can you provide a reference.

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 4

bretknoll - Sunday, March 26, 2017 7:53 AM
Please excuse me if this obvious, but what are primary and secondary databases? Are we talking about databases involved in HA? Are these only Always On HA or does it include Log Shipping? If you are able to answer this can you provide a reference.

~~I could certainly be wrong but I'm thinking it was a typo. I believe what was meant was "Primary and Secondary FILES".~~

@ ~~Henrico Bekker~~ ... can you verify, please? And if you really did mean "Databases" and not "Files", then I agree with Bret... do you have a reference link because I've never heard of "Primary and Secondary DATABASES" before. EDIT: SEE THE NEXT POST BELOW FOR A CLARIFYING LINK!

Shifting gears a bit, this IS a really cool feature because I have predominately batch-oriented databases that would benefit from a higher max dop and busy OLTP databases that should be relegated to a lower MAXDOP on the same box. In the past, my rule of thumb was MAXDOP <= Core/4 < 8 . I can tweak the hell out of that when we get to 2016 in a couple of months.

Another really helpful 2016 feature for databases that haven't yet been brought up to snuff to fix years of legacy development and poor coding practices that causes the bad form of parameter sniffing is the new ability to clear proc cache at the database level.

--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: 1004415 More actions · Answer 5

Yeah... I was wrong. They really do mean "Primary and Secondary DATABASES". Here's a reference that's a bit more clear rather than leaving those of us that don't know "in a lurch for a search" for what they meant. It's one of those things where if you don't know about it, it's hard to find.
http://www.sqlskills.com/blogs/glenn/using-alter-database-scoped-configuration-in-sql-server-2016/

To quote the pertinent statement from the article:

If you are using AlwaysOn Availability Groups, the first four commands can also be used to affect the database-level configuration setting for ALL of the secondary database copies that are in a AG.

Heh... the good part about being wrong is that I learned something new today! ::) :blush:

--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)

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 6

Jeff Moden - Sunday, March 26, 2017 10:48 AM
Yeah... I was wrong. They really do mean "Primary and Secondary DATABASES". Here's a reference that's a bit more clear rather than leaving those of us that don't know "in a lurch for a search" for what they meant. It's one of those things where if you don't know about it, it's hard to find.
http://www.sqlskills.com/blogs/glenn/using-alter-database-scoped-configuration-in-sql-server-2016/
To quote the pertinent statement from the article:
If you are using AlwaysOn Availability Groups, the first four commands can also be used to affect the database-level configuration setting for ALL of the secondary database copies that are in a AG.
Heh... the good part about being wrong is that I learned something new today! ::) :blush:

Apologies for not responding to the thread sooner, but thank you for resonding to bretknoll.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 7

No problem. And thank you! Thanks to your question, I learned something new.

--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)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Ken Wymore - Tuesday, March 21, 2017 8:00 AM
Cool option. Yet another reason to upgrade to 2016.

Agreed. This is very beneficial.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLWinther SSCertifiable Points: 6294 More actions · Answer 9

This is a very useful setting!

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform