Share via

Max degree of parallelism (MAXDOP) on SQL Failovered clustered instances.

Vijay Kumar 161 Reputation points
2020-08-24T18:46:22.347+00:00

We have 4 node SQL cluster. Each node has 4 physical processors with 18 cores each, 36 logical processors each. Cluster is running 23 instances, we are going to set "Cost Threshold of Parallelism" = some higher value (50). Currently this is set to 5. Just want to know how this can impact on cluster CPU resources. I can see there's several blogs related to parallelism and MAXDOP but not specific to clusters. Anyone there who can guide on this or any documentation is most welcomed.

Thanks

Vijay

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,961 questions
Windows Server Clustering
Windows Server Clustering
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.Clustering: The grouping of multiple servers in a way that allows them to appear to be a single unit to client computers on a network. Clustering is a means of increasing network capacity, providing live backup in case one of the servers fails, and improving data security.
1,007 questions
0 comments
Accepted answer
  1. Tom Phillips 17,736 Reputation points
    2020-08-24T20:59:03.84+00:00

    There is not really a difference in clusters vs multiple instances per node. That is what you are doing.
    I suggest you read:
    https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-database-engine-instances-sql-server?view=sql-server-ver15

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Cris Zhan-MSFT 6,631 Reputation points
    2020-08-25T08:42:11.703+00:00

    Hi,

    The cost threshold for parallelism option specifies the threshold at which SQL Server creates and runs parallel plans for queries. check the Configure the max degree of parallelism Server Configuration Option

    Multiple SQL Server instances on one server will share the same CPU, memory, network and storage. What is the optimal configuration for your environment requires a lot of tuning and experimentation.

    ===============================================

    If the response helped, do "Accept Answer" and upvote it.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.