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

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

 

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

 

 

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

 

 

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

 

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

使用SET STATISTICS TIME檢查查詢的運(yùn)行時(shí)間；

使用SET SHOWPLAN 分析查詢的查詢計(jì)劃 。

SET STATISTICS IO

 

命令SET STATISTICS IO>

 

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

 

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

 

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

 

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

 

 

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

 

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

 

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

索引頁(yè)幾乎全部放在數(shù)據(jù)緩存中，所以物理讀的值是零。這是因?yàn)槲覀冎安痪檬窃趀mployees表上執(zhí)行了其他查詢，此時(shí)表和它的索引已經(jīng)被緩存。你的查詢開銷可能有不同。

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

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

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

SET STATISTICS TIME

 

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

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

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

 

 

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

 

 

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

 

 

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

 

 

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分成幾步,你不能用本地變量來(lái)保存開始時(shí)間。變量在GO命令執(zhí)行后就被銷毀。但你可以象這樣在臨時(shí)表里保存開始時(shí)間。

 

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  

請(qǐng)注意，SQL Server’s DATETIME 數(shù)據(jù)類型存儲(chǔ)的時(shí)間是以3毫秒為增量。使用DATETIME數(shù)據(jù)類型不可能獲得比這更細(xì)的時(shí)間粒度。

 SHOWPLAN 輸出和分析

 

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

 

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

SHOWPLAN 輸出:

 

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

 

 

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

 

 

SET SHOWPLAN_TEXT {>

 

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

 

其它有用的SET命令

 

 

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

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

 

一旦設(shè)為>

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

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

 

事實(shí)上SHOWPLAN有兩個(gè)版本：SHOWPLAN_ALL和SHOWPLAN_TEXT。他們提供的信息基本上一樣。然而，SHOWPLAN_ALL輸出的結(jié)果是準(zhǔn)備給圖形查詢工具的而不是給聽(tīng)眾的。我們?cè)谶@整篇文章中將用到SHOWPLAN_TEXT，可提供更可讀的格式輸出。下面的簡(jiǎn)單查詢選擇authors表的所有行。因?yàn)槲覀儧](méi)有提供where子句所以它除了掃描整個(gè)表別無(wú)選擇：select * form authors

 

 

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

 

SHOWPLAN 操作

 

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

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

 

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