Recommended Maxdop for SQL Server

There are some server level settings in SQL Server that you want to, and must, change. Each installation and client environment is different. Hence you want to get the best performance from you SQL that you can based on your circumstances.

Brent Ozar did a great job providing the items you need to look at here. Number one is setting the Max Degree of Parallelism aka MAXDOP.

Why?

The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan. This option determines the computing and thread resources that are used for the query plan operators that perform the work in parallel. Depending on whether SQL Server is set up on a symmetric multiprocessing (SMP) computer, a non-uniform memory access (NUMA) computer, or hyperthreading-enabled processors, you have to configure the max degree of parallelism option appropriately. – Microsoft Support.

Thankfully the MSDN team has a page that provides you with a MAXDOP calculator. Simply add your input and the page will tell you the optimal setting for your environment.

Why complicate matters with all the details if you can simply use a calculator?

Note: You can also run the SQL Server 2008 R2 or SQL Server 2012 Best Practice Analyze to verify your settings are correct.

Why haven’t you changed yours yet?

Support link: http://support.microsoft.com/kb/2806535/en-us

Advertisements
This entry was posted in SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014 and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s