Updating remote query timeout

06-Apr-2020 07:22

Earlier in Figure 1, the SQL Server environment is configured using a MAXDOP value of 1, through testing we found changing the MAXDOP to a value of 8 during index maintenance yielded the best performance.Let's demonstrate how to setup a SQL Server Agent Job to do so: 1.Index Maintenance is typically performed through a SQL Server Agent Job on a scheduled basis.The maintenance can be custom scripts or commonly a SQL Server Maintenance Plan.We should take advantage of increasing the "maximum degree of parallelism" (MAXDOP) during maintenance for rebuilding indexes.Figure 2 below is used to measure performance differences by changing the MAXDOP during index rebuilds for a 30MB table in the Adventure Works database.

Below is an example to change it instance-wide to a value of 8 without requiring the MAXDOP hint.

If you believe there could be an issue, try adjusting the maintenance schedule or other system processing to reduce processor utilization between different applications.

Need USB Driver Downloads for Windows 10, Windows 8, Windows 7, Vista and XP?… continue reading »

Read more