Tag Archives: MAXDOP

SQL Server 2016 : DBCC CheckDB with MaxDop Option

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.

sp_configure 'max degree of parallelism', 6;
GO
RECONFIGURE WITH OVERRIDE;
GO

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.

select @@SPID
DBCC CHECKDB (AdventureWorks2012) WITH MAXDOP = 5;

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

select Session_id , scheduler_id  from sys.dm_os_tasks
where session_id = <SPID>

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

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

 

Advertisements

Max Degree of Parallelism and MAXDOP

Max Degree of Parallelism
If you do not want to use all of the installed processors in your SQL Server 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.

sp_configure ‘max degree of parallelism’, 6;
GO
RECONFIGURE WITH OVERRIDE;
GO

MAXDOP
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.

Use below extended procedure to get the number of processors.
EXECUTE xp_msver ‘ProcessorCount’

Example
SELECT
database_id AS databaseID
, QUOTENAME(DB_NAME(database_id)) AS ‘databaseName’
, [OBJECT_ID] AS objectID
, index_id AS indexID
, partition_number AS partitionNumber
, avg_fragmentation_in_percent AS fragmentation
, page_count
, 0 AS ‘defragStatus’ /* 0 = unprocessed, 1 = processed */
FROM sys.dm_db_index_physical_stats (@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode)
WHERE avg_fragmentation_in_percent >= 30
And index_id > 0
And page_count > 8 — ignore objects with less than 1 extent
And index_level = 0 — leaf-level nodes only, supports @scanMode
OPTION (MaxDop 2);