登录
【IT168技术文档】
由于要分析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
| 第1页： 第1页 |
