When considering a typical OLTP system consisting of lots of short, simple transactions, multiple CPUs are valuable in their ability to service lots of simultaneous threads from multiple users. In contrast, a typical OLAP (reporting / data warehouse) application consists of a smaller number of much larger queries. It follows that splitting up a query into multiple chunks, with each chunk running on a separate CPU is more suited to an OLAP system whereby large amounts of resources can be used in speeding up the execution time of large queries.
During query compilation, SQL Server will decide whether or not to use a parallel query if the MAXDOP setting allows and the estimated cost of executing the query in a serial manner on a single CPU exceeds a cost threshold. By default, MAXDOP is 0, meaning that SQL Server is left to decide on the appropriate number of CPUs to use. This value can be set to 1, effectively disabling parallel queries, or a specific number which limits the amount of CPUs that can be used.
In some cases, parallel queries in an OLTP environment are chosen by SQL Server to circumvent poor design and maintenance practices, most often as a result of missing indexes or out of date statistics. For example, SQL Server may decide to perform an index scan rather than a lookup, in which case it may parallelize the query. A high incidence of parallel queries is typically accompanied by a large number of CXPACKET waits.
In typical OLTP environments, MAXDOP is often set to 1 to limit the CPU and memory impact from parallel queries. In such cases, the question needs to be asked as to why SQL Server is choosing parallel queries, that is, are the indexes being designed and maintained appropriately?
One of the downsides from setting MAXDOP to 1 is that certain operations, such as index rebuilds, benefit greatly from parallelism, but are unable to do so with a MAXDOP 1 setting. In such cases, the MAXDOP setting can be specified at a statement level. For example, the CREATE INDEX command, an example of which is presented here, accepts a MAXDOP parameter:
— Use a MAXDOP hint to override the default server MAXDOP setting
CREATE NONCLUSTERED INDEX [IX_Address_StateProvinceID]
ON [Person].[Address] ([StateProvinceID] ASC)
WITH (MAXDOP=0)
GO
In this example, we specify MAXDOP = 0 to override the instance
default MAXDOP setting, and thereby permit the index creation to be
parallelized if SQL Server decides that is the best approach.
Like all configuration options, Max Degree of Parallelism can be
set using sp_configure, or via the Advanced page of a Server’s properties
window in SQL Server Management Studio, as shown in figure 1.
Figure 1: The Advanced tab of the Server Properties
window allows changes to Server configuration settings such as Max Degree of
Parallelism and the Cost Threshold
Cost Threshold for Parallelism
If MAXDOP is left at the default value, or set to a number more
than 1, the threshold at which SQL Server will consider a parallel plan can be
set through the Cost Threshold for Parallelism option.
This value, specified in seconds, represents the estimated time the query would
take if executed serially on a single CPU. Queries estimated to take longer
than this will be considered slaterpharmacy.com/zithromax for parallelism. The default value for this
setting is 5.
In some cases, increasing this value is a better alternative to
setting MAXDOP to 1 when dealing with a large number of (unwanted) parallel queries.
]]>