oracle數據庫菜鳥教程「sqlserver日誌查看」

在《深入解析SQL Server並行執行原理及實踐（上）》談完並行執行的原理，咱們再來談談優化，到底並行執行能給我們帶來哪些好處，我們又應該注意什麼呢，下面展開。

Amdahl』s Law

再談並行優化前我想有必要談談阿姆達爾定律，可惜老爺子去年已經駕鶴先去了。

其中 P：可以並行的百分比

N：算法並行計算使用的「CPU」

這裡我們舉個簡單的例子，我們來做一份大餐，如圖1-1所示：

圖1-1

馬鈴薯泥，荷蘭豆，雞排還有整體組合各需十分鐘。在這裡前三個食材是可以共同執行的，也就是說4個步驟中3步可並行 P=3/4，其中3個食材可同時加工N=3，則根據公式S(N)=1/(1-0。75)+0。75/3 =2這個操作整體相比完全串行可提升200%即做這個大餐的時間由40分鐘，縮短到20分鐘。

這裡實際引申出一個事實，整體的操作時間和整體事務中串行的比例多少有很大關聯，這個引申到我們的並行調優中，並行可以改進查詢，但受串行部分影響也是很大的，同時也告之我們沒有必要過度串行優化，還是做大餐，即便並行可做的越來越多，但改進的效果卻越發不明顯，過度無謂的優化我們是應該避免的，如圖1-2：

圖1-2

SQL Server中禁止並行的一些操作

上面我們了解了並行的操作性，但在SQL Server並不是所有操作都可以並行的，有些操作符會導致整個執行計劃無法並行，而有些會使得某些分支(branch)無法並行，下面羅列出相關的操作符對並行的影響，這些感興趣的朋友可以自行測試。

執行計劃禁止並行

T-SQL scalar functions

更新表變量數據

訪問系統表

動態游標

Branche不能並型

TOP（global）

2012以前窗口函數(Row_Number)

Multi -Statement Function

Backward scan

CTE遞歸

並行執行的優點

說了一些限制，我們再來簡單說下優點，要不開此文章的意義何在。實際上在上半部分文章中大家可能已經感受到它的優點了，這裡再簡單總結下。實際上在並行中，計算工作是均勻的分配在參與並行的threads中，所有的threads同時工作，無先後之分。某些操作中threads自身的工作完成後還會協助threads工作，雖然會有短暫的CXPACKET等待(數據分佈，預估等問題)但基本可以解決或是緩解。

執行時分支(Branchs)間有時可以是無順序的，更好地增加了並行。

針對CPU-Bound的操作，SQL Server可以說時隨着CPU(並行度)的增加，性能也基本是線性增加的。

並行相關的設置

SQL Server中有並行相關的一些設置，主要有兩個：並行閾值及最大並行度。

並行閾值：上篇已經提到過，查詢子樹大小觸發並行的條件，此值的設定仁者見仁智者見智了，一般設定為實例執行計劃編譯的平均查詢子樹大小上下幅度不超過20%

最大並行度：查詢中的操作符可同時採用的線程數。這個值便隨着NUMA的誕生應尤為注意，早在SQL 2005研發階段，NUMA開始出現，而SQL 2005也提供了支持，但程度有限，隨着SQLOS的進一步演化在SQL2008時對其支持已經很不錯了，這裡有大家都知道的NUMA架構下的foreign memory問題，實際上SQL Server在採用並行時會試圖將並行的線程都集中在某個NUMA節點下，所以我們在配置初始參數時並行度最好控制在某個NUMA節點的核數內，而且最好是偶數，這裏面涉及到很多SQLOS的知識，限於篇幅就不深入了。

使用並行應注意的問題

強制使用並行

Trace flag 8649 在SQL Server中可以不觸發並行而手動指定並行，注意這個標記是無官方文檔記錄，勿輕易使用。使用時只需在查詢最好加上query hint：Option(querytraceon 8649)即可。

數據分佈不均，預估，碎片等問題

導致CXPACKET等待以及過多無謂IO，應對方式創建臨時對象，更新統計信息，整理碎片等。

nested loop Join導致的隨機IO，及nested loop join預讀問題等

冷數據中使用並行nested join可能導致實例的IO穩定性受影響，面對具體場景應酌情使用。應對方式可以關閉nested loop預讀，而nested loop預讀時SQL Server也會試圖將隨機IO轉化為連續IO，如具體應用合理應接受並行nested loop join。

線程飢餓問題(worker thread starvation)

前面我們說過，線程的授予是按照分支(branches)及並行度授予的，如果並行度高，此時複雜的查詢下分支又很多，這個時候可能針對某個查詢分配過多線程，加之這類查詢又高並發，則這時出現線程飢餓的幾率就大大增加了。具體生產中，這個應引起我們的注意。這裡給大家舉個簡單的實例，感興趣的同學可以自己測試下。這個查詢有5個分支，分支所申請的線程就是5*16共80個！如圖5-1：

圖5-1

並行死鎖

並行執行提升查詢響應時間，提高用戶體驗已經被我們所熟知了，但正如我一直強調的，任何事物均有利弊，我們要做的重點是權衡。並行死鎖在並行執行中也會偶爾出現，官方給出的解釋是SQL Server的「BUG」，你只需將查詢的MAXDOP調整為1，死鎖就會自動消失，但有時我們還應追溯其本質。這裡用一個實例為大家說明下並行死鎖的原因，以便我們更好的利用並行。

生成測試數據

接下來我們執行如下語句，取30000下最大偶數，此時我將執行並行數maxdop隨意調整為奇數，3，5，7我的執行都可以迅速返回結果。

但當我將並行數調整為偶數時，執行時間居然長達數秒…打開trace profiler跟蹤dead lock chain我們發現，當並行數為偶數時出現了死鎖。

註：我們用Trace profiler捕捉死鎖

如圖6-1，6-2，6-3：

圖6-1

圖6-2

圖6-3

有的同學可能覺得蹊蹺，發生了什麼我們具體分析下並行死鎖的相應執行計劃。

分析：

訪問基表數據時用的是聚集索引掃描，但掃描方式是backward，而SQL server中只有forward scan可以並行掃描，backward只能串行掃描
因此在做exchange向各個threads分發數據時（distribute streams）採用roundrobin輪詢分發數據，這勢必造成奇偶數據按threads分開流向下一個過濾操作符
在Filter時將奇數的數據過濾，而相應的threads也就沒有了數據
所以在最後exchange匯總數據時(gather streams)有的threads沒有數據，因而造成死鎖。

（註：thread 0為主線程，不參與並行分支工作）

分析如圖6-4：

圖6-4

而反觀並行採用奇數並行數，這時當分發數據時就不會造成某個thread所持有的數據只是奇數或是偶數，也就不會造成後來的情形，死鎖也就不會出現。如圖6-4感興趣的同學可以做實驗調整並行數並閱讀相應的執行計劃。

圖6-5

至此我們應該可以明白了。

萬事皆有因果，一個簡單的BUG可以做為回應，但深究可能窺其本質，並且很有意思。技術人需有這種精神。

優化實踐

最後咱們再通過一個簡單的實例說下優化實踐。

日常的OLTP環境中，有時會涉及到一些統計方面的SQL語句，這些語句可能消耗巨大，進而影響整體運行環境，這裡我為大家介紹如何利用SQL Server中的「類MapReduce」方式，在特定的統計情形中不犧牲響應速度的情形下減少資源消耗。

我們可能經常會利用開窗函數對巨大的數據集進行分組統計排序。比如下面的例子：

腳本環境

當我們針對bigProduct表的productid分組，並按照bigTransactionHistory的actualcost及quantity分別排序取結果集語句如下：

執行此語句並輸出實際執行計劃如圖7-1：

圖7-1

可以看出我的這條語句由於對大量結果集進行排序，致使消耗了365MB的內存，並且由於分別對actualcost， quantity排序使得在進行第二個排序時內存不足並溢出，排序的操作只能在tempdb中進行。

Sort由於是典型的計算密集型運算符，此查詢在我的機器上執行時間為5s。

大量的內存被個別查詢長時間獨佔，使得Buffer Pool的穩定性下降，進而可能影響整體吞吐。

關於SQL Server的Sort限於篇幅這裡就不細說了。

在介紹「類MapReduce」之前，我想先接着上面Sort溢出的現象給大家簡單介紹下通過Query hints 來影響優化器的資源分配。

通過查詢可以看出由於我加了Query Hint，改變了優化器的資源評估標準，使得優化器認為productid本身需要資源從1001 and 3001分配變為了1001 and 5001分配，內存申請由365MB變為了685MB，接近一倍的增長，避免了溢出。並且執行時間也由5S變為了2S，提升了用戶體驗。

如圖7-2：

圖7-2

可以看到溢出與不溢出在查詢消耗時間上差別很大，但這樣就是好了嗎?其實未必，畢竟即便在非溢出的情形中將近700MB的內存近2s內被這個查詢佔用，這在高並發的OLTP環境中是傷全局的。那更理想的解決方式呢?

在並行執行計劃中是多個線程(CPU核)協同工作，這裏面的Sort面對大量數據結果集時即便多核同時進行，在複雜的預算面前也是有些力不從心。在分佈式的思想中，講究分而治之，我們只要將大的結果集化為多個小的部分並多核同時進行排序，這樣就達到了分而治之的效果。也就是前面說的「MapReduce」。

幸好，在SQL Server實現並行運算的運算符」nestloop」與之相似。上篇中並行nested loop join的原理中已經提到。

並行Nest loop Join實現方式

在並行循環嵌套中，外表數據Scan，seek多線程(threads)同時進行(Map)，而內表的在每個thread上串行執行(Reduce)。

優點：

可以減少執行過程中各線程數據流的數據交換
顯著的減少內存需求

上述查詢我用如下的方式實現：

執行中輸出實際執行計劃可以看出，此計劃中消耗的內存15MB，和上述的執行計劃相比有指數級的下降，同時執行時間為不到2s，保證執行時間的同時明顯降低了資源消耗，從而避免了實例級的影響。

已經很美好了：)

如圖7-3：

圖7-3

到這裡其實我們已經達到了我們想要的效果，但還可以更好嗎?我們還需要多了解些。

上面我講到了並行nest loops的優點，少資源佔用，少數據交換。但就像在我以前的文章中說的那樣：」任何術都是有缺陷的」，前文已經提到了並行中很可能造成數據的傾斜，如上圖7-3中藍線中標註的外表seek，實際是只在一個thread中完成的。優化器為我們加了數據交換，使得外部的數據在多個threads下分佈均衡與內表匹配提升效率，但優化器可不會每次都如此「好心」(智能)。

其實在並行seek，scan中由於實現方式在05到08的過程變化很大(前文提到過)，使得操作更需注意，這裡我們直接上新的方案。