Max degree of parallelism (MAXDOP) in SQL server 2000/2008/2008R2/2012/2014/2014/2017.

As an SQL Server DBA, recently, I have given interview and interviewer asked below questions in Bangalore(India).

1. What is max degree of parallelism<MAXDOP> in SQL server 2000/2008/2008R2/2012/2014/2014/2017?
2. When SQL Server considers parallel execution plans for queries?
3. How to change max degree of parallelism in SQL server? 
4. Who can change the max degree of parallelism in SQL server? 
5. What permission required change max degree of parallelism in SQL server? 
6. Where we can set of max degree of parallelism in SQL server? 
7. What is the limitation of max degree of parallelism in SQL server?
8. In which scenario, we need to use max degree of parallelism option in SQL server?
9. Examples of max degree of parallelism?

What is max degree of parallelism<MAXDOP> in SQL server 2000/2008/2008R2/2012/2014/2014/2017?

A max degree of parallelism is a number of processor. which says, how many CPU or Microprocessor will use execution plan parallelly to execute the query.

When the SQL instance run the query in SSMS that use microprocessor or CPU to execute the query. At that time, CPU or microprocessor detect max degree of parallelism.

For example, if the max degree of parallelism value set as 4, and we are running any query, so the 4 CPU will parallel execute the execution plan of that query.

When SQL Server considers parallel execution plans for queries?

When run DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP, index data definition language (DDL) operations, parallel insert, online alter column, parallel stats collection, and static and keyset-driven cursor population.

How to change max degree of parallelism in SQL server?

Right Click on SQL Server Instance à Properties à Advance à max degree of parallelism à change the value  à Ok.

OR

USE AdventureWorks2012 

GO  

EXEC sp_configure 'show advanced options', 1; 

GO 

RECONFIGURE WITH OVERRIDE; 

GO 

EXEC sp_configure 'max degree of parallelism', 4; 

GO 

RECONFIGURE WITH OVERRIDE; 

GO

Who can change the max degree of parallelism SQL server? What permission required change max degree of parallelism SQL server?

User should have sysadmin and serveradmin fixed server roles.

What is the limitation of max degree of parallelism SQL server?

If the affinity mask set as default (option is checked), it will not restrict the number of processors available to SQL Server on symmetric multiprocessing (SMP) systems.

If affinity mask not set as default (option is Unchecked) it will restrict the number of processors available to SQL Server on symmetric multiprocessing (SMP) systems.

Where we can set of max degree of parallelism in SQL server?

We can set it on instance level and object level. In below example, we have set on object level.

In which scenario, we need to use max degree of parallelism option in SQL server?

As per the number of query- 

If you have very small number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a larger value.

For example, you can set the MAXDOP value to 16.  

If you a have very large number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a smaller value.

For example, you can set the MAXDOP value to 4. 

As per the Microsoft Stranded- 

MaxDop setting will vary depending upon the number of CPU cores:

CPU Cores (Total)	MaxDop
1	1
2-7	2
8-16	4
>16	8

* Assuming mixed SQL workloads, involving both OLTP and reporting.

Note Any value that you consider using should be thoroughly tested against the specific application activity or pattern of queries before you implement that value on a production server.

Examples:-

To go the table à expend à Indexes à select Index à right click on select index à Properties à Option à change the value (default value is 0).

Example - MAXDOP for Create Index: -

USE AdventureWorks2012; 

GO 

CREATE INDEX IX_Product_NewID ON Product (ID)  WITH (MAXDOP=4); 

GO 

Example - MAXDOP for Rebuild Index: -

USE AdventureWorks2012; 

GO 

Alter INDEX IX_Product_NewID ON Product  REBUILD WITH (MAXDOP=4); 

GO 

Example - MAXDOP for select command: -

USE AdventureWorks2012; 

GO 

Select * from Product (nolock)  ORDER BY ProductID

OPTION (MAXDOP 1)

Note: - If the MAXDOP is set for the query and query execution time is more then check the execution plan of that query, in the plan you can see the parallelism cost in %, and then increase or decrease MAXDOP value. 

Please add if you have some interesting fact on MAXDOP - Jainendra Verma

SQL Server : Some DBCC commands and other T-SQL Script for Performance Tuning in SQL Server

Few below important T-SQL Scripts and DBCC commands for Tuning in SQL Server. Using below queries you can tune the Database in SQL Server-

/*Returns size and fragmentation information for the data and indexes of the specified table or view*/

SELECT * FROM sys.dm_db_index_physical_stats

(DB_ID(N'HRMS_DB'), OBJECT_ID(N'Sparsed'), NULL, NULL , 'DETAILED');

/*This command is used to scan the current DB

and display the pages and extens and fragemention*/

DBCC SHOWCONTIG

/*This command is used to scan a particular table

and display pages,extens,avg size,row size,

and scanned pages of a table*/

DBCC SHOWCONTIG(tbl_Emp)

SELECT * FROM sys.dm_db_index_physical_stats('HRMS_DB','tbl_Emp' , NULL,NULL , 'LIMITED');

/* To display total pagesize,datapage size,used pages on a database*/

select * from sys.allocation_units

/*To display the rows spread accross the pages in a table*/

DBCC CHECKTABLE ('tbl_Emp')

/*To check the overall Database and tables with rows spread across the pages*/

DBCC CHECKDB (HRMS_DB)

/*DBCC CLEANTABLE reclaims space after a

variable-length column is dropped.

A variable-length column can be one of the following data types:

varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml.

The command does not reclaim space after a fixed-length column is dropped */

/*For the below example let's create a table

and insert some data and then drop the

column and then use the command and see the result*/

/*Step 1: Create table */

drop table dbo.CleanTableTest

CREATE TABLE dbo.CleanTableTest

    (FileName nvarchar(4000),

    DocumentSummary nvarchar(max),

    Document varbinary(max)

    );

/*Step 2: Insert the data into the table

using wizard or from the table which contains

large records */

/*Now Verify the current page counts

 and average space used */

 SELECT * FROM sys.dm_db_index_physical_stats

(DB_ID(N'HRMS_DB'), OBJECT_ID(N'dbo.CleanTableTest'), NULL, NULL ,'DETAILED');

/*Now find out from the output few columns like

avg_pagesize,record count,pagecount,max record byte size,

avg_record byte size*/

/*Step 3: drop the columns of the table*/

ALTER TABLE dbo.CleanTableTest

DROP COLUMN FileName, Document;

/*Now Check the size of the table*/

SELECT * FROM sys.dm_db_index_physical_stats

(DB_ID(N'HRMS_DB'), OBJECT_ID(N'dbo.CleanTableTest'), NULL, NULL ,'DETAILED');

/*Now Clean the table*/

/*Even though you drop the column the space

is still reserved so now take a screen shot

and test the min,max,avg,row sized */

/*(After DBCC CLEANTABLE now check the

space used the min,max,row sizes will be different*/

DBCC CLEANTABLE (HRMS_DB,'dbo.CleanTableTest');

/*Now check the space of the table*/

SELECT * FROM sys.dm_db_index_physical_stats

(DB_ID(N'HRMS_DB'), OBJECT_ID(N'dbo.CleanTableTest'), NULL, NULL ,'DETAILED');

DBCC CheckDB(HRMS_DB)

/*This Command is used Removes residual information left on database pages

because of data modification routines in SQL Server*/

/*Before this command check the DBSpace using

sp_helpdb HRMS_DB */

EXEC sp_clean_db_free_space

@dbname = N'HRMS_DB' ;

/*To get the complete infor of all the data type*/

EXEC sp_datatype_info

/*This Command display's all the objects that references the table tbl_Emp

like views,sp,indexes releated to this table*/

EXEC sp_depends @objname = N'tbl_Emp';

/*To Display the Memory Status*/

DBCC MemoryStatus

Note: Please do not run the below query on production server if it's not required. To run above queries we can tune the our database easily.