分析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
   
    usedMuch
into #temp
from LijiDownload
group by sumIndex
order by usedMuch desc
select distinct t.usedMuch,substring(L.TextData,
   
   ,
   
   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,
   
   ,
   
   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