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);
aiDeeva.com 