SQL Server最佳化SELECT語句方法

Microsoft提供了三種調優查詢的主要的方法：

 

 

使用SET STATISTICS IO 檢查查詢所產生的讀和寫；

使用SET STATISTICS TIME檢查查詢的已耗用時間；

使用SET SHOWPLAN 分析查詢的查詢計劃 。

 

 

SET STATISTICS IO

 

命令SET STATISTICS IO ON 強制SQL Server 報告執行事務時I/O的實際活動。他不能和SET NOEXEC ON 選項配對使用，因為他僅僅對監測實際執行命令的I/O活動有意義。一旦這個選項被開啟，每個查詢產生包括I/O統計資訊的額外輸出。為了關閉這個選項，執行SET STATISTICS IO OFF。

 

註：這些命令也能在 Sybase Adaptive Server中運行，雖然結果集可能看起來有點不同。

 

例如，下面是在Northwind 資料庫中對於employees表上的一個行統計的簡單查詢指令碼而獲得的I/O統計資訊:

 

SET STATISTICS IO ON GO SELECT COUNT(*) FROM employees GO SET STATISTICS IO OFF GO Results: --------------- 2977 Table ‘Employees’ . Scan count 1, logical read 53, physical reads 0, readahead reads 0.

這個掃描統計告訴我們掃描執行的數量，邏輯讀顯示的是從緩衝中讀出來的頁面的數量，物理讀顯示的是從磁碟中讀的頁面的數量，Read-ahead 讀顯示了放置在緩衝中用於將來讀操作的頁面數量。

 

此外，我們執行一個系統預存程序獲得表大小的統計資訊以供我們分析：

 

sp_spaceused employees Results: name rows reserved data index_size unused -------------- -------- --------- ------- Employees 2977 2008KB 1504KB 448KB 56KB

 

通過看這些資訊我們能得到些什麼呢？

這個查詢沒有掃描整個表，在表中的資料量超過1.5M位元組，而僅僅執行了53個邏輯I/O操作就得到了結果。這表明該查詢發現了一個可用來計算結果的索引，並且掃描索引比掃描所有資料頁花費更少的I/O操作。

索引頁幾乎全部放在資料緩衝中，所以物理讀的值是零。這是因為我們之前不久是在employees表上執行了其他查詢，此時表和他的索引已被緩衝。你的查詢開銷可能有不同。

Microsoft報告沒有read-ahead（預讀）活動。在這種情況下，資料和索引頁已被緩衝起來了。當對一個非常大的表作表掃描時，read-ahead可能會半路插入進來，並且在你的查詢用到他們之前緩衝起所需的頁。當SQL Server確定你的事務是順序讀取資料庫頁並且認為他能預測到你下一步將用到的頁面時，Real-ahead會自動開啟。實際上一個獨立的SQL Server串連在你的進程之前已開始運行並為他快取資料頁。（設定和最佳化read-ahead 參數已超出這篇文章的討論範圍。

在這個例子中，該查詢已儘可能有效率地執行了，不必進一步最佳化。

SET STATISTICS TIME

 

一個事務的實耗時間是個不穩定的測量，因為這些時間和在伺服器上其他使用者的活動有關。然而，相比那些對你的使用者沒有所有意義的資料頁數字，他提供了一些實際的測量。他們關心等待查詢返回的時間消耗，不關心資料的緩衝和有效read-ahead。SET STATISTICS TIME ON命令報告下面的查詢的實際佔用時間和CPU使用方式。執行SET STATISTICS TIME OFF禁止這個選項。

 

SET STATISTICS TIME ON GO SELECT COUNT（*） FROM titleauthers GO SET STATISTICS TIME OFF GO Results: SQL Server Execution Times; Cup time=0 ms. Elapsed time=8672 ms. SQL Server Parse and Compile Time: Cpu time=10 ms ---------------- 25 (1 row(s) affected) SQL Servre Execution Times: Cpu time=0 ms.? Elapsed time=10 ms. SQL Server Parse and Compile Time: Cup time=0 ms

第一條資訊報告了多少使人困惑的佔用（實耗）時間，8672豪秒，這個資料和我們的指令碼不相關，這顯示的是之前一個命令執行以來逝去的時間。你能忽略這條資訊。SQL Server僅僅花費10毫秒時間去分析和編譯該查詢。花費0毫秒去執行他（在查詢結果可看到）。其真實的意思是這個查詢所花費的時間太短以至不能計量。最後的資訊報告了這個SET STATISTICS TIME OFF命令相關的分析及編譯花費了0毫秒。你能忽略這個資訊。最重要的資訊以加重字型反白。

 

注意實耗時間和CPU時間是以毫秒顯示。這個數字在你的電腦上可能會改動（不過不要嘗試和我們的筆記本電腦比較你機器的效能，因為這不是代表性的指標）。而且，每次你執行這個指令碼，考慮到你的SQL Server還在處理一些其他事務，你得到的統計資訊都可能有一點不同。

 

如果你需要測量一系列的查詢或預存程序的實耗期間，更好的辦法是採用程式設計的方式（如下所示）。當你運行多個命令時你不得不進行手工合計，這是因為STATISTICS TIME只報告單個查詢的期間。想象一下，當你對一個在迴圈裡執行成千上萬次查詢的指令碼進行計時的情況下，將面臨大量的輸出和大量的手工工作。

 

相反，考慮下面這個指令碼在事務的前後分別捕捉時間並以秒的形式報告總期間（你也能使用毫秒）：

 

DECLARE @start_time DATETIME SELECT @start_time=GETDATE() <any query or a script that you want to time, without a GO> SELECT ’Elapsed Time,sec’ =DATEDIFF(second, @start_time,GETDATE()) GO

如果你的指令碼被GO分成幾步,你不能用本地變數來儲存開始時間。變數在GO命令執行後就被銷毀。但你能象這樣在暫存資料表裡儲存開始時間。

 

CREATE TABLE #save_time (start_time DATETIME NOT NULL) INSERT #save_time VALUES ( GETDATE()) GO < any script that you want to time (may include GO) > GO SELECT ‘Elapsed Time, sec’ = DATEDIFF ( second, start_time, GETDATE()) FROM TABLE #save_time DROP TABLE #save_time GO

請注意，SQL Server’s DATETIME 資料類型儲存的時間是以3毫秒為增量。使用DATETIME資料類型不可能獲得比這更細的時間粒紋。

 

 

SHOWPLAN 輸出和分析

 

這篇文章通過explain plan(解析計劃)解釋Microsoft SQL Server 2000 使用SET SHOWPLAN_TEXT ON 所輸出內容的意義和用處。一個explain plan（也被叫做查詢計劃，執行計畫，或最佳化計劃）提供了資料庫查詢引擎執行SQL事務的十分周詳的步驟。知道怎麼閱讀explain plan有助於提高高端查詢調整和最佳化的能力。

 

註：大部分的例子要麼是基於PUBS資料庫，要麼是基於SQL Server系統資料表的.針對這些執行個體，我們給非常多表增加了好幾萬條記錄以便於在評估查詢計劃時體現查詢最佳化工具的實際作用。

 

 

SHOWPLAN 輸出:

 

我們喜歡查詢最佳化工具的一個功能就是以查詢執行計畫的形式提供反饋。目前我們能更為周詳地說明語句的執行，並描述你可能在查詢計劃中遇見的訊息。理解這個輸出能使你的最佳化水平達到一個新高度。你能不再把最佳化器視為一個能處理你的查詢語句的有魔力的“黑盒子”，

 

下面的命令指示SQL Server顯示在同一個串連（或進程）中每個查詢的執行計畫，或將這個選項關閉。

 

SET SHOWPLAN_TEXT { ON | OFF }

 

預設情況下，SHOWPLAN_TEXT ON使得你正在審查的代碼不被執行。而是，SQL Server 編譯這些代碼並且顯示這個查詢的執行計畫。直到你發出SET.SHOWPLAN_TEXT OFF命令後他才停止。

 

 

其他有用的SET命令

 

有各種各樣對調優和調試有用的SET命令。在這篇檔案前面我們提到了SET STATISTICS命令。在某些情況下你能發現其他SET命令的用處:

 

 

 

SET NOEXEC{ ON | OFF}: 檢查你的Transact-SQL代碼的文法，包括編譯該代碼但不執行。當使用延遲名字解析時，這對檢查一個查詢語句的文法是非常有用的。即，當一個表還沒有建立時，你就能檢查基於該表的查詢語句的文法。

SET FMTONLY{ ON | OFF }:僅向用戶端返回查詢的中繼資料。對於SELECT語句，通常僅返回列頭。

SET PARSEONLY { ON | OFF }:檢查你的Transact-SQL代碼的文法，但不編譯或執行該代碼。

 

一旦設為 ON這些命令將一直有效，直到你手工關閉他們。這些設定不是馬上生效，但他們將從下一個步驟開始生效，換言之，你必須在SHOWPLAN or NOEXEC等設定生效前發出GO命令。

 

典型的T-SQL代碼如下，獲得一個查詢的執行計畫，而不實際執行。

 

SET SHOWPLAN_TEXT ON GO <query> GO SET SHOWPLAN_TEXT OFF GO

我們將展示幾個SHOWPLAN_TEXT 輸出的例子。為了避免冗餘，我將不重複上面SET命令的展示.在這個部分裡所提供的查詢都將代替這個指令碼中的標籤並且都象上面展示的相同“封裝”。

 

事實上SHOWPLAN有兩個版本：SHOWPLAN_ALL和SHOWPLAN_TEXT。他們提供的資訊基本上相同。然而，SHOWPLAN_ALL輸出的結果是準備給映像查詢工具的而不是給聽眾的。我們在這整篇文章中將用到SHOWPLAN_TEXT，可提供更可讀的格式輸出。下面的簡單查詢選擇authors表的所有行。因為我們沒有提供where子句所以他除了掃描整個表別無選擇：select * form authors

 

在下面的表中SHOWPLAN_TEXT輸出的結果沒有格式化，我們不得不從SHOWPLAN_ALL的輸出中整理出更多的可讀資訊：

 

SHOWPLAN_TEXT SHOWPLAN_ALL StmtTextStmtText --------------------------------- |--Clustered Index Scan |--Clustered Index Scan (OBJECT:([pubs].[dbo]. (OBJECT:([pubs].[dbo]. [authors].[UPKCL_auidind])) [authors].[UPKCL_auidind])) StmtID NodeID Parent --------- -------- ------- 2 2 1 PhysicalOp LogicalOp ------------ ---------------- NULL NULL Clustered Index scan Clustered Index scan Argument --------------------------------------------- 1 OBJECT:([pubs].[dbo]. ].[UPKCL_auidind]) DefindedValues --------------------------------------- 23 _ <all columns in table>_ EstimatedRows EstimateIO EstimatedCPU ------------------ ------------- -------- 23 NULL NULL 23 0.01878925 5.1899999E-5 AvgRowSizeotalSubtreeCost ------------------------------------ NULL 3.7682299E-2 1113.7682299E-2 OutputList ----------------------------------------- NULL _ <all columns in table>_ Warnings TypeParallel EstimateExecutions -------- ------------------------- NULL SELECT 0NULL NULPLAN_ROW01.0

 

這裡重要的不同是SHOWPLAN_ALL語句返回了非常多有用的調優資訊，但這些非常難理解和應用。

 

 

SHOWPLAN 操作

 

SHOWPLAN操作，有時叫做“標籤”(tag)，其中一部分操作非常清晰地說明了SQL Server的做法，而其他一些操作將把人難住。這些操作分成物理操作和邏輯操作。物理操作描述被用來處理查詢的物理演算法，例如，執行一個索引尋找。邏輯操作描述語句中使用的關係代數操作，如彙總運算等。SHOWPLAN的結果被細分非具體的步驟分成幾步。每個查詢的物理操作代表一個獨立步驟。步驟通常會伴有一個邏輯操作，但不是所有的步驟都包括邏輯操作。此外，大部分的步驟都有一個操作（要麼邏輯操作要麼物理操作）和一個參數。參數是操作所影響的查詢組件。關於所有執行計畫步驟的討論內容非常繁多。