? ?? 繼上篇: SQL2005性能分析一些細(xì)節(jié)功能你是否有用到?(二)
????
第一: SET STATISTICS PROFILE ON
????????? 當(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)。
注:
? ? 本文引用:MSDN
???
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對(duì)您有幫助,請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長(zhǎng)非常感激您!手機(jī)微信長(zhǎng)按不能支付解決辦法:請(qǐng)將微信支付二維碼保存到相冊(cè),切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
