Updating remote query timeout
Be sure to properly plan the maintenance and associated MAXDOP settings if the server is not a dedicated SQL Server instance, but contains multiple SQL Server instances or a multi-purpose server with other applications.
Be sure when changing MAXDOP on a shared server you will not cause CPU issues for other applications.
Person REBUILD OPTION (MAXDOP 8)GOALTER INDEX ALL ON Person.
Person REBUILD OPTION (MAXDOP 16)GOALTER INDEX ALL ON Person.
We can check the degree of parallelism currently set instance-wide on a SQL Server instance by running the following command: As a database administrator, our goal is to optimize SQL Server for the day to day workload.
Before beginning, we'll briefly review the term maximum degree of parallelism for processors.
The maximum degree of parallelism (MAXDOP) is defined as the maximum number of logical processors (CPUs) that SQL Server can use in a single query.
In test environments when SQL Server is configured system-wide (sp_configure) with a MAXDOP value of 1, 2, or 4 due to SQL Server workload requirements, there can be improvements.
Test in your environment to determine which MAXDOP value performs the best.
See the sample index rebuild script below using the MAXDOP query hint.