認識平行(parallelism)處理，以MAXDOP、cost threshold for parallelism與max degree of parallelism選項為例

擁有多個處理器 (CPU) 的伺服器，是有機會可以使用多個執行緒來執行平行處理作業。

理想的願景是：線性延展性，例如：
(1) 2 * CPU = 1/2 回應時間
(2) 2 * 資料量 + 使用 2 * CPU = 相同的回應時間


但是，平行處理是需要額外付出成本，例如：
(1) 增加執行時的額外負載。
(2) 可能會減少CPU的總處理能力。

所以，在多個處理器 (CPU) 的伺服器上，以實際測試的效能結果來看：1+1，的確是不等於 2。

---

SQL Server 提供平行查詢，讓擁有多個處理器 (CPU) 的電腦，也能獲得最佳的查詢執行和索引作業。
因為 SQL Server 可利用數個作業系統執行緒平行地執行查詢或索引作業，所以可快速而有效率地完成作業。

平行查詢實際所使用的執行緒數目，是在查詢計畫執行初始化時，由計畫的複雜度與平行處理原則的程度決定。
平行處理原則的程度決定將要使用的 CPU 最大數目，而不是將要使用的執行緒數目。

平行處理原則的程度值是在伺服器層級設定，可以使用 sp_configure 系統預存程序修改。
您可以指定 MAXDOP 查詢提示或 MAXDOP 索引選項，來覆寫個別查詢或索引陳述式的這個值。

如果下列任何條件為真，則 SQL Server 查詢最佳化工具不使用平行執行計畫進行查詢：
1. 查詢的序列執行成本不夠高，無法考量替代平行執行計畫。
2. 序列執行計畫被認為比特定查詢之任何可能的平行執行計畫更快。

3. 此查詢包含無法平行執行的純量或關聯式運算子。特定運算子可能造成查詢計畫的一個區段以序列模式執行，或整個計畫以序列模式執行。

在執行 SQL Server Enterprise 的多處理器電腦上，索引陳述式可能會如同其他查詢般，使用多個處理器來執行與索引陳述式相關聯的掃描、排序和索引作業。

執行單一索引陳述式所用的處理器數目，取決於 max degree of parallelism 組態選項、目前的工作負載以及索引統計資料。

max degree of parallelism 選項會決定用於執行平行計畫的最大處理器數目。
如果 SQL Server Database Engine 偵測到系統忙碌中，在陳述式執行開始之前，會先自動降低索引作業之平行處理原則的程度。

如果非資料分割索引的前端索引鍵資料行具有有限的相異值數目，或者每個相異值的頻率具有大幅差異，Database Engine 也可能會降低平行處理原則的程度。

查詢最佳化工具所使用的處理器數目通常可以提供最佳的效能。

然而，諸如建立、重建、卸除非常大的索引都需要大量的資源，並可能在索引作業期間造成其他應用程式和資料庫作業的資源不足。

當發生此問題時，您可以指定 MAXDOP 索引選項和限制索引作業要使用的處理器數目，藉以手動設定執行索引陳述式要使用的最大處理器數目。

---

環境說明：
1. Windows Server 2008 R2 (x64) Enterprise Edition - 版本：6.1.7600。
2. SQL Server 2008 (SP1) (x64) Enterprise Editon - 版本：10.0.2531.0。

3. 四核心的 CPU。
4.設定最大可用的記憶體是：1024 MB。

-- 準備環境
-- 建立資料庫
CREATE DATABASE parallelism
GO
-- 建立資料表
CREATE TABLE parallelism.dbo.tbMax
(sid INT identity PRIMARY KEY, cDateTime datetime,cData char(8000))
GO
-- 新增資料列
SET NOCOUNT ON
GO
DECLARE @cnt INT=1
WHILE @cnt<=50000 
BEGIN  
INSERT parallelism.dbo.tbMax VALUES( GETDATE() ,GETDATE())  
SET @cnt+=1 
END   
-- 5 萬筆 SELECT COUNT(*) FROM parallelism.dbo.tbMax 

---

--EX1. 使用「查詢提示(Query Hints)」：MAXDOP

MAXDOP 針對指定這個選項的查詢覆寫 sp_configure 和資源管理員的 max degree of parallelism 組態選項。 MAXDOP 查詢提示可能會超過使用 sp_configure 所設定的值。

如果 MAXDOP 超過使用資源管理員所設定的值，Database Engine 就會使用＜ALTER WORKLOAD GROUP (Transact-SQL)＞中所描述的資源管理員 MAXDOP 值。
當您使用 MAXDOP 查詢提示時，適用所有搭配 max degree of parallelism 組態選項使用的語意規則。

--01 預設值：採取：平行查詢執行計畫，耗用成本：334.553
/*
採取：平行查詢執行計畫，可以使用一個以上的執行緒。
使用到「平行處理原則(蒐集資料流)」運算子。
*/
SELECT * FROM parallelism.dbo.tbMax
ORDER BY cDateTime desc

--02 設定查詢提示 MAXDOP 為 1，採取：序列執行計畫，設定耗用成本：603.74
/*
設定查詢提示 MAXDOP 為 1
採取：序列執行計畫，執行時只會使用一個執行緒。
無法使用「平行處理原則(蒐集資料流)」運算子。
*/
SELECT * FROM parallelism.dbo.tbMax
ORDER BY cDateTime desc
OPTION (MAXDOP 1)

---

--EX2. 伺服器屬性的設定值：cost threshold for parallelism

cost threshold for parallelism 選項 使用 cost threshold for parallelism 選項指定 Microsoft SQL Server 為查詢建立及執行平行計畫的臨界值。

唯有執行同一查詢的序列計畫的估計成本高於 cost threshold for parallelism 中設定的值時，SQL Server 才會建立及執行查詢的平行計畫。

成本是指在特定硬體組態下，執行序列計畫所需估計的經過時間 (以秒為單位)。只有在對稱式多重處理器上才應該設定 cost threshold for parallelism。

---

通常較長的查詢比較適合平行計畫；
其效能「優點」：
就是不需要額外的時間來初始化、同步處理及終止平行計畫。


在執行混合較短及較長的查詢時，就會啟用 cost threshold for parallelism 選項。 較短的查詢執行序列計畫，而較長的查詢使用平行計畫。

---

cost threshold for parallelism 的值可判斷哪些查詢是短的，所以要使用序列計畫來執行。 


在某些狀況下，即使查詢的成本計畫小於目前的 cost threshold for parallelism 值，還是會選擇平行計畫。


之所以會發生這種情形，是因為在決定要使用平行或序列計畫時，所依據的成本預估值是在完成整體最佳化之前提供的。

cost threshold for parallelism 選項可設成從 0 到 32767 的任何值。預設值是 5。
在下列情況下，SQL Server 會忽略 cost threshold for parallelism 值：
1. 您的電腦只有一個處理器。
2. 因為 affinity mask 組態選項的關係，只有一個 CPU 可供 SQL Server 使用。
3. max degree of parallelism 選項設為 1。

 cost threshold for parallelism 選項是進階選項。如果您是使用 sp_configure 系統預存程序來變更設定，只有當 show advanced 選項設定為 1 時，才能變更 cost threshold for parallelism。

這個設定會立即生效 (不需重新啟動伺服器)。

--01 查詢 cost threshold for parallelism 選項，其預設值為：5。
SELECT * FROM sys.configurations
WHERE name = 'cost threshold for parallelism'

--02 調整 cost threshold for parallelism 選項值為：700。
EXEC sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
EXEC sp_configure 'cost threshold for parallelism', 700;
GO
reconfigure;
GO

--03 查詢 cost threshold for parallelism 選項值為：700。
SELECT * FROM sys.configurations
WHERE name = 'cost threshold for parallelism'

--04 採取：序列執行計畫，耗用成本：603.74
/*
採取：序列執行計畫，執行時只會使用一個執行緒。

因為設定 cost threshold for parallelism 選項值為：700。
但此程式的耗用成本為：603.74，無法使用「平行處理原則(蒐集資料流)」運算子。
*/
SELECT * FROM parallelism.dbo.tbMax
ORDER BY cDateTime desc

--05 調整 cost threshold for parallelism 選項值為預設值：5。
EXEC sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
EXEC sp_configure 'cost threshold for parallelism', 5;
GO
reconfigure;
GO

-- 查詢 cost threshold for parallelism 選項值為：5。
SELECT * FROM sys.configurations
WHERE name = 'cost threshold for parallelism'

--06 採取：平行查詢執行計畫，耗用成本：334.553
/*
採取：平行查詢執行計畫，可以使用一個以上的執行緒。
使用到「平行處理原則(蒐集資料流)」運算子。
*/
SELECT * FROM parallelism.dbo.tbMax
ORDER BY cDateTime desc

---

--EX3. 設定 cost threshold for parallelism 超過 2 秒以上，就建立及執行查詢的平行計畫

成本是指在特定硬體組態下，執行序列計畫所需估計的經過時間 (以秒為單位)。只有在對稱式多重處理器上才應該設定 cost threshold for parallelism。

--01 查詢 cost threshold for parallelism 選項，其預設值為：5。
SELECT * FROM sys.configurations
WHERE name = 'cost threshold for parallelism'

--02 採取：序列執行計畫，耗用成本：2.12348
USE AdventureWorks2008
GO
SELECT TransactionID
FROM Production.TransactionHistory
WHERE TransactionID >185000
ORDER BY TransactionType DESC

--02 調整 cost threshold for parallelism 選項值為：2。
EXEC sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
EXEC sp_configure 'cost threshold for parallelism', 2;
GO
reconfigure;
GO

--03 查詢 cost threshold for parallelism 選項值為：2。
SELECT * FROM sys.configurations
WHERE name = 'cost threshold for parallelism'

--04 採取：平行查詢執行計畫，耗用成本：1.34245
/*
採取：平行查詢執行計畫，可以使用一個以上的執行緒。
使用到「平行處理原則(蒐集資料流)」運算子。
*/
USE AdventureWorks2008
GO
SELECT TransactionID
FROM Production.TransactionHistory
WHERE TransactionID >185000
ORDER BY TransactionType DESC

--05 調整 cost threshold for parallelism 選項值為預設值：5。
EXEC sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
EXEC sp_configure 'cost threshold for parallelism', 5;
GO
reconfigure;
GO

-- 查詢 cost threshold for parallelism 選項值為：5。
SELECT * FROM sys.configurations
WHERE name = 'cost threshold for parallelism'

---

--EX4. 伺服器屬性的設定值：max degree of parallelism 選項

SQL Server 在具有多個微處理器或 CPU 的電腦上執行時，會偵測平行處理原則的最佳程度，也就是說，針對每一個平行計畫的執行，執行單一陳述式所要採用的處理器個數。您可以使用 max degree of parallelism 選項來限制要用於平行計畫執行的處理器數目。

預設值為 0 會使用所有可以使用的處理器。將 max degree of parallelism 設成 1 可抑制產生平行計畫。將這個值設成大於 1 的數字 (最大值 64)，則會限制單一查詢執行所使用的最大處理器個數。 如果指定的數值大於可用的處理器數目，就會使用可用處理器的實際數目。

如果電腦只有一個處理器，則會忽略 max degree of parallelism 值。

覆寫 max degree of parallelism SQL Server 會針對查詢、索引資料定義語言 (DDL) 作業，以及靜態和索引鍵集驅動資料指標擴展，考慮進行平行執行計畫。 您可以在查詢陳述適中指定 MAXDOP 查詢提示，來覆寫查詢中的 max degree of parallelism 值。

建立或重建索引的索引作業，或者卸除叢集索引的索引作業，都需要大量資源。您可以在索引陳述式中指定 MAXDOP 索引選項，覆寫索引作業中的 max degree of parallelism 值。

MAXDOP 值會在執行時套用至陳述式，且不會儲存在索引中繼資料內。 除了查詢作業和索引作業外，此選項也會控制 DBCC CHECKTABLE、DBCC CHECKDB 和 DBCC CHECKFILEGROUP 的平行處理原則。您可以使用追蹤旗標 2528，來停用這些陳述式的平行執行計畫。

---1 查詢 max degree of parallelism 選項，其預設值為 0，表示可以使用所有的處理器。
SELECT * FROM sys.configurations
WHERE name ='max degree of parallelism'

--02 採取：平行查詢執行計畫，耗用成本：334.553
/*
採取：平行查詢執行計畫，可以使用一個以上的執行緒。
使用到「平行處理原則(蒐集資料流)」運算子。
*/
SELECT * FROM parallelism.dbo.tbMax
ORDER BY cDateTime desc
--03 調整 max degree of parallelism 選項值為：1，則會限制單一查詢執行所使用的最大處理器個數為：1。
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

--04 採取：序列執行計畫，耗用成本：603.74
/*
採取：序列執行計畫，執行時只會使用一個執行緒。

因為設定  max degree of parallelism 選項值為：1，則會限制單一查詢執行所使用的最大處理器個數為：1。
無法使用「平行處理原則(蒐集資料流)」運算子。
*/
SELECT * FROM parallelism.dbo.tbMax
ORDER BY cDateTime desc

--05 調整 max degree of parallelism 選項為預設值：0，表示可以使用所有的處理器。
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO

--06 採取：平行查詢執行計畫，耗用成本：334.553
/*
採取：平行查詢執行計畫，可以使用一個以上的執行緒。
使用到「平行處理原則(蒐集資料流)」運算子。
*/
SELECT * FROM parallelism.dbo.tbMax
ORDER BY cDateTime desc

---

參考資料：
cost threshold for parallelism 選項 http://technet.microsoft.com/zh-tw/library/ms188603.aspx

max degree of parallelism 選項 http://msdn.microsoft.com/zh-tw/library/ms181007.aspx

平行查詢處理 http://msdn.microsoft.com/zh-tw/library/ms178065.aspx

平行查詢範例 http://msdn.microsoft.com/zh-tw/library/ms175097.aspx

平行程度 http://msdn.microsoft.com/zh-tw/library/ms188611.aspx

查詢提示 (Transact-SQL) http://technet.microsoft.com/zh-tw/library/ms181714.aspx

設定平行索引作業 http://msdn.microsoft.com/zh-tw/library/ms189329.aspx

Parallelism http://blogs.msdn.com/craigfr/archive/tags/Parallelism/default.aspx

Parallel Query Execution Presentation http://blogs.msdn.com/craigfr/archive/2007/04/17/parallel-query-execution-presentation.aspx