分析SQL语句的性能-- Compare NText Column value by using CheckSum
Posted on 2006-07-14 11:47 C#Hunter 阅读(915) 评论(0) 编辑 收藏 举报由于要分析SQL Profiler捕获的SQL 语句的性能,需要找出执行频率高,用时长的语句。
SQL Profiler生成的表如下:
CREATE TABLE [dbo].[LijiDownload](
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
[EventClass] [int] NULL,
[TextData] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ApplicationName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NTUserName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LoginName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CPU] [int] NULL,
[Reads] [bigint] NULL,
[Writes] [bigint] NULL,
[Duration] [bigint] NULL,
[ClientProcessID] [int] NULL,
[SPID] [int] NULL,
[StartTime] [datetime] NULL,
[BinaryData] [image] NULL,
[sumIndex] [int] NULL, ---为了分析加入的
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
由于Text类型的数据不能比较和做Group等运算。所以引入sumIndex列来识别相同的TextData值。
--生成sumIndex列的数据
update LijiDownload set sumIndex=checkSum(substring(TextData,0,500))
--500 :可以指定为捕获的SQL 语句的最大长度。
--找出使用频率最高的前10条语句:
select top 10 sumIndex,count(sumIndex) as usedMuch
into #temp
from LijiDownload
group by sumIndex
order by usedMuch desc
select distinct t.usedMuch,substring(L.TextData,0,500)as TextData
from #temp t inner join LijiDownload L
on t.sumIndex = L.sumIndex
order by t.usedmuch desc
--找出总用时最高的前10条语句
select distinct substring(L.TextData,0,500)as TextData,b.TotalDuration
from LijiDownload L inner join
(select top 10 sum(Duration) as TotalDuration,sumIndex from LijiDownload
where eventclass=41 -- 类型为SQL语句
group by sumIndex
order by TotalDuration desc) b
on L.sumIndex=b.sumIndex
order by b.TotalDuration desc