編程學(xué)習(xí)網(wǎng) > 數(shù)據(jù)庫(kù) > SQL Server > SQL Server調(diào)優(yōu)系列基礎(chǔ)篇（并行運(yùn)算總結(jié)）
2015
01-16

SQL Server調(diào)優(yōu)系列基礎(chǔ)篇（并行運(yùn)算總結(jié)）

前言

上三篇文章我們介紹了查看查詢(xún)計(jì)劃的方式，以及一些常用的連接運(yùn)算符、聯(lián)合運(yùn)算符的優(yōu)化技巧。

本篇我們分析SQL Server的并行運(yùn)算，作為多核計(jì)算機(jī)盛行的今天，SQL Server也會(huì)適時(shí)調(diào)整自己的查詢(xún)計(jì)劃，來(lái)適應(yīng)硬件資源的擴(kuò)展，充分利用硬件資源，最大限度的提高性能。

閑言少敘，直接進(jìn)入本篇的正題。

技術(shù)準(zhǔn)備

同前幾篇一樣，基于SQL Server2008R2版本，利用微軟的一個(gè)更簡(jiǎn)潔的案例庫(kù)（Northwind）進(jìn)行解析。

一、并行運(yùn)算符

在我們?nèi)粘Ｋ鶎?xiě)的T-SQL語(yǔ)句，并不是所有的最優(yōu)執(zhí)行計(jì)劃都是一樣的，其最優(yōu)的執(zhí)行計(jì)劃的形成需要多方面的評(píng)估才可以，大部分根據(jù)SQL Server本身所形成的統(tǒng)計(jì)信息，然后對(duì)形成的多個(gè)執(zhí)行計(jì)劃進(jìn)行評(píng)估，進(jìn)而選出最優(yōu)的執(zhí)行方式。

在SQL Server根據(jù)庫(kù)內(nèi)容形成的統(tǒng)計(jì)信息進(jìn)行評(píng)估的同時(shí)，還要參照當(dāng)前運(yùn)行的硬件資源，有時(shí)候它認(rèn)為最優(yōu)的方案可能當(dāng)前硬件資源不支持，比如：內(nèi)存限制、CPU限制、IO瓶頸等，所以執(zhí)行計(jì)劃的優(yōu)劣還要依賴(lài)于底層硬件。

當(dāng)SQL Server發(fā)現(xiàn)某個(gè)處理的數(shù)據(jù)集比較大，耗費(fèi)資源比較多時(shí)，但此時(shí)硬件存在多顆CPU時(shí)，SQL Server會(huì)嘗試使用并行的方法，把數(shù)據(jù)集拆分成若干個(gè)，若干個(gè)線(xiàn)程同時(shí)處理，來(lái)提高整體效率。

在SQL Server中可以通過(guò)如下方法，設(shè)置SQL Server可用的CPU個(gè)數(shù)

默認(rèn)SQL Server會(huì)自動(dòng)選擇CPU個(gè)數(shù)，當(dāng)然不排除某些情況下，比如高并發(fā)的生產(chǎn)環(huán)境中，防止SQL Server獨(dú)占所有CPU，所以提供了該配置的界面。

還有一個(gè)系統(tǒng)參數(shù)，就是我們熟知的MAXDOP參數(shù)，也可以更改此系統(tǒng)參數(shù)配置，該配置也可以控制每個(gè)運(yùn)算符的并行數(shù)（記?。哼@里是每個(gè)運(yùn)算符的，而非全部的），我們來(lái)查看該參數(shù)

這個(gè)并行運(yùn)算符的設(shè)置數(shù)，指定的是每個(gè)運(yùn)算符的最大并行數(shù)，所以有時(shí)候我們利用查看系統(tǒng)任務(wù)數(shù)的DMV視圖sys.dm_os_tasks來(lái)查看，很可能看到大于并行度的線(xiàn)程數(shù)據(jù)量，也就是說(shuō)線(xiàn)程數(shù)據(jù)可能超過(guò)并行度，原因就是兩個(gè)運(yùn)算符重新劃分了數(shù)據(jù)，分配到不同的線(xiàn)程中。

這里如沒(méi)特殊情況的話(huà)，建議采用默認(rèn)設(shè)置最佳。

我們舉一個(gè)分組的例子，來(lái)理解并行運(yùn)算

采用并行運(yùn)算出了提升性能還有如下幾個(gè)優(yōu)點(diǎn)：

  • 不依賴(lài)于線(xiàn)程的數(shù)量，在運(yùn)行時(shí)自動(dòng)的添加或移除線(xiàn)程，在保證系統(tǒng)正常吞吐率的前提下達(dá)到一個(gè)性能最優(yōu)值
  • 能夠適應(yīng)傾斜和負(fù)載均衡，比如一個(gè)線(xiàn)程運(yùn)行速度比其它線(xiàn)程慢，這個(gè)線(xiàn)程要掃描或者運(yùn)行的數(shù)量會(huì)自動(dòng)減少，而其它跑的快的線(xiàn)程會(huì)相應(yīng)提高任務(wù)數(shù)，所以總的執(zhí)行時(shí)間就會(huì)平穩(wěn)的減少，而非一個(gè)線(xiàn)程阻塞整體性能。

下面我們來(lái)舉個(gè)例子，詳細(xì)的說(shuō)明一下

并行計(jì)劃一般應(yīng)用于數(shù)據(jù)量比較大的表，小表采用串行的效率是最高的，所以這里我們新建一個(gè)測(cè)試的大表，然后插入部分測(cè)試數(shù)據(jù)，我們插入250000行，整體表超過(guò)6500頁(yè)，腳本如下 

--新建表，建立主鍵，形成聚集索引
CREATE TABLE BigTable
(
   [KEY] INT,
   DATA INT,
   PAD CHAR(200),
   CONSTRAINT [PK1] PRIMARY KEY ([KEY])
)
GO
--批量插入測(cè)試數(shù)據(jù)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

我們來(lái)執(zhí)行一個(gè)簡(jiǎn)單查詢(xún)的腳本 

SELECT [KEY],[DATA]
FROM BigTable

這里對(duì)于這種查詢(xún)腳本，沒(méi)有任何篩選條件的情況下，沒(méi)必要采用并行掃描，因?yàn)椴捎么袙呙璧姆绞降玫綌?shù)據(jù)的速度反而比并行掃描獲取的快，所以這里采用了clustered scan的方式，我們來(lái)加一個(gè)篩選條件看看 

SELECT [KEY],[DATA]
FROM BigTable
WHERE DATA<1000

對(duì)于這個(gè)有篩選條件的T-SQL語(yǔ)句，這里SQL Server果斷的采用的并行運(yùn)算的方式，聚集索引也是并行掃描，因?yàn)槲译娔X為4個(gè)邏輯CPU(其實(shí)是2顆物理CPU，4線(xiàn)程)，所以這里使用的是4線(xiàn)程并行掃描四次表，每個(gè)線(xiàn)程掃描一部分?jǐn)?shù)據(jù)，然后匯總。

這里總共用了4個(gè)線(xiàn)程，其中線(xiàn)程0為調(diào)度線(xiàn)程，負(fù)責(zé)調(diào)度所有的其它線(xiàn)程，所以它不執(zhí)行掃描，而線(xiàn)程1到線(xiàn)程4執(zhí)行了這1000行的掃描！當(dāng)然這里數(shù)據(jù)量比較少，有的線(xiàn)程分配了0個(gè)任務(wù)，但是總得掃描次數(shù)為4次，所以這4個(gè)線(xiàn)程是并行的掃描了這個(gè)表。

可能上面獲取的結(jié)果比較簡(jiǎn)單，有的線(xiàn)程任務(wù)還沒(méi)有給分配滿(mǎn)，我們來(lái)找一個(gè)相對(duì)稍復(fù)雜的語(yǔ)句 

SELECT MIN([DATA])
FROM BigTable

這個(gè)執(zhí)行計(jì)劃挺簡(jiǎn)單的，我們依次從右邊向左分析，依次執(zhí)行為：

4個(gè)并行聚集索引掃描——>4個(gè)線(xiàn)程并行獲取出前當(dāng)前線(xiàn)程的最小數(shù)——>執(zhí)行4個(gè)最小數(shù)匯總——>執(zhí)行流聚合獲取出4個(gè)數(shù)中的最小值——>輸出結(jié)果項(xiàng)。

然后4個(gè)線(xiàn)程，每個(gè)線(xiàn)程一個(gè)流聚合獲取當(dāng)前線(xiàn)程的最小數(shù)

然后，將這個(gè)四個(gè)最小值經(jīng)過(guò)下一個(gè)“并行度”的運(yùn)算符匯聚成一個(gè)表

然后下一個(gè)就是流聚合，從這個(gè)4行數(shù)據(jù)中獲取出最小值，進(jìn)行輸出，關(guān)于流聚合我們上一篇文章中已經(jīng)介紹

以上就一個(gè)一個(gè)標(biāo)準(zhǔn)的多線(xiàn)程并行運(yùn)算的過(guò)程。

上面的過(guò)程中，因?yàn)槲覀兪褂玫牟⑿芯奂饕龗呙钄?shù)據(jù)，4個(gè)線(xiàn)程基本上是平均分?jǐn)偭巳蝿?wù)量，也就是說(shuō)每個(gè)線(xiàn)程掃描的數(shù)據(jù)量基本相等，下面我們將一個(gè)線(xiàn)程使其處于忙碌狀態(tài)，看看SQL Server會(huì)不會(huì)將任務(wù)動(dòng)態(tài)的平攤到其它幾個(gè)不忙碌的線(xiàn)程上。

我們?cè)趤?lái)添加一個(gè)大數(shù)據(jù)量表，腳本如下 

SELECT [KEY],[DATA],[PAD] 
INTO BigTable2
FROM BigTable

我們來(lái)寫(xiě)一個(gè)大量語(yǔ)句的查詢(xún)，使其占用一個(gè)線(xiàn)程，并且我們這里強(qiáng)制指定只用一個(gè)線(xiàn)程運(yùn)行 

SELECT MIN(B1.[KEY]+B2.[KEY]) 
FROM BigTable B1 CROSS JOIN BigTable2 B2
OPTION(MAXDOP 1)

以上代碼想跑出結(jié)果，就我這個(gè)電腦配置估計(jì)少說(shuō)五分鐘以上，并且我們還強(qiáng)行串行運(yùn)算，速度可想而知，

我們接著執(zhí)行上面的獲取最小值的語(yǔ)句，查看執(zhí)行計(jì)劃 

SELECT MIN([DATA])
FROM BigTable

我們?cè)趫?zhí)行計(jì)劃中，查看到了聚集索引掃描的線(xiàn)程數(shù)量

可以看到，線(xiàn)程1已經(jīng)數(shù)量減少了近四分之的數(shù)據(jù)，并且從線(xiàn)程1到線(xiàn)程4，所掃描的數(shù)據(jù)量是依次增加的。

我們上面的語(yǔ)句很明確的指定了MAXDOP為1，理論上講只可能會(huì)影響一個(gè)線(xiàn)程，為什么這幾個(gè)線(xiàn)程都影響呢？其實(shí)這個(gè)原因很簡(jiǎn)單，我的電腦是物理CPU只有兩核，所謂的線(xiàn)程數(shù)只是超線(xiàn)程，所以非傳統(tǒng)意義上的真正的4核數(shù)，所以線(xiàn)程之間是互相影響的。

我們來(lái)看一個(gè)并行連接操作的例子，我們查看并行嵌套循環(huán)是怎樣利用資源的 

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

上面的語(yǔ)句中，我們?cè)贐igTable中Key列存在聚集索引，而查詢(xún)條件中DATA列不存在，所以這里肯定為聚集索引掃描，對(duì)數(shù)據(jù)進(jìn)行查找

來(lái)看執(zhí)行計(jì)劃

我們依次來(lái)分析這個(gè)流程，結(jié)合文本的執(zhí)行計(jì)劃分析更為準(zhǔn)確，從右邊依次向左分析

第一步，就是利用全表通過(guò)聚集索引掃描獲取出數(shù)據(jù)，因?yàn)檫@里采用的并行的聚集索引掃描，我們來(lái)看并行的線(xiàn)程數(shù)和掃描數(shù)

四個(gè)線(xiàn)程掃描，這里線(xiàn)程3獲取出數(shù)據(jù)100行數(shù)據(jù)。

然后將這100行數(shù)據(jù)，重新分配線(xiàn)程，這里每個(gè)線(xiàn)程平均分配到25行數(shù)據(jù)

到此，我們要獲取的結(jié)果已經(jīng)均分成4個(gè)線(xiàn)程共同執(zhí)行，每個(gè)線(xiàn)程分配了25行數(shù)據(jù)，下一步就是交給嵌套循環(huán)連接了，因?yàn)槲覀兩厦娴恼Z(yǔ)句中需要從BigTable2中獲取數(shù)據(jù)行，所以這里選擇了嵌套循環(huán)，依次掃描BigTable2獲取數(shù)據(jù)。

關(guān)于嵌套循環(huán)連接運(yùn)算符，可以參照我的第二篇文章。

我們知道這是外表的循環(huán)數(shù)，也就是說(shuō)這里會(huì)有4個(gè)線(xiàn)程并行執(zhí)行嵌套循環(huán)。如果每個(gè)線(xiàn)程均分25行，數(shù)據(jù)那么內(nèi)部表就要執(zhí)行

4*25=100次。

然后，執(zhí)行完，嵌套掃描獲取結(jié)果后，下一步就是，將各個(gè)線(xiàn)程執(zhí)行的結(jié)果通過(guò)并行運(yùn)算符匯總，然后輸出

上述過(guò)程就是一個(gè)并行嵌套循環(huán)的執(zhí)行流程。充分利用了四核的硬件資源。

參考文獻(xiàn)

結(jié)語(yǔ)

此篇文章先到此吧，文章短一點(diǎn)，便于理解掌握，后續(xù)關(guān)于并行操作還有一部分內(nèi)容，后續(xù)文章補(bǔ)充吧，本篇主要介紹了查詢(xún)計(jì)劃中的并行運(yùn)算符，下一篇我們接著補(bǔ)充一部分SQL Server中的并行運(yùn)算，然后分析下我們?nèi)粘Ｋ鶎?xiě)的增刪改這些操作符的優(yōu)化項(xiàng)，有興趣可提前關(guān)注，關(guān)于SQL Server性能調(diào)優(yōu)的內(nèi)容涉及面很廣，后續(xù)文章中依次展開(kāi)分析。

有問(wèn)題可以留言或者私信，隨時(shí)恭候有興趣的童鞋加入SQL SERVER的深入研究。共同學(xué)習(xí)，一起進(jìn)步。

系列目錄：

掃碼二維碼 獲取免費(fèi)視頻學(xué)習(xí)資料

Python編程學(xué)習(xí)

查 看2022高級(jí)編程視頻教程免費(fèi)獲取