This is very rare interview question I faced in one of my interview and
thought to write a quick article with reference of MSDN.
When SQL Server runs on a computer with more than one microprocessor or
CPU, it detects the best degree of parallelism, that is, the number of
processors employed to run a single statement, for each parallel plan
execution. You can use the max degree of parallelism option to limit the number
of processors to use in parallel plan execution. To enable the server to
determine the maximum degree of parallelism, set this option to 0, the default
value. Setting maximum degree of parallelism to 0 allows SQL Server to use all
the available processors up to 64 processors. To suppress parallel plan
generation, set max degree of parallelism to 1. Set the value to a number
greater than 1 to restrict the maximum number of processors used by a single
query execution. The maximum value for the degree of parallelism setting is
controlled by the edition of SQL Server, CPU type, and operating system. If a
value greater than the number of available processors is specified, the actual
number of available processors is used. If the computer has only one processor,
the max degree of parallelism value is ignored.
|
Use below guidelines to configure
1.
For servers that use more than eight processors, use the following
configuration:
MAXDOP=8
2.
For servers that use eight or fewer processors, use the following
configuration:
MAXDOP=0 to
N (N represents the number of processors)
Also, the maximum value of 8 that is mentioned in
these guidelines is applicable for typical SQL Server activity and the
overhead for the exchange operators that are used in parallel query plans.
You can vary this maximum value, depending on your specific application
patterns and the concurrent activity on the instance of SQL Server. For
example, consider the following situations:
·
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.
The max degree of parallelism option is an advanced option. If you are
using the sp_configure system stored procedure to change the setting, you can
change max degree of parallelism only when show advanced options is set to 1.
The setting takes effect immediately (without restarting the MSSQLSERVER
service).
The following example sets the max degree of parallelism option to 8.
sp_configure 'show
advanced options', 1;
GO
RECONFIGURE WITH
OVERRIDE;
GO
sp_configure 'max
degree of parallelism', 8;
GO
RECONFIGURE WITH
OVERRIDE;
GO
Using Management Studio
1.
In Object Explorer, right-click a server and select Properties.
2.
Click the Advanced node.
3.
In the Max Degree of Parallelism box, select the maximum number of
processors to use in parallel plan execution.
The default value of 0 uses all available
processors. Set max degree of parallelism to 1 to suppress parallel plan
generation. Set the value to a number greater than 1 to restrict the maximum
number of processors used by a single parallel query execution. If a value
greater than the number of available processors is specified, the actual
number of available processors is used. If the computer has only one
processor, the max degree of parallelism value is ignored.
Note: The max degree of parallelism
configuration option does not limit the number of processors that SQL Server
uses. To configure the number of processors that SQL Server uses, use the affinity
mask configuration option.
Affinity Mask Option
To carry out multitasking, Microsoft Windows 2000
and Windows Server 2003 sometimes move process threads among different
processors. Although efficient from an operating system point of view, this
activity can reduce SQL Server performance under heavy system loads, as each
processor cache is repeatedly reloaded with data. Assigning processors to
specific threads can improve performance under these conditions by
eliminating processor reloads and reducing thread migration across processors
(thereby reducing context switching); such an association between a thread
and a processor is called processor affinity.
Affinity
support for servers with 33 to 64 processors is only available on 64-bit
operating systems.
Changes to the affinity masks occur dynamically,
allowing for on-demand startup and shutdown of the CPU schedulers that bind
process threads within SQL Server. This can occur as conditions change on the
server. For example, if a new instance of SQL Server is added to the server,
it may be necessary to make adjustments to the affinity mask option to
redistribute processor load.
Modifications to the affinity bitmasks require SQL
Server to enable a new CPU scheduler and disable the existing CPU scheduler.
New batches can then be processed on the new or remaining schedulers.
To start a new CPU scheduler, SQL Server creates a
new scheduler and adds it to the list of its standard schedulers. The new
scheduler is considered only for the new incoming batches. Current batches
continue to run on the same scheduler. The workers migrate to the new
scheduler as they free up, or as new workers are created. Shutting down a
scheduler requires all batches on the scheduler to complete their activities
and exit. A scheduler that has been shut down is marked as offline so that no
new batch is scheduled on it.
Whether a new scheduler is added or removed, the
permanent system tasks such as lockmonitor, checkpoint, system task thread
(processing DTC), and signal process continue to run on the scheduler while
the server is operational. These permanent system tasks do not dynamically
migrate. To redistribute processor load for these system tasks across
schedulers, it is necessary to restart the SQL Server instance. If SQL Server
attempts to shut down a scheduler associated with a permanent system task,
the task continues to run on the offline scheduler (no migration). This
scheduler is bound to the processors in the modified affinity mask and should
not put any load on the processor it was affinitized with before the change.
Having extra offline schedulers, should not significantly affect the load of
the system. If this is not the case, a database server reboot is required to
reconfigure these tasks.
The I/O affinity tasks (such as lazywriter and
logwriter) are directly affected by the I/O affinity mask. If the lazywriter
and logwriter tasks are not affinitized, they follow the same rules defined
for the other permanent tasks such as lockmonitor or checkpoint.
The values for affinity mask are as follows:
1.
A one-byte affinity mask covers up to 8 CPUs in a multiprocessor
computer.
2.
A two-byte affinity mask covers up to 16 CPUs in a multiprocessor
computer.
3.
A three-byte affinity mask covers up to 24 CPUs in a multiprocessor
computer.
4.
A four-byte affinity mask covers up to 32 CPUs in a multiprocessor
computer.
5.
To cover more than 32 CPUs, configure a four-byte affinity mask for the
first 32 CPUs and up to a four-byte affinity64 mask for the remaining CPUs.
As an example of setting the affinity mask option,
if processors 1, 2, and 5 are selected as available with bits 1, 2, and 5 set
to 1 and bits 0, 3, 4, 6, and 7 set to 0, a hexadecimal value of 0x26 or the
decimal equivalent of 38 is specified. Number the bits from right to left.
The affinity mask option starts counting processors from 0 to 31, so that in
the following example the counter 1 represents the second processor on the
server.
sp_configure 'show
advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity
mask', 38;
RECONFIGURE;
GO
These are affinity mask values for an 8-CPU
system.
The affinity mask option is an advanced option. If
you are using the sp_configure system stored procedure to change the setting,
you can change affinity mask only when show advanced options is set to 1.
After executing the Transact-SQL RECONFIGURE command, the new setting takes
effect immediately without requiring a restart of the SQL Server instance.
Refer
below links for detailed information
|
||||||||||||||||||||||||||||
No comments:
Post a Comment