歡迎光臨
每天分享高質量文章

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.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]
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的深入研究。共同學習,一起進步。

原文出處: 指尖流淌-吳學雷的博客

原文鏈接: http://www.cnblogs.com/zhijianliutang/p/4156229.html

赞(0)

分享創造快樂