SQL Server並行運算學習總結教程

技術準備

同前幾篇一樣，基於SQL Server2008R2版本，利用微軟的一個更簡潔的案例庫(Northwind)進行解析。

一、並行運算子

在我們日常所寫的T-SQL語句，並不是所有的最優執行計畫都是一樣的，其最優的執行計畫的形成需要多方面的評估才可以，大部分根據SQL Server本身所形成的統計資訊，然後對形成的多個執行計畫進行評估，進而選出最優的執行方式。

在SQL Server根據庫內容形成的統計資訊進行評估的同時，還要參照當前啟動並執行硬體資源，有時候它認為最優的方案可能當前硬體資源不支援，比如：記憶體限制、CPU限制、IO瓶頸等，所以執行計畫的優劣還要依賴於底層硬體。

當SQL Server發現某個處理的資料集比較大，耗費資源比較多時，但此時硬體存在多顆CPU時，SQL Server會嘗試使用並行的方法，把資料集拆分成若干個，若干個線程同時處理，來提高整體效率。

在SQL Server中可以通過如下方法，設定SQL Server可用的CPU個數

 

 

預設SQL Server會自動選擇CPU個數，當然不排除某些情況下，比如高並發的生產環境中，防止SQL Server獨佔所有CPU，所以提供了該配置的介面。

還有一個系統參數，就是我們熟知的MAXDOP參數，也可以更改此系統參數配置，該配置也可以控制每個運算子的並行數(記住：這裡是每個運算子的，而非全部的)，我們來查看該參數

 

 

這個並行運算子的設定數，指定的是每個運算子的最大並行數，所以有時候我們利用查看系統任務數的DMV視圖sys.dm_os_tasks來查看，很可能看到大於並行度的線程資料量，也就是說線程資料可能超過並行度，原因就是兩個運算子重新劃分了資料，分配到不同的線程中。

這裡如沒特殊情況的話，建議採用預設設定最佳。

我們舉一個分組的例子，來理解並行運算

 

 

採用並行運算出了提升效能還有如下幾個優點：

不依賴於線程的數量，在運行時自動的添加或移除線程，在保證系統正常吞吐率的前提下達到一個效能最優值

能夠適應傾斜和負載平衡，比如一個線程運行速度比其它線程慢，這個線程要掃描或者啟動並執行數量會自動減少，而其它跑的快的線程會相應提高任務數，所以總的執行時間就會平穩的減少，而非一個線程阻塞整體效能。

下面我們來舉個例子，詳細的說明一下

並行計劃一般應用於資料量比較大的表，小表採用串列的效率是最高的，所以這裡我們建立一個測試的大表，然後插入部分測試資料，我們插入250000行，整體表超過6500頁，指令碼如下

 

--建立表，建立主鍵，形成叢集索引

CREATE TABLE BigTable

(

[KEY] INT,

DATA INT,

PAD CHAR(200),

CONSTRAINT [PK1] PRIMARY KEY ([KEY])

)

GO

--批量插入測試資料250000行

SET NOCOUNT ON

DECLARE @i INT

BEGIN TRAN

SET @i=0

WHILE @i<250000

BEGIN

INSERT BigTable VALUES(@i,@i,NULL)

SET @i=@i+1

IF @i%1000=0

BEGIN

COMMIT TRAN

BEGIN TRAN

END

END

COMMIT TRAN

GO

 

我們來執行一個簡單查詢的指令碼

SELECT [KEY],[DATA]

FROM BigTable

 

 

這裡對於這種查詢指令碼，沒有任何篩選條件的情況下，沒必要採用並行掃描，因為採用串列掃描的方式得到資料的速度反而比並行掃描擷取的快，所以這裡採用了clustered scan的方式，我們來加一個篩選條件看看

SELECT [KEY],[DATA]

FROM BigTable

WHERE DATA<1000

 

 

對於這個有篩選條件的T-SQL語句，這裡SQL Server果斷的採用的並行運算的方式，叢集索引也是並行掃描，因為我電腦為4個邏輯CPU(其實是2顆物理CPU，4線程)，所以這裡使用的是4線程並行掃描四次表，每個線程掃描一部分資料，然後匯總。

 

 

這裡總共用了4個線程，其中線程0為調度線程，負責調度所有的其它線程，所以它不執行掃描，而線程1到線程4執行了這1000行的掃描!當然這裡資料量比較少，有的線程分配了0個任務，但是總得掃描次數為4次，所以這4個線程是並行的掃描了這個表。

可能上面擷取的結果比較簡單，有的線程任務還沒有給分配滿，我們來找一個相對稍複雜的語句

SELECT MIN([DATA])

FROM BigTable

 

 

這個執行計畫挺簡單的，我們依次從右邊向左分析，依次執行為：

4個並行叢集索引掃描——>4個線程並行擷取出前當前線程的最小數——>執行4個最小數匯總——>執行流彙總擷取出4個數中的最小值——>輸出結果項。

 

 

然後4個線程，每個線程一個流彙總擷取當前線程的最小數

 

 

然後，將這個四個最小值經過下一個“並行度”的運算子匯聚成一個表

 

 

然後下一個就是流彙總，從這個4行資料中擷取出最小值，進行輸出，關於流彙總我們上一篇文章中已經介紹

 

 

以上就一個一個標準的多線程並行運算的過程。

上面的過程中，因為我們使用的並行叢集索引掃描資料，4個線程基本上是平均分攤了任務量，也就是說每個線程掃描的資料量基本相等，下面我們將一個線程使其處於忙碌狀態，看看SQL Server會不會將任務動態平攤到其它幾個不忙碌的線程上。

我們在來添加一個大資料量表，指令碼如下

SELECT [KEY],[DATA],[PAD]

INTO BigTable2

FROM BigTable

我們來寫一個大量語句的查詢，使其佔用一個線程，並且我們這裡強制指定只用一個線程運行

SELECT MIN(B1.[KEY]+B2.[KEY])

FROM BigTable B1 CROSS JOIN BigTable2 B2

OPTION(MAXDOP 1)

以上代碼想跑出結果，就我這個電腦配置估計少說五分鐘以上，並且我們還強行串列運算，速度可想而知，

我們接著執行上面的擷取最小值的語句，查看執行計畫

SELECT MIN([DATA])

FROM BigTable

我們在執行計畫中，查看到了叢集索引掃描的線程數量

 

 

可以看到，線程1已經數量減少了近四分之的資料，並且從線程1到線程4，所掃描的資料量是依次增加的。

我們上面的語句很明確的指定了MAXDOP為1，理論上講只可能會影響一個線程，為什麼這幾個線程都影響呢?其實這個原因很簡單，我的電腦是物理CPU只有兩核，所謂的線程數只是超執行緒，所以非傳統意義上的真正的4核心數，所以線程之間是互相影響的。

我們來看一個並行串連操作的例子，我們查看並行嵌套迴圈是怎樣利用資源的

SELECT B1.[KEY],B1.DATA,B2.DATA

FROM BigTable B1 JOIN BigTable2 B2

ON B1.[KEY]=B2.[KEY]

WHERE B1.DATA<100

上面的語句中，我們在BigTable中Key列存在叢集索引，而查詢條件中DATA列不存在，所以這裡肯定為叢集索引掃描，對資料進行尋找

來看執行計畫

 

 

我們依次來分析這個流程，結合文本的執行計畫分析更為準確，從右邊依次向左分析

 

 

第一步，就是利用全表通過叢集索引掃描擷取出資料，因為這裡採用的並行的叢集索引掃描，我們來看並行的線程數和掃描數

 

 

四個線程掃描，這裡線程3擷取出資料100行資料。

然後將這100行資料，重新分配線程，這裡每個線程平均分配到25行資料

 

 

到此，我們要擷取的結果已經均分成4個線程共同執行，每個線程分配了25行資料，下一步就是交給嵌套迴圈串連了，因為我們上面的語句中需要從BigTable2中擷取資料行，所以這裡選擇了嵌套迴圈，依次掃描BigTable2擷取資料。

關於嵌套迴圈串連運算子，可以參照我的第二篇文章。

 

 

我們知道這是外表的迴圈數，也就是說這裡會有4個線程並存執行嵌套迴圈。如果每個線程均分25行，資料那麼內部表就要執行

4*25=100次。

 

 

然後，執行完，嵌套掃描擷取結果後，下一步就是，將各個線程執行的結果通過並行運算子匯總，然後輸出

 

 

上述過程就是一個並行嵌套迴圈的執行流程。充分利用了四核的硬體資源。

參考文獻

微軟聯機叢書邏輯運算子和物理運算子引用

參照書籍《SQL.Server.2005.技術內幕》系列

結語

此篇文章先到此吧，文章短一點，便於理解掌握，後續關於並行操作還有一部分內容，後續文章補充吧，本篇主要介紹了查詢計劃中的並行運算子，下一篇我們接著補充一部分SQL Server中的並行運算，然後分析下我們日常所寫的增刪改這些操作符的最佳化項，有興趣可提前關注，關於SQL Server效能調優的內容涉及面很廣，後續文章中依次展開分析。

有問題可以留言或者私信，隨時恭候有興趣的童鞋加入SQL SERVER的深入研究。共同學習，一起進步。

 

SQL Server並行運算學習總結教程二

技術準備

同前幾篇一樣，基於SQL Server2008R2版本，利用微軟的一個更簡潔的案例庫(Northwind)進行解析。

內容

文章開始前，我們先來回顧上一篇中介紹的並行運算，來看文章最後介紹的並行運算語句：

SELECT B1.[KEY],B1.DATA,B2.DATA
FROM BigTable B1 JOIN BigTable2 B2
ON B1.[KEY]=B2.[KEY]
WHERE B1.DATA<100

 

 

上面是詳細的執行計畫，從右邊依次向左執行，上圖中有一個地方很有意思，就是在叢集索引掃描後擷取的資料，又重新了使用了一次重新分配任務的過程

(Repartition Streams)，就是上圖的將擷取的100行資料重新分配到並行的各個線程中。

其實這裡本可以直接將索引掃描出來的100行資料直接扔到嵌套迴圈中執行。它這裡又重新分配任務的目的就是為了後面嵌套迴圈的並存執行，最大限度的利用硬體資源!

但這樣做又帶了另一個弊端就是執行完嵌套迴圈之後，需要將結果重新匯總，就是下面的(Gather Sreams)運算子。

我們來看看該語句如果不並行的執行計畫

SELECT B1.[KEY],B1.DATA,B2.DATA
FROM BigTable B1 JOIN BigTable2 B2
ON B1.[KEY]=B2.[KEY]
WHERE B1.DATA<100
option(maxdop 1)

 

 

這才是正宗的串列執行計畫。

和上面的並存執行計劃相比較，你會發現SQL Server充分利用硬體資源而形成的並行計劃，是不是很帥!

如果還沒感覺到SQL Server並存執行計劃的魅力，我們再來舉個例子，看如下語句

 

 

 

SELECT BIG_TOP.[KEY],BIG_TOP.DATA,B2.DATA
FROM
(
SELECT TOP 100 B.[KEY],B.DATA
FROM BigTable B
ORDER BY DATA
) BIG_TOP,
BigTable2 B2
WHERE BIG_TOP.[KEY]=B2.[KEY]

 

先來分析下上面的語句，這個語句我們在外表中加入了TOP 100.....ORDER BY DATA關鍵字，這個關鍵字是很有意思....

因為我們知道這個語句是擷取根據DATA關鍵字排序，然後擷取出前100行的意思...

1、根據DATA排序.....丫的多線程我看你怎麼排序?每個線程排列自己的?那你排列完了在匯聚在一起...那豈不是還得重新排序!!

2、擷取前100行資料，丫多線程怎麼擷取?假如我4個線程掃描每個線程擷取25條資料?這樣出來的結果對嘛?

3、我們的目標是讓外表和上面的100行資料還要並行嵌套迴圈串連，因為這樣才能充分利用資源，這個怎麼實現呢?

上面的這些問題，我們來看強大的SQL Server將為我們怎樣產生強悍的執行計畫

 

 

上面的執行計畫已經解決了我們以上所述的三個問題，我們依次來分析下，這幾個問題的解決方案

第一個問題，關於並列排序問題

首選根據叢集索引掃描的方式採用並列的方式從表中擷取出資料

 

 

然後，在並行的根據各個線程中的資料進行排序，擷取前幾列值，我們知道，我們的目標擷取的是前100行，它這裡擷取的方式是冗餘擷取，也就是說每個線程各自排序自己的資料

然後擷取出前面的資料，通過迴圈賽的方式進行交換，擷取出一部分資料

 

 

第二個問題，關於並列擷取前100行資料問題

我們知道要想擷取前100行資料，就必須將各個線程的資料匯總到一起，然後通過比較擷取前100行資料，這是必須的，於是在這一步裡SQL Server又的重新將資料匯總到一起

 

 

第三個問題，下一步需要將這100行資料和外表進行串連，擷取出結果，這裡面採用的嵌套迴圈串連的方式，為了充分利用資源，提升效能，SQL Server又不得不將這100行資料均分到各個線程中去執行，所以這裡又採用了一個分割任務的運算子分發流(Distribute Sreams)任務

 

 

所以經過此步驟又將系統的硬體資源充分利用起來了，然後下一步同樣就是講過嵌套迴圈進行關聯擷取結果，然後再重新將結果匯總，然後輸出

 

 

我們可以看到上面的一個流程，SQLServer經過了：先拆分(並行掃描)——》再並行(擷取TOP 100....)——》再拆分(為了並行嵌套迴圈)——》再並行(為了合并結果)

總之，SQL Server在運行語句的時候，經過各種評估之後，利用各種拆分、各種匯總，目的就是充分的利用硬體資源，達到一個效能最佳化的方式!這就是SQL Server並行運算的精髓。

當然凡事有利就有弊，我們通過這條語句來對比一下串列和並行在SQL Server中的優劣項

一下是串列執行計畫：

 

SELECT BIG_TOP.[KEY],BIG_TOP.DATA,B2.DATAz
FROM
(
SELECT TOP 100 B.[KEY],B.DATA
FROM BigTable B
ORDER BY DATA
) BIG_TOP,
BigTable2 B2
WHERE BIG_TOP.[KEY]=B2.[KEY]
option(maxdop 1)

 

 

 

串列執行的執行計畫：簡單、大氣、沒有複雜的各種拆分、各種匯總及並行。

我們來比較下兩者的不同項，先比較一個T-SQL語句的各個參數值：

 

 

前者是串列、後者是並行

串列編譯耗費CPU：2、並行編譯耗費CPU:10

串列編譯耗費記憶體：184、並行編譯耗費記憶體：208

串列編譯耗時：2、並行編譯耗時：81

上面是採取並行的缺點：1、更消耗CPU、2、編譯更消耗記憶體、3、編譯時間更久

我們來看一下並行的優點：

上圖中串列記憶體使用量(1024)，並行記憶體(448)

優點就是：並存執行消耗記憶體更小

當然還有一個更重要的優點：執行速度更快!

 

 

採用並行的執行方式，執行時間從218毫秒提升到187毫秒!資料量少，我機器效能差所以提升不明顯!

在並行運算執行過程中，還有一種運算子經常遇到：位元影像運算子，這裡我們順帶也介紹一下

舉個例子：

SELECT B1.[KEY],B1.DATA,B2.[KEY]
FROM BigTable B1 JOIN BigTable2 B2
ON B1.DATA=B2.DATA
WHERE B1.[KEY]<10000

這裡我們擷取大表中Key列小於10000行的資料。

 

 

上述的執行語句，就引入了位元影像計算。

其實位元影像計算的目標很簡單：提前過濾，因為我們的語句中要求擷取的結果項比較多10000行資料，在我們後面的線程中採用的並行掃描的方式擷取出資料。由於資料量比較多的原因，各個線程在執行的過程中擷取完資料的時間不同，為了避免因某個線程執行速度緩慢，導致整體堵塞，索引引入了位元影像運算，先將擷取出來的部分結果過濾輸出到前面的雜湊匹配，完整執行。

關於位元影像運算子更多詳細可參照：http://msdn.microsoft.com/zh-cn/library/bb510541

結語

此篇文章先到此吧，本篇主要是上一篇並行運算的一個延續，兩篇文章介紹了SQL Server中關於並行運算的原理和使用方式，關於並行運算這塊就到這吧，下一篇我們補充SQL Server中關於索引的利用方式和動態索引的內容，關於索引我相信很多瞭解資料庫產品的人都熟悉，但是SQL Server中一些語句利用索引的方式可能還不清楚，我們下一篇分析這塊，藉此瞭解索引的建立方式和最佳化技巧，有興趣可提前關注，關於SQL Server效能調優的內容涉及面很廣，後續文章中依次展開分析。

有問題可以留言或者私信，隨時恭候有興趣的童鞋加入SQL SERVER的深入研究。共同學習，一起進步。