Tag: MAXDOP

What is Max Degree of Parallelism?
Degree of parallelism is the number of processors that are assigned to run a single SQL statement. SQL Server manages to detect the best degree of parallelism for each query to generate the execution plan. SQL Server may use all the procedure in case if it required. If you do not want SQL Server to use all of the installed processors in your operations, you can limit the use of processors by configuring the processor value to a lower number than the actual number of processors installed. For instance, if a server has four processors and you want to use only three for parallelism, then you should set the max degree of parallelism option to 3.

The default option, 0, uses the actual number of processors installed in the system for the parallel execution plans of queries and index operations.

<br />
sp_configure 'max degree of parallelism', 6;<br />
GO<br />
RECONFIGURE WITH OVERRIDE;<br />
GO<br />

MAXDOP
Maxdop is a query hint. When you want to use a specific number of processors for an individual query or index operation, you can use the MAXDOP query hint or index option to provide better control. MAXDOP overrides the settings defined in the max degree of parallelism option. The use of MAXDOP is limited to the execution of a query that contains the MAXDOP query hint or index option.

SQL Server 2016 introduces a new option to limit the number of processors assigned for the DBCC CHECKDB statement, overriding the instance level MAXDOP configuration. You can use this option in case if you want to use all the compute power of the server or you want to use the minimum compute power of the server.

In the below example I am specifying the MAXDOP hint as 5 which will internally asks Database Engine to generate the execution plan to use the 5 processors.

<br />
select @@SPID<br />
DBCC CHECKDB (AdventureWorks2012) WITH MAXDOP = 5;<br />

You can check the number of processors user by SQL Server by executing the below query.

<br />
select Session_id , scheduler_id  from sys.dm_os_tasks<br />
where session_id = &lt;SPID&gt;<br />

You can execute the below script to identify the optimal MAX DOP setting and set the MAX DOP as using SP_Configure procedure.

<br />
select case<br />
      when cpu_count / hyperthread_ratio &gt; 8 then 8<br />
      else cpu_count / hyperthread_ratio<br />
      end as optimal_maxdop_setting<br />
      from sys.dm_os_sys_info;</p>
<p>-- Script to set the maxDOP<br />
sp_configure 'show advanced options', 1;<br />
GO<br />
RECONFIGURE WITH OVERRIDE;<br />
GO<br />
sp_configure 'max degree of parallelism', &lt;OutPut of the above script&gt;;<br />
GO<br />
RECONFIGURE WITH OVERRIDE;<br />
GO<br />

 

CHECKDB in the AI era: your first data-quality gate

Corruption doesn’t just break queries anymore — a corrupt page that feeds an embedding pipeline becomes a corrupt vector, and no reranker will save you. DBCC CHECKDB is still the cheapest data-quality gate you own. Run it on a schedule, bound it with MAXDOP so it doesn’t eat the server, and treat the rest of your AI data hygiene the same way: Free Toolkits — AI-Ready Data Checklist