分析SQL语句的性能-- Compare NText Column value by using CheckSum

由于要分析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