SQL2005性能分析一些细节功能你是否有用到?(三)
2010-10-01 10:19:40 来源:WEB开发网第一: SET STATISTICS PROFILE ON
当我们比较查询计划中那一个最好时,事实上我们更愿意用SET STATISTICS PROFILE ON,而不是SET SHOWPLAN_TEXT ON。它可以告诉你每种选择的或多或少的查询消耗情况;你还可以同时运行两个或更多查询来看哪个执行的最好。
运行SET STATISTICS PROFILE ON 后,发出现很多信息,这里以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)
除了显示出当前SQL语句外,还详细的给出了实际运行的情况,怎样查找索引,怎样扫描表,又是怎样排序等等.
更多精彩
赞助商链接