SQL Server調優系列基礎篇

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

前言

關於SQL Server調優系列是一個龐大的內容體系，非一言兩語能夠分析清楚，本篇先就在SQL 調優中所最常用的查詢計劃進行解析，力圖做好基礎的掌握，夯實基本功！而後再談談整體的語句調優。

通過本篇瞭解如何閱讀和理解查詢計劃、並且列舉一系列最常用的查詢執行運算子。

技術準備

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

一、區別不同的運算子

在所有T-SQL語句在執行的時候，都會將語句分解為一些基本的結構單元，這些結構單元統稱為：運算子。每一個運算子都實現一個單獨的基本操作，比如：表掃描、索引尋找、索引掃描、過濾等。每個運算子可以迴圈迭代，也可以延續子運算子，這樣就可以組成查詢樹，即：查詢計劃。

每個T-SQL語句都會通過多種運算子進行組合形成不同的查詢計劃，並且這些查詢計劃對於結果的篩選都是有效，但在執行的時候，SQL Server的查詢最佳化工具會自動為我們找到一個最優的。

每一個運算子都會有來源資料的傳入和結果資料的輸出，來源資料的輸入可以來源於其它的運算子或者直接從資料來源表中讀取，經過本身的運算進行結果的輸出。所以每一個運算子是獨立的。互不關心的。

如下例子

SELECT COUNT(*) FROM Orders

此語句會產生兩個簡單的運算子

當然，在SQL Server中上述的兩個運算子有它自己的表達方式，Count(*)是流彙總運算子進行的。

每一個運算子會有三個屬性影響其執行的效率

1、記憶體消耗

所有的運算子都需要一定量的固定記憶體用以完成執行。當一個T-SQL語句經過編譯後產生查詢計劃後，SQL Server會為認為最優的查詢計劃嘗試去固定記憶體，目的是為了再次執行的時候不需要再重新申請記憶體而浪費時間，加快執行速度。

然後，有一些運算子需要額外的記憶體空間來儲存行資料，這樣的運算子所需要的記憶體量通常就和處理的資料行數成正比。如果出現如下幾種情況則會導致記憶體不能申請到，而影響執行效能

a、如果伺服器上正在執行其它的類似的記憶體消耗巨大的查詢，導致系統記憶體剩餘不足的時候，當前的查詢就得延遲進行，直接影響效能。

b、當並發量過大的的情況下，多個查詢競爭有限的記憶體資源，伺服器會適當的控制並發和減少輸送量來維護機器效能，這時候同樣也會影響效能

c、如果進行中的申請的到可用記憶體很少的情況下，SQL Server會在執行過程中和磁碟進行交換資料，通常是使用Tempdb臨時庫進行操作，而這個過程會很慢。更有甚者，會耗盡Tempdb上的磁碟空間以失敗結束

通常比較消耗記憶體的運算子主要有分類、雜湊串連以及雜湊彙總等串連操作。

2、阻斷運算和非阻斷運算

所謂阻斷和非阻斷的區別就是：運算子是否在輸入資料的時候能夠直接輸出結果資料。

a、當一個運算子在消耗輸入行的同時產生輸出行，這種運算子就是非阻斷式的。

比如我們經常使用的 Select Top ...操作，此操作就是輸入行的同時進行輸出行操作，所以此操作就是非阻斷式的。

b、當一個運算子所產生的輸出結果需要等待所有的資料輸入的時候，這個操作運算就是阻斷運算的。

比如上面我們舉的例子Count(*)操作，此操作就需要等待所有的資料行輸入才能計算出，所以為阻斷式運算，另外還有分組計算。

 提示：並不是所有的阻斷式操作就需要消耗記憶體，比如Count(*)就為阻斷式，但它不消耗記憶體，但大部分阻斷式操作都會消耗記憶體。

在大部分的OLTP系統中，我們要盡量的使用非阻斷式操作來代替阻斷式操作，這樣才能更好的提高相應時間，比如有時候我們用EXISTS子查詢來判斷，比用SELECT count(*)>0的速度要理想的多。

 

二、查看查詢計劃

在SQL Server2005版本以上，系統提供了三種展示方式：映像方式、文本方式和XML方式。

1、映像方式

映像方式這種方式是最為常見的一種方式，清晰、簡潔、易懂。非常適合入門級，當然也有它自身的缺點比如複雜的T-SQL語句會產生較大的映像，查看必須收縮操作，比較麻煩。

SSMS預設給我們提供了查看該查詢計劃的便捷按鈕，需要查看某一條語句的時候，只需要點擊上就可以

我們來看一個映像方式展示的查詢計劃圖

以上查詢語句所產生的實際執行計畫，將其分成了各個不同的運算子進行組合，從最右側的叢集索引掃描（index scan）然後經過一系列的運算子加工形成最左側的結果輸出（select）。

需要注意的是圖中箭頭的方向指向的是資料的流向，箭頭線的粗細表示了資料量的多少。

在圖形化執行計畫中，每一個不同的運算子都有它自身的屬性值，我們可以把滑鼠移至運算子表徵圖上查看

 當然也可以直接在表徵圖上右鍵，查看屬性，進入到屬性面板，查看更詳細的屬性值

關於這裡面各個運算子的詳細指標值，我們在後面介紹，不過這裡面有幾個關鍵的值這裡可以說是先稍微提一下，關於影響此語句的整體的績效參數，我們可以選擇最開始的Select運算子，右鍵查看屬性值

此運算子包含了整條語句的編譯時間、所需記憶體、緩衝計劃大小、並行度、記憶體授權、編譯執行所需要的參數以及變數值等資訊。

此方式作為一種相對直觀的方式展示給使用者，所以在我們語句調優中佔據很大的指導地位，我們知道一條T-SQL語句可能會產生很多不同的執行計畫，而SQL Server會協助我們選擇最優的執行計畫，當然我們也可以利用它選擇的執行計畫去調整自己的語句達到最佳化的目的。

鑒於以上目標，SSMS為我們提供了“評估執行計畫”選項，此選項只為評估指導使用，並未實際執行，所以它不包含實際行等具體資訊

2、文本方式

此方式在SSMS中預設沒有提供快速鍵，我們需要自己用語句開啟，開啟的方式有兩種

a、只開啟執行計畫，不包括詳細的評估值

SET SHOWPLAN_TEXT ON

b、開啟所有的執行計畫明細，包括各個屬性的評估值 

SET SHOWPLAN_ALL ON 

文本方式展現的方式，沒有了明確的箭頭指示，改用豎線（|）標示子運算子和當前運算的子父關係。並且資料流方向都是從子運算子流向父運算子的，雖然文本展現方式不夠直觀，但是如果掌握了文本的閱讀方式，此方式會更易閱讀，尤其在涉及很大的大型計劃的時候，此方式更容易儲存、處理、搜尋和比較。

我們來看一個列子

此種方式輸出的形式為文本方式，我們可以拷貝至文字編輯器中分析，方便於尋找分析等操作

 以上是文字查詢計劃的分析方式，簡單點的就是從最裡面的運算子開始執行，資料流方向也是依次從子運算子流向父運算子。

上面的方式看起來有點映像方式，分析起來簡單更易用。但是或許缺少的是每個運算子的屬性運算資訊，我們通過b方法裡來查看明細

利用此方式可以直觀的分析出每個運算子操作的屬性評估值。

3、XML方式

XML展現查詢計劃的方式是SQL Server2005中新加入的功能，此方式結合了文本方式和圖形計劃方式的優點。利用XML元素的方式展現查詢計劃。

更主要的特點是利用XML方式是一種規範的方式，可以利用編程的方式進行標準XML操作，利於查詢。並且在SQL Server2005中還加入的XML的資料類型，並且內建了XQuery功能進行查詢。此方式尤其對與超大型的查詢計劃查看非常的方便。

通過以下語句開啟

SET STATISTICS XML ON 

我們可以點擊輸出的XML進行查看

XML方式展現了非常詳細的查詢計劃資訊，我們可以簡單的分析下

• StmtSimple：描述了T-SQL的執行文本，並且詳細分析了該語句的類型，以及各個屬性的評估值。
• StatementSetOptions：描述該語句的各種屬性值的Set值
• QueryPlan：是詳細的執行計畫，包括執行計畫的並行的線程數、編譯時間、記憶體佔有量等
• OutputList：輸出參數列表
• 在中間這部分就是具體的不同的執行運算子的資訊了，並且包括詳細的預估值等
• ParameterList：輸出參數列表

XML方式提供的資訊是最為全面的，並且在SQL Server內部儲存的查詢計劃類型也為XML資料類型。

三、分析查詢計劃 

當我們拿到一個語句的查詢計劃，我們應該會分析裡面的執行計畫的含義，以及各個運算子的屬性值，學會如何調整各個運算子的屬性值來整體的提高該語句的運行效率。

1、掃描以及尋找

對於掃描（scan）和尋找（seek）這兩種方式是資料庫裡面從基礎資料表裡擷取的資料的基本方式。

a、當一張表為堆表（沒有任何索引）的時候或者擷取的資料列不存在任何索引來供尋找，此種資料的擷取只能通過全表掃描過濾擷取，如果存在索引項目會通過索引項目的掃描來擷取資料，提高擷取資料的速度。

該方法是最為簡單的擷取資料的方式

b、如果當前搜尋的資料行存在索引項目，那麼會採取索引尋找（seek）進行資料檢索。

該條語句就是執行的索引尋找，因為在Orders表中的OrderDate列存在非叢集索引項。這裡順便提一下如果引入靜態變數，SQL Server會自動參數化該值，目的是為了減少編譯次數，重複利用執行計畫。

由於尋找只是搜尋合格這些頁進行輸出操作，所以尋找效率只和合格行數、頁數成正比，和整個表中的總行數沒有關係。

c、當所選的索引列不包含輸出資料行的時候，也就是說要篩選出的列項不為索引所覆蓋，對於這種情況又引出了另外一種尋找方式

書籤尋找（Bookmark Lookup）

其實該方式是掃描和尋找之間的一個折中方式，我們知道，如果通過叢集索引掃描，則會擷取所有的列，但是這涉及表中的每一行資料，影響效能，相反如果只是通過叢集索引方式進行尋找，則有一些列不能擷取得到，如果這些列正是我們需要的，這就是不準確的，所以，鑒於此，引入了折中的方式：書籤尋找（Bookmark Lookup)

簡單點講：書籤尋找就是通過索引頁節點資料尋找相關的列資料。

我們來看一個具體的查詢列子

這裡需要解釋一下，在SQL Server2005 SP2版本以上，書籤尋找也被稱為鍵尋找，其實是一個概念。

這種方式有一些弊端，就是在進行書籤尋找的時候，如果通過非叢集索引的分葉節點尋找到叢集索引資料，這種情況通過叢集索引能夠快速的擷取到資料，如果非叢集索引關鍵字和叢集索引關鍵字不存在任何關聯，這種情況下，書籤尋找就會執行隨機的I/O操作到叢集索引或者堆表中，而這種情況是非常耗時的，因為相比而言順序I/O掃描都要比隨機I/O掃描效能高很多。

為瞭解決上面所述的問題，在SQL Server2005以後的版本中，在建立index的時候引入了INCLUDE關鍵字。通過建立索引的時候，直接將書籤要尋找的項直接包含進去，這樣就不會發生隨機I/O操作。此種方式的缺點會造成索引儲存增大一部分，但相比帶來的好處，基本可以忽略不計。

 

 

結語

此篇文章先到此吧，本篇主要介紹了關於T-SQL語句調優從執行計畫該如下下手，並介紹了幾個常見的簡單運算子，下一篇將著重介紹我們最常用的一些運算子和調優技巧，包括：串連運算子、彙總運算子、聯合運算子、並行運算等吧，關於SQL Server效能調優的內容涉及面很廣，後續文章中依次展開分析。

 

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

SQL Server調優系列基礎篇