分析SQL语句的性能

由于要分析SQL Profiler捕获的SQL 语句的性能，需要找出执行频率高，用时长的语句。

　　SQL Profiler生成的表如下：

CREATE TABLE [dbo].[LijiDownload]([RowNumber] [int] IDENTITY(,) 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,,500))

　　500 ：可以指定为捕获的SQL 语句的最大长度。

　　找出使用频率最高的前10条语句：

select top 10 sumIndex,count(sumIndex) as usedMuchinto #tempfrom LijiDownloadgroup by sumIndexorder by usedMuch descselect distinct t.usedMuch,substring(L.TextData,,500as TextDatafrom #temp t inner join LijiDownload Lon t.sumIndex = L.sumIndexorder by t.usedmuch desc

　　找出总用时最高的前10条语句

select distinct substring(L.TextData,,500as TextData,b.TotalDurationfrom LijiDownload L inner join(select top 10 sum(Duration) as TotalDuration,sumIndex from LijiDownloadwhere eventclass=41 -- 类型为SQL语句group by sumIndexorder by TotalDuration desc) bon L.sumIndex=b.sumIndexorder by b.TotalDuration desc