SQL Server調優系列基礎篇（並行運算總結篇二）

標籤：style   blog   http   io   ar   color   os   使用   sp   

前言

上一篇文章我們介紹了查看查詢計劃的並行運行方式。

本篇我們接著分析SQL Server的並行運算。

閑言少敘，直接進入本篇的正題。

技術準備

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

內容

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

SELECT B1.[KEY],B1.DATA,B2.DATA FROM BigTable B1 JOIN BigTable2 B2ON 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 B2ON B1.[KEY]=B2.[KEY]WHERE B1.DATA<100option(maxdop 1)

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

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

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

SELECT BIG_TOP.[KEY],BIG_TOP.DATA,B2.DATAFROM (   SELECT TOP 100 B.[KEY],B.DATA   FROM BigTable B   ORDER BY DATA) BIG_TOP,BigTable2 B2WHERE 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.DATAFROM (   SELECT TOP 100 B.[KEY],B.DATA   FROM BigTable B   ORDER BY DATA) BIG_TOP,BigTable2 B2WHERE 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 B2ON B1.DATA=B2.DATAWHERE B1.[KEY]<10000

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

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

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

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

結語

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

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

 

文章最後給出上一篇的串連

SQL Server調優系列基礎篇

SQL Server調優系列基礎篇（常用運算子總結）

SQL Server調優系列基礎篇（聯合運算子總結）

SQL Server調優系列基礎篇（並行運算總結）

 

如果您看了本篇部落格,覺得對您有所收穫，請不要吝嗇您的“推薦”。

 

SQL Server調優系列基礎篇（並行運算總結篇二）