以下的文章主要向大家講述的是SQL Server 2000優(yōu)化SELECT 語句的實際操作方案，此文講述的是SQL Server SQL語句優(yōu)化系列文章中的***篇。該系列文章描述了在Micosoft’s SQLServer2000關(guān)系數(shù)據(jù)庫管理系統(tǒng)中優(yōu)化SELECT語句的基本技巧。

我們討論的技巧可在Microsoft's SQL Enterprise Manager或 Microsoft SQL Query Analyzer（查詢分析器）提供的Microsoft圖形用戶界面使用。

 

除調(diào)優(yōu)方法外，我們給你展示了***實踐，你可應(yīng)用到你的SQL語句中以提高性能（所有的例子和語法都已在Microsoft SQL Server 2000中驗證）。

 

 

閱讀該系列文章后，你應(yīng)該對Microsoft 工具包中提供的查詢SQL Server 2000優(yōu)化工具和技巧有一個基本的了解，我們將提供包含各種各樣的以提高性能和加速數(shù)據(jù)讀取操作的查詢技巧。

 

 

Microsoft提供了三種調(diào)優(yōu)查詢的主要的方法：

 

使用SET STATISTICS IO 檢查查詢所產(chǎn)生的讀和寫；

使用SET STATISTICS TIME檢查查詢的運行時間；

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

SET STATISTICS IO

 

命令SET STATISTICS IO>

 

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

 

例如，下面是在Northwind 數(shù)據(jù)庫中對于employees表上的一個行統(tǒng)計的簡單查詢腳本而獲得的I/O統(tǒng)計信息:

 

SET STATISTICS IO>這個掃描統(tǒng)計告訴我們掃描執(zhí)行的數(shù)量，邏輯讀顯示的是從緩存中讀出來的頁面的數(shù)量，物理讀顯示的是從磁盤中讀的頁面的數(shù)量，Read-ahead 讀顯示了放置在緩存中用于將來讀操作的頁面數(shù)量。

 

此外，我們執(zhí)行一個系統(tǒng)存儲過程獲得表大小的統(tǒng)計信息以供我們分析：

 

 

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

 

通過看這些信息我們能得到些什么呢？

 

這個查詢沒有掃描整個表，在表中的數(shù)據(jù)量超過1.5M字節(jié)，而僅僅執(zhí)行了53個邏輯I/O操作就得到了結(jié)果。這表明該查詢發(fā)現(xiàn)了一個可用來計算結(jié)果的索引，并且掃描索引比掃描所有數(shù)據(jù)頁花費更少的I/O操作。

索引頁幾乎全部放在數(shù)據(jù)緩存中，所以物理讀的值是零。這是因為我們之前不久是在employees表上執(zhí)行了其他查詢，此時表和它的索引已經(jīng)被緩存。你的查詢開銷可能有不同。

Microsoft報告沒有read-ahead（預(yù)讀）活動。在這種情況下，數(shù)據(jù)和索引頁已經(jīng)被緩存起來了。當(dāng)對一個很大的表作表掃描時，read-ahead可能會半路插入進(jìn)來，并且在你的查詢用到它們之前緩存起所需的頁。

當(dāng)SQL Server確定你的事務(wù)是順序讀取數(shù)據(jù)庫頁并且認(rèn)為它能預(yù)測到你下一步將用到的頁面時，Real-ahead會自動打開。實際上一個獨立的SQL Server連接在你的進(jìn)程之前已開始運行并為它緩存數(shù)據(jù)頁。（配置和優(yōu)化read-ahead 參數(shù)已超出這篇文章的討論范圍。

在這個例子中，該查詢已經(jīng)盡可能有效率地執(zhí)行了，不必進(jìn)一步SQL Server 2000優(yōu)化。

SET STATISTICS TIME

 

一個事務(wù)的實耗時間是一個不穩(wěn)定的測量，因為這些時間與在服務(wù)器上其他用戶的活動有關(guān)。然而，相比那些對你的用戶沒有任何意義的數(shù)據(jù)頁數(shù)字，它提供了一些實際的測量。他們關(guān)心等待查詢返回的時間消耗，不關(guān)心數(shù)據(jù)的緩存和有效的read-ahead。

SET STATISTICS TIME> SET STATISTICS TIME>***條信息報告了多少使人困惑的占用（實耗）時間，8672豪秒，這個數(shù)據(jù)與我們的腳本不相關(guān)，這顯示的是之前一個命令執(zhí)行以來逝去的時間。你可以忽略這條信息。SQL Server僅僅花費10毫秒時間去分析和編譯該查詢。

花費0毫秒去執(zhí)行它（在查詢結(jié)果可看到）。其真實的意思是這個查詢所花費的時間太短以至不能計量。***的信息報告了這個SET STATISTICS TIME OFF命令相關(guān)的分析及編譯花費了0毫秒。你可以忽略這個信息。最重要的信息以加重字體突出顯示。

 

 

注意實耗時間和CPU時間是以毫秒顯示。這個數(shù)字在你的電腦上可能會改變（但是不要嘗試與我們的筆記本電腦比較你機器的性能，因為這不是代表性的指標(biāo)）。而且，每次你執(zhí)行這個腳本，考慮到你的SQL Server還在處理一些其他事務(wù)，你得到的統(tǒng)計信息都可能有一點不同。

 

 

如果你需要測量一系列的查詢或者存儲過程的實耗持續(xù)時間，更好的辦法是采用程序設(shè)計的方式（如下所示）。當(dāng)你運行多個命令時你不得不進(jìn)行手工合計，這是因為STATISTICS TIME只報告單個查詢的持續(xù)時間。想象一下，當(dāng)你對一個在循環(huán)里執(zhí)行成千上萬次查詢的腳本進(jìn)行計時的情況下，將面臨大量的輸出和大量的手工工作。

 

 

相反，考慮下面這個腳本在事務(wù)的前后分別捕獲時間并以秒的形式報告總持續(xù)時間（你也可以使用毫秒）：

 

 

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命令執(zhí)行后就被銷毀。但你可以象這樣在臨時表里保存開始時間。

 

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 數(shù)據(jù)類型存儲的時間是以3毫秒為增量。使用DATETIME數(shù)據(jù)類型不可能獲得比這更細(xì)的時間粒度。

 SHOWPLAN 輸出和分析

 

這篇文章通過explain plan(解析計劃)解釋Microsoft SQL Server 2000 使用SET SHOWPLAN_TEXT>

 

注：大部分的例子要么是基于PUBS數(shù)據(jù)庫，要么是基于SQL Server系統(tǒng)表的.針對這些實例，我們給很多表增加了好幾萬條記錄以便于在評估查詢計劃時體現(xiàn)查詢優(yōu)化器的實際作用。

SHOWPLAN 輸出:

 

我們喜歡查詢優(yōu)化器的一個功能就是以查詢執(zhí)行計劃的形式提供反饋?，F(xiàn)在我們可以更為詳細(xì)地說明語句的執(zhí)行，并描述你可能在查詢計劃中遇到的消息。理解這個輸出可以使你的優(yōu)化水平達(dá)到一個新高度。你可以不再把SQL Server 2000優(yōu)化器視為一個可以處理你的查詢語句的有魔力的“黑盒子”，

 

 

下面的命令指示SQL Server顯示在同一個連接（或進(jìn)程）中每個查詢的執(zhí)行計劃，或?qū)⑦@個選項關(guān)閉。

 

 

SET SHOWPLAN_TEXT {>

 

默認(rèn)情況下，SHOWPLAN_TEXT>

 

其它有用的SET命令

 

 

有各種各樣對調(diào)優(yōu)和調(diào)試有用的SET命令。在這篇文檔前面我們提到了SET STATISTICS命令。在某些情況下你可以發(fā)現(xiàn)其它SET命令的用處:

SET NOEXEC{>SET FMTONLY{>SET PARSEONLY {> 

 

一旦設(shè)為>

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

SET SHOWPLAN_TEXT>我們將展示幾個SHOWPLAN_TEXT 輸出的例子。為了避免冗余，我將不重復(fù)上面SET命令的展示.在這個部分里所提供的查詢都將代替這個腳本中的標(biāo)簽并且都象上面展示的一樣“包裝”。

 

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

 

 

在下面的表中SHOWPLAN_TEXT輸出的結(jié)果沒有格式化，我們不得不從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語句返回了很多有用的調(diào)優(yōu)信息，但這些很難理解和應(yīng)用。

 

SHOWPLAN 操作

 

SHOWPLAN操作，有時叫做“標(biāo)簽”(tag)，其中一部分操作非常清晰地說明了SQL Server的做法，而其它一些操作將把人難住。這些操作分成物理操作和邏輯操作。物理操作描述被用來處理查詢的物理算法，例如，執(zhí)行一個索引查找。

邏輯操作描述語句中使用的關(guān)系代數(shù)操作，如聚合運算等。SHOWPLAN的結(jié)果被細(xì)分非具體的步驟分成幾步。每個查詢的物理操作代表一個獨立步驟。步驟通常會伴有一個邏輯操作，但不是所有的步驟都包括邏輯操作。此外，大部分的步驟都有一個操作（要么邏輯操作要么物理操作）和一個參數(shù)。參數(shù)是操作所影響的查詢組件。關(guān)于所有執(zhí)行計劃步驟的討論內(nèi)容非常繁多。

 

以上的相關(guān)內(nèi)容就是對SQL Server 2000優(yōu)化SELECT語句方法的介紹，望你能有所收獲。