Switch branches/tags
Nothing to show
Find file History
Latest commit 9a2aac9 Aug 27, 2018
Permalink
..
Failed to load latest commit information.
LICENSE.md LICENSE.md Aug 25, 2018
README.md README.md Aug 27, 2018
maxdop_calculator.sql MAXDOP Calculator Aug 25, 2018

README.md

MAXDOP Calculator

This T-SQL script will help you to calculate to correct MAXDOP configuration for your SQL Server instance. It runs for any SQL Server instance, starting from the 2012 version.

Here is an example of the output message from a SQL Server instance running on the 2016 version or major:

------------------------------------------------------------------------
MAXDOP at Database level:
------------------------------------------------------------------------
DatabaseName                   ConfigurationName MAXDOP Configured Value
------------------------------ ----------------- -----------------------
MyDB_01                        MAXDOP            0
MyDB_02                        MAXDOP            0
MyDB_03                        MAXDOP            0
 
--------------------------------------------------------------
MAXDOP at Instance level:
--------------------------------------------------------------
MAXDOP configured value: 	8                             
MAXDOP recommended value: 	8                             
--------------------------------------------------------------

The output also returns the MAXDOP configuration at database level, this information is retrieved from the sys.database_scoped_configurations DMV.

Here is an example of the output message from a SQL Server instance running on a version prior to SQL Server 2016 version:

--------------------------------------------------------------
MAXDOP at Instance level:
--------------------------------------------------------------
MAXDOP configured value: 	0                             
MAXDOP recommended value: 	4                             
--------------------------------------------------------------
 
In case you want to change MAXDOP to the recommeded value, please use this script:
 
EXEC sp_configure 'max degree of parallelism',4                             
GO
RECONFIGURE WITH OVERRIDE;

The output message returns the current and recommended MAXDOP configuration, also the syntax to change it in case it is wrong.

NOTE

The requirement to execute this T-SQL script is to have SQLCMD mode enabled in SSMS, you can take a look at one of my blog post where you can learn how to enable it.

Please don't hesitate to leave your feedback, thanks!

Follow me

N|Solid N|Solid N|Solid N|Solid

License

MIT