剖析SQL Server執行計畫

-->Title: 淺議SQL Server執行計畫
-->Author: wufeng4552
-->Date :2009-10-20 15:08:24

前言:

最近溫習了執行計劃方面的部份知識,為了加深印象與方便初學者,特做了如下整理.

不對地方歡迎提出並指正.

查看執行計畫的方式:

(1)菜單方式:

(1.1)顯示實際執行計畫

(1.2)顯示預估的執行計畫

以上兩種均位於位於”查詢”下拉式功能表中,兩者的不同之處在於當實際運行一個查詢時,當前的伺服器上的運算也會被考慮進去。大多數情況下,兩種方式產生的執行計畫產生的結果是相似的.

(2)命令方式

SET SHOWPLAN_TEXT ON

這條命令被執行後,所有在當前這個查詢分析器會話中執行的查詢都不會運行,而是會顯示一個基於文本的執行計畫

注意:執行某條用到暫存資料表的查詢時，必須在執行查詢先運行SET STATISTICS PROFILE ON語句 如:

go

if not object_id('tempdb..#t') is null

 drop table #t

Go

Create table #t([日期] Datetime,[姓名] nvarchar(2))

Insert #t

select '2009-10-01',N'張三' union all

select '2009-10-01',N'李四' union all

select '2009-10-02',N'趙六'

Go

SET STATISTICS PROFILE ON

go

select * from #T

SET STATISTICS PROFILE OFF

結果如圖1

 

圖1

為了討論方便 下面以 Northwind 庫中表 [Order Details] 為例(我已經將主鍵刪除)

use Northwind

go

SET SHOWPLAN_TEXT ON

go

select ProductID,sum(Quantity)Quantity from [Order Details]

group by ProductID order by ProductID

go

SET SHOWPLAN_TEXT OFF

/*

StmtText

|--Sort(ORDER BY:([Northwind].[dbo].[Order Details].[ProductID] ASC))

     |--Hash Match(Aggregate, HASH:([Northwind].[dbo].[Order Details].[ProductID]) DEFINE:([Expr1004]=SUM([Northwind].[dbo].[Order Details].[Quantity])))

        |--Table Scan(OBJECT:([Northwind].[dbo].[Order Details]))

*/

use Northwind

go

----建一個叢集索引

CREATE CLUSTERED INDEX INDEX_ProductID on [Order Details](ProductID)

go

SET SHOWPLAN_TEXT ON

go

select ProductID,sum(Quantity)Quantity from [Order Details]

group by ProductID order by ProductID

go

SET SHOWPLAN_TEXT OFF

/*

StmtText

---------------------------------------

  |--Stream Aggregate(GROUP BY:([Northwind].[dbo].[Order Details].[ProductID]) DEFINE:([Expr1004]=SUM([Northwind].[dbo].[Order Details].[Quantity])))

       |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Order Details].[INDEX_ProductID]), ORDERED FORWARD)

(2 個資料列受到影響)

*/

如果在執行計畫中看到如下所示的任何一項,從效能方面來說,下面所示的每一項都是不理想的。

Index or table scans(索引或者表掃描)：可能意味著需要更好的或者額外的索引。

Bookmark Lookups(書籤尋找)：考慮修改當前的叢集索引，使用複蓋索引，限制SELECT語句中的欄位數量。

Filter(過濾)：在WHERE從句中移除用到的任何函數，不要在SQL語句中包含視圖，可能需要額外的索引。

Sort(排序)：資料是否真的需要排序？可否使用索引來避免排序？在用戶端排序是否會更加有效率？

以上事項避免得越多,查詢效能就會越快.

注意:如果有在預存程序中或者其它T-SQL批處理代碼中用到了暫存資料表，就不能在查詢分析器或Management Studio使用”顯示預估的執行計畫”選項來評估查詢。必須實際運行這個預存程序或者批處理代碼。這是因為使用”顯示預估的執行計畫”選項來運行一個查詢時，它並沒有實際被運行，暫存資料表也沒有建立。由於暫存資料表沒有被建立，參考到暫存資料表的代碼就會失敗，導致預估的執行計畫不能成建立成功。從另一方面來說，如果使用的是表變數而不是暫存資料表，則可以使用”顯示預估的執行計畫”選項.

use Northwind

go

select a.* from [orders] a,[Order Details] b

where a.OrderID=b.OrderID

 

圖2

查看執行計畫時記住如下幾點：

(1)     非常複雜的執行計畫會被分成多個部分，它們分別列出在螢幕上。每個部分分別代表查詢最佳化工具為了得到最終結果而必須執行的單個處理或步驟。執行計畫的每個步驟經常會被拆分成一個個更小的子步驟。不幸的是，它們是從右至左顯示在螢幕上的。這意味著你必須滾動到圖形執行計畫的最右邊去查看每個步驟是從哪兒開始的

(2)     每個步驟與子步驟間通過箭頭串連，藉此顯示查詢執行的路徑。

(3)     最後，查詢的所有部分在螢幕頂部的左邊匯總到一起,如果將滑鼠移動到串連步驟或子步驟的箭頭上，就可以看到一個彈出式視窗，上面顯示有多少筆記錄從一個步驟或子步驟移動到另一個步驟或子步驟(如圖3) 如果將滑鼠移動到任何執行計畫任何步驟或者子步驟的上面，就會顯示一個彈出式視窗，上面顯示該步驟或子步驟的更加詳細的資訊如圖2彈出視窗

 

圖3

(4)     圖形執行計畫上串連每個表徵圖的箭頭粗細不同(如圖3)。箭頭的粗細表示每個表徵圖之間移動的資料行數量以及資料行大小移動所需的相對本。箭頭越粗，相對成本就越高。可以使用這個指標來快速測量一個查詢。你可能會特別關注粗箭頭以瞭解它如何影響到查詢的效能。例如，粗線頭應該在圖形執行計畫的右邊，而非左邊。如果看到它們在左邊，就意味著太多的資料行被返回，這個執行計畫也不是最佳的執行計畫.

(5)     執行計畫的每個部分都被分配了一個成本百分比(如圖2,3)。它表示這個部分耗用了整個執行計畫的多少資源。當對一個執行計畫進行分析的時候，應該將精力集中於有著高成本百分比的那些部分。這樣就可以在有限的時間裡找到可能性最大的問題，從而回報了你在時間上的投資.

(6)     你可能會注意到一個執行計畫的某些部分被執行了不止一次。作為執行計畫分析的一部分，應該將你的一些時間集中在任何執行了超過一次的那些部分上，看看是否有什麼方式減少它們執行的次數。執行的次數越少，查詢的速度就越快。

(7)     I/O與CPU成本。查詢最佳化工具使用這些數字來做出最佳選擇。它們可用來參考的一個意義是，較小的I/O或CPU成本比較大的I/O或CPU成本使用更少的伺服器資源.

use Northwind

go

--刪除叢集索引

DROP INDEX [Order Details].INDEX_ProductID

--CREATE CLUSTERED INDEX INDEX_ProductID ON [Order Details](ProductID)

SET STATISTICS IO ON

select * from [Order Details] where ProductID=42

SET STATISTICS IO OFF

資料表'Order Details'。掃描計數1，邏輯讀取11，實體讀取0，讀取前讀取0，LOB 邏輯讀取0，LOB 實體讀取0，LOB 讀取前讀取0。

use Northwind

go

--刪除叢集索引

--DROP INDEX [Order Details].INDEX_ProductID

--建立叢集索引

CREATE CLUSTERED INDEX INDEX_ProductID ON [Order Details](ProductID)

go

SET STATISTICS IO ON

select * from [Order Details] where ProductID=42

SET STATISTICS IO OFF

資料表'Order Details'。掃描計數1，邏輯讀取2，實體讀取0，讀取前讀取0，LOB 邏輯讀取0，LOB 實體讀取0，LOB 讀取前讀取0。

以上可以看出邏輯讀相差很大,由此可以通過SET STATISTICS IO ON 來查看邏輯讀，完成同一功能的不同SQL語句，邏輯讀越小查詢速度越快

(8)將滑鼠移到圖形執行計畫上的表名(以及它的表徵圖)上面，就會彈出一個視窗，從它上面可以看到一些資訊。這些資訊讓你知道是否有用到索引來從表中擷取資料，以及它是如何使用的。這些資訊包括：

(8.1)Table Scan(表掃描)：如果看到這個資訊，就說明資料表上沒有叢集索引，或者查詢最佳化工具沒有使用索引來尋找。意即資料表的每一行都被檢查到。如果資料表相對較小的話，表掃描可以非常快速，有時甚至快過使用索引。因此，當看到有執行表掃描時，第一件要做的事就是看看資料表有多少資料行。如果不是太多的話，那麼表掃描可能提供了最好的總體效能。但如果資料表大的話，表掃描就極可能需要長時間來完成，查詢效能就大受影響。在這種情況下，就需要仔細研究，為資料表增加一個適當的索引用於這個查詢。假設你發現某查詢使用了表掃描，有一個合適的非叢集索引，但它沒有用到。這意味著什麼呢？為什麼這個索引沒有用到呢？如果需要獲得的資料量相對資料表大小來說非常大，或者資料選擇性不高(意味著同一個欄位中重複的值很多)，表掃描經常會比索引掃描快。例如，如果一個資料表有10000個資料行，查詢返回1000行，如果這個表沒有叢集索引的話，那麼表掃描將比使用一個非叢集索引更快。或者如果資料表有10000個資料行，且同一個欄位(WHERE條件句有用到這個欄位)上有1000筆重複的資料，表掃描也會比使用非叢集索引更快。查看圖形執行計畫上的資料表上的彈出式視窗時，請注意”預估的資料行數(Estimated Row Count)”。這個數字是查詢最佳化工具作出的多少個資料行會被返回的最佳推測。如果執行了表掃描且”預估的資料行數”數值很高的話，就意味著返回的記錄數很多，查詢最佳化工具認為執行表掃描比使用可用的非叢集索引更快

(8.2)Index Seek(索引尋找)：索引尋找意味著查詢最佳化工具使用了資料表上的非叢集索引來尋找資料。效能通常會很快，尤其是當只有少數的資料行被返回時

(8.3)Clustered Index Seek(叢集索引尋找)：這指查詢最佳化工具使用了資料表上的叢集索引來尋找資料，效能很快。實際上，這是SQL Server能做的最快的索引尋找類型

(8.4)Clustered Index Scan(叢集索引掃描)：叢集索引掃描與表掃描相似，不同的是叢集索引掃描是在一個建有叢集索引的資料表上執行的。和一般的表掃描一樣，叢集索引掃描可能表明存在效能問題。一般來說，有兩種原因會引此叢集索引掃描的執行。第一個原因，相對於資料表上的整體資料行數目，可能需要擷取太多的資料行。查看”預估的資料行數量(Estimated Row Count)”可以對此加以驗證。第二個原因，可能是由於WHERE條件句中用到的欄位選擇性不高。在任何情況下，與標準的表掃描不同，叢集索引掃描並不會總是去尋找資料表中的所有資料，所以叢集索引掃描一般都會比標準的表掃描要快。通常來說，要將叢集索引掃描改成叢集索引尋找，你唯一能做的是重寫查詢語句，讓語句限制性更多，從而返回更少的資料行

(9)絕大多數情況下，查詢最佳化工具會對串連進行分析，按最有效率的順序，使用最有效率的連線類型來對資料表進行串連。但並不總是如此。在圖形執行計畫中你可以看到代表查詢所使用到的各種不同連線類型的表徵圖。此外，每個串連表徵圖都有兩個箭頭指向它。指向串連表徵圖的上面的箭頭代表該串連的外部表格，下面的箭頭則代表這個串連的內部表。箭頭的另一頭則指向被串連的資料表名。有時在多表串連的查詢中，箭頭的另一頭指向的並不是一個資料表，而是另一個串連。如果將滑鼠移到指向外部串連與內部串連的箭頭上，就可以看到一個彈出式視窗，告訴你有多少資料行被發送至這個串連來進行處理。外部表格應該總是比內部表含有更少的資料行。如果不是，則說明查詢最佳化工具所選擇的串連順序可能不正確

(10) 查看圖形執行計畫時，你可能會發現某個表徵圖的文字用紅色顯示，而非通常情況下的黑色。這意味著相關的表的一些統計資料遺失，統計資料是查詢最佳化工具產生一個好的執行計畫所必須的, 遺失的統計資料可以通過右鍵這個表徵圖，並選擇”建立遺失的統計資料”來建立。這時會彈出”建立遺失的統計資料”對話方塊，通過它可以很容易地建立遺失的統計資料。 當可以選擇去更新遺失的統計資料時，應該總是這樣做，因為這樣極有可能讓你正在分析的查詢語句從中獲得效能上的好處

(11) 有時你會在圖形執行計畫上看到標識了”Assert”的表徵圖。這意味著查詢最佳化工具正在驗證查詢語句是否有違反參考完整性或者條件約束。如果沒有，則沒有問題。但如果有的話，查詢最佳化工具將無法為該查詢建立執行計畫，同時會產生一個錯誤

(12) 你常常會在圖形執行計畫上看到標識成”書籤尋找(Bookmark Lookup)”的表徵圖。書籤尋找相當常見。書籤尋找的本質是告訴你查詢處理器必須從資料表或者叢集索引中來尋找它所需要的資料行，而不是從非叢集索引中直接讀取。 打比方說，如果一個查詢語句的SELECT,JOIN以及WHERE子句中的所有欄位，都不存在於那個用來定位器合查詢條件的資料行的非叢集索引中，那麼查詢最佳化工具就不得不做額外的工作在資料表或叢集索引中尋找那些滿足這個查詢語句的欄位。另一種引起書籤尋找的原因是使用了SELECT *。由於在絕大多情況下它會返回比你實際所需更多的資料，所以應該永不使用SELECT *. 從效能方面來說，書籤尋找是不理想的。因為它會請求額外的I/O開銷在欄位中尋找以返回所需的資料行。 如果認為書籤尋找防礙了查詢的效能，那麼有四種選擇可以用來避免它：可以建立WHERE子句會用到的叢集索引，利用索引交集的優勢，建立覆蓋的非叢集索引，或者(如果是SQL Server 2000/2005企業版的話)可以建立索引檢視表。如果這些都不可能，或者使用它們中的任何一個都會耗用比書籤尋找更多的資源，那麼書籤尋找就是最佳的選擇了。

(13) 有時查詢最佳化工具需要在tempdb資料庫中建立臨時工作表。如果是這樣的話，就意味著圖形執行計畫中有標識成Index Spool, Row Count Spool或者Table Spool的表徵圖。 任何時候，使用到工作表一般都會防礙到效能，因為需要額外的I/O開銷來維護這個工作表。理想情況下應該不要用到工作表。不幸的是並不能總是避免用到工作表。有時當使用工作表比其它選擇更有效率時，它的使用實際上會增強效能。不論何種情況，圖形執行計畫中的工作表都應該引起你的警覺。應該仔細檢查這樣的查詢語句，看看是否有辦法重寫查詢來避免用到工作表。有可能沒有辦法。但如果有的話，你就朝提升這個查詢的效能方面前進了一步.

(14) 在圖形執行計畫上看到流彙總(Stream Aggregate)表徵圖就意味著有對一個單一的輸入進行了彙總。當使用了DISTINCT子句，或者任何彙總函式時，如AVG, COUNT, MAX, MIN,或者SUM等，流彙總操作就相當常見。 

(15)查詢分析器與Management Studio不是唯一的可以產生、顯示查詢執行計畫的工具。SQL Server Profiler也可以顯示執行計畫，但格式是文本形式的。使用SQL Server Profiler來顯示執行計畫的一個優勢是，它能為實際啟動並執行大量查詢產生執行計畫。如果使用查詢分析器和Management Studio，則一次只能運行一個

(16)如果在查詢中使用了OPTION FAST提示，那就必須小心執行計畫的結果可能不是你所期望的。這時你所看到的執行計畫基於使用了FAST提示的結果，而不是整個查詢語句的實際執行計畫。FAST提示用來告知果詢最佳化器儘可能快地返回指定行數的資料行，即便這樣做會防礙查詢的整體效能。使用這個提示的目的在於為使用者快速返回特定行數的記錄，由此讓他們產生速度非常快速的錯覺。。當返回指定行數的資料行後，剩餘的資料行按照它們通常的速度返回。 因此，如果使用了FAST提示，那麼產生的執行計畫只是基於那些FAST返回的資料行，而非查詢要返回的所有資料行。如果想看所有資料行的執行計畫，那麼就必須移除這個FAST提示.

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/wufeng4552/archive/2009/10/20/4703976.aspx