Parallel query processing in SQL Server

Task: to give a basic look at the subsystem for parallel query processing in SQL Server

/* Testing laptop: ProBook 6540b, Intel Core i5 */

USE [AdventureWorks2012]
GO

SELECT [cpu_count]                     AS 'Logical CPU Count',
       [hyperthread_ratio]             AS 'Hyperthread Ratio',
       [cpu_count]/[hyperthread_ratio] AS 'Physical CPU Count'
FROM   [sys].[dm_os_sys_info]
GO
/* Result:
   Logical CPU Count | Hyperthread Ratio | Physical CPU Count
   4                   4	           1
*/

Source code 1: System configuration

SELECT [name], [value]
FROM   [sys].[configurations]
WHERE  [name] = 'cost threshold for parallelism'
       OR
       [name] = 'max degree of parallelism'
GO
/* Result:
   name                           | value
   cost threshold for parallelism   5 => default value, i.e. 5 seconds
   max degree of parallelism        0 => default value, i.e. parallelism
                                         with an automatic count of
                                         processors
*/


SELECT   *
FROM     [Sales].[SalesOrderDetail]        AS [sod]
         INNER JOIN [Production].[Product] AS [p]
         ON [sod].[ProductID] = [p].[ProductID]
ORDER BY [Style]
GO

Source code 2: Parallel query processing providing that the cost threshold parameter equals to 5

Figure 1: Execution plan for parallel query processing regarding the cost threshold parameter

EXEC sp_configure 'show advanced option', '1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure "max degree of parallelism", 0
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure "cost threshold for parallelism", 10
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced option', '0'
RECONFIGURE WITH OVERRIDE
GO

SELECT [name], [value]
FROM   [sys].[configurations]
WHERE  [name] = 'cost threshold for parallelism'
       OR
       [name] = 'max degree of parallelism'
GO
/* Result:
   name                           | value
   cost threshold for parallelism   10 => custom value i.e., 10 seconds
   max degree of parallelism        0  => default value i.e., parallelism
                                          with an automatic count of
                                          processors
*/


SELECT   *
FROM     [Sales].[SalesOrderDetail]        AS [sod]
         INNER JOIN [Production].[Product] AS [p]
         ON [sod].[ProductID] = [p].[ProductID]
ORDER BY [Style]
GO


EXEC sp_configure 'show advanced option', '1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure "max degree of parallelism", 0
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure "cost threshold for parallelism", 5
RECONFIGURE WITH OVERRIDE
GO

SELECT [name], [value]
FROM   [sys].[configurations]
WHERE  [name] = 'cost threshold for parallelism'
       OR
       [name] = 'max degree of parallelism'
GO
/* Result:
   name                           | value
   cost threshold for parallelism   5 => default value i.e., 5 seconds
   max degree of parallelism        0 => default value i.e., parallelism
                                         with an automatic count of
                                         processors
*/

Source code 3: Serial query processing providing that the cost threshold parameter equals to 10

Figure 2: Execution plan for serial query processing regarding the cost threshold parameter

SELECT   *
FROM     [Sales].[SalesOrderDetail]
ORDER BY [ProductID]

SELECT   *
FROM     [Sales].[SalesOrderDetail]
ORDER BY [ProductID]
OPTION   (MAXDOP 1) -- Note: query hint for one processor
GO

Source code 4: Execution plan – costs comparison for parallel and serial with OPTION (MAXDOP 1) query processing

Figure 3: Execution plan - cost 40% for parallel vs. 60% for serial with OPTION (MAXDOP 1) query processing

Figure 4: Setting of physical and logical CPU count simulation

SELECT [cpu_count]                     AS 'Logical CPU Count',
       [hyperthread_ratio]             AS 'Hyperthread Ratio',
       [cpu_count]/[hyperthread_ratio] AS 'Physical CPU Count'
FROM   [sys].[dm_os_sys_info]
GO
/* Result:
   Logical CPU Count | Hyperthread Ratio | Physical CPU Count
   16                  4	           4
*/

Source code 5: Simulating 4 physical and 16 logical CPU count

Note: Express Edition of SQL Server does not support simulating physical CPU count

General recommendations:

• OLTP: on Pure OLTP system, where the transactions are smaller and queries are not long but very quick usually, set the “Maximum Degree of Parallelism” to 1 (one).
• Data-warehousing / Reporting server: as queries will be running for long time, it is advised to set the “Maximum Degree of Parallelism” to 0 (zero).
• Mixed System (OLTP & OLAP): the right balance has to be found. To set the “Maximum Degree of Parallelism” to 2, which means the query still uses parallelism but only on 2 CPUs. However, to keep the “Cost Threshold for Parallelism” very high.

Additional references:

• Execute T-SQL in Parallel
  by Kevin Kline
• Execute T-SQL Scripts in Parallel
  by James Ma
• Flash Tip: Simulating Parallel Plans
  by Microsoft TechNet
• How SQL Server 2005 Express Edition determines the CPU count and uses the CPUs during processing
  by Microsoft Support
• Parallel Data Warehouse
  by Microsoft SQL Server
• Parallel execution in SSIS
  by James Serra
• Parallel Query Processing
  by Microsoft MSDN
• SQL SERVER – CXPACKET – Parallelism – Usual Solution – Wait Type – Day 6 of 28
  by Pinal Dave
• SQL SERVER – Partition Parallelism Support in expressor 3.6
  by Pinal Dave
• SQL SERVER – Using MAXDOP 1 for Single Processor Query – SQL in Sixty Seconds #008 – Video
  by Pinal Dave
• SQL Server 2012’s Information on Parallel Thread Usage
  by Joe Sack
• SQLAuthority News – Download Whitepaper – Understanding and Controlling Parallel Query Processing in SQL Server
  by Pinal Dave