As an SQL
Server DBA, recently, I have given interview and interviewer asked below
questions in Bangalore(India).
- What is max
degree of parallelism<MAXDOP> in SQL server 2000/2008/2008R2/2012/2014/2014/2017?
- When SQL Server considers parallel execution plans for
queries?
- How to change max degree of parallelism in SQL server?
- Who can change
the max degree of parallelism in SQL server?
- What permission required change max
degree of parallelism in SQL server?
- Where we can set of max degree of parallelism in SQL server?
- What is the limitation of max degree of
parallelism in SQL server?
- In which scenario, we need to use max degree of parallelism option in SQL server?
- 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
