????????? 當(dāng)我們比較查詢計(jì)劃中那一個(gè)最好時(shí)，事實(shí)上我們更愿意用SET STATISTICS PROFILE ON,而不是SET SHOWPLAN_TEXT ON。它可以告訴你每種選擇的或多或少的查詢消耗情況;你還可以同時(shí)運(yùn)行兩個(gè)或更多查詢來看哪個(gè)執(zhí)行的最好。

????????? 運(yùn)行 SET STATISTICS PROFILE ON 后,發(fā)出現(xiàn)很多信息,這里以 stmtText 來說明下:

???????? StmtText:
select * from
???????? (
?? ??? ??? ?select *,
?? ??? ??? ??? row_number() over (order by card_no desc) as RowNum
????????????? from tblName
????????? ) as tbl

where RowNum between 1 and 20

?

|--Filter(WHERE:([Expr1003]>=(1) AND [Expr1003]<=(20)))?? ?
? |--Top(TOP EXPRESSION:(CASE WHEN (20) IS NULL OR (20)<(0) THEN (0) ELSE (20) END))?? ?
??? |--Sequence Project(DEFINE:([Expr1003]=row_number))
???? |--Compute Scalar(DEFINE:([Expr1007]=(1)))
?????? |--Segment? ?
??????? |--Nested Loops(Inner Join, OUTER REFERENCES:([bdg_retail].[dbo].[Card_Ext].[Id], [Expr1005]) WITH ORDERED PREFETCH)
???????? |--Index Scan(OBJECT:([bdg_retail].[dbo].[Card_Ext].[IX_Card_ext_Card_No]), ORDERED BACKWARD)
????????? |--Clustered Index Seek(OBJECT:([bdg_retail].[dbo].[Card_Ext].[PK_CARD_EXT]), SEEK:([bdg_retail].[dbo].[Card_Ext].
??????????? [Id]=[bdg_retail].[dbo].[Card_Ext].[Id]) LOOKUP ORDERED FORWARD)

?????????? 除了顯示出當(dāng)前SQL語句外,還詳細(xì)的給出了實(shí)際運(yùn)行的情況,怎樣查找索引,怎樣掃描表,又是怎樣排序等等.

? ? ? ? ????? Nested Loops:嵌套查詢;?

? ? ? ? ? ? ? Index Scan:索引查找;

? ? ? ? ????? Clustered Index Seek:聚集索引查找

????? 第二:sp_spaceused

???????? 作用: 獲得表大小的統(tǒng)計(jì)信息以供我們分析：

? ? ? ?? 案例:

???????? sp_spaceused employees
???????? Results:
???????? name rows reserved data index_size unused
???????? -------------- -------- --------- ------- -------------- ---------

???????? Employees 2977 2008KB 1504KB 448KB 56KB

? ? ? ?? 效果圖:

???????? 返回內(nèi)容說明: ?? ??

??????????????? Name ?? ?為其請(qǐng)求空間使用信息的表名。
??????????????? Rows? 表中現(xiàn)有的行數(shù)。
??????????????? reserved? 表保留的空間總量。
??????????????? Data 表中的數(shù)據(jù)所使用的空間量。
??????????????? index_size 表中的索引所使用的空間量。
??????????????? Unused 表中未用的空間量。

????????? 備注: sp_spaceused 計(jì)算數(shù)據(jù)和索引使用的磁盤空間量以及當(dāng)前數(shù)據(jù)庫(kù)中的表所使用的磁盤空間量。如果沒有給定 objname，sp_spaceused 則報(bào)告整個(gè)當(dāng)前數(shù)據(jù)庫(kù)所使用的空間。

????????? 權(quán)限: 執(zhí)行權(quán)限默認(rèn)授予 public 角色。

????? 第三:SQL2005 中的排名函數(shù)row_number()

?????????? 分頁(yè)算法有很多種,這里我想說一下我一直在用的分頁(yè)方法,SQL2005的新特性:排名函數(shù)中的row_number()
ROW_NUMBER (Transact-SQL)

?????????? 定義: 返回結(jié)果集分區(qū)內(nèi)行的序列號(hào)，每個(gè)分區(qū)的第一行從 1 開始。

?????????? 語法: ROW_NUMBER ( )???? OVER ( [ <partition_by_clause> ] <order_by_clause> )

?????????? 參數(shù): 1:<partition_by_clause>:將 FROM 子句生成的結(jié)果集劃入應(yīng)用了 ROW_NUMBER 函數(shù)的分區(qū)。

????????????????? 2:<order_by_clause>:確定將 ROW_NUMBER 值分配給分區(qū)中的行的順序。有關(guān)詳細(xì)信息，請(qǐng)參閱 ORDER BY 子句 (Transact-SQL)。當(dāng)在排名函數(shù)中使用 <order_by_clause> 時(shí)，不能用整數(shù)表示列。

?????????? 返回類型: bigint?

?????????? 備注: ORDER BY 子句可確定在特定分區(qū)中為行分配唯一 ROW_NUMBER 的順序。

?????????? 本節(jié)意圖: 以前我一直有一個(gè)誤區(qū),就是認(rèn)為 只要是分頁(yè)時(shí)按需所取(查詢第幾頁(yè)就取第只取幾頁(yè)的數(shù)據(jù)),效率就會(huì)特別高 ,后來用上了IO分析,才知道并不像我想像中的那樣完美(取任何一頁(yè)速度都是一樣快).

? ? ? ? ?? 案例:

--取第一頁(yè)
(20 row(s) affected)

Table 'Card_Ext'. Scan count 1, logical reads 92 , physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

?

--取第一百頁(yè)
(20 row(s) affected)

Table 'Card_Ext'. Scan count 1, logical reads 8157 , physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

?

?--取第一萬頁(yè) ?
(20 row(s) affected)

Table 'Card_Ext'. Scan count 1, logical reads 81322 , physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

?????????????? 解決方案: 大數(shù)據(jù)分頁(yè)中,無論你采用什么樣的分頁(yè)算法,都會(huì)出現(xiàn)性能瓶頸,所以可以采用 top n 的方法來折中一下:當(dāng)實(shí)際查詢結(jié)果特別多時(shí),只選取前n條.

????????????? 本節(jié)結(jié)論: 上面的結(jié)果都是在已經(jīng)有數(shù)據(jù)緩存的情況下運(yùn)行的結(jié)果,所以只看到了邏輯讀,并沒有出現(xiàn)物理讀取的記錄.記錄顯示,邏輯讀數(shù)量在不斷變化,根據(jù)用戶取的頁(yè)數(shù)大小成倍增加,也就是說與頁(yè)數(shù)大小成正比.原因是row_number()的產(chǎn)生是在數(shù)據(jù)全部查詢出來后再按照排序順序從一開始生成的,所以它要把頁(yè)數(shù)之前的所有數(shù)據(jù)都先裝成內(nèi)存,才能生成.
這樣就非常直接的回答了為什么取數(shù)據(jù)越到最后速度越慢的原因了.

?????? 順便說下在運(yùn)用sp_help命令時(shí),是不能同時(shí)運(yùn)行執(zhí)行計(jì)劃的.否則會(huì)報(bào)這樣的錯(cuò):Msg 262, Level 14, State 4, Procedure sp_help, Line 88 SHOWPLAN permission denied in database 'master'.

? ? ? 總結(jié):性能調(diào)優(yōu)是一項(xiàng)特別細(xì)的工作,往往更改一個(gè)小小的語句性能就會(huì)發(fā)生翻天覆地的變化;要在不斷的實(shí)踐中總結(jié)經(jīng)驗(yàn)。