排查数据库性能的常用sql语句

检测死锁：

 

View Code

 1  declare  @spid  int,
 2      @bl  int,
 3      @intTransactionCountOnEntry   int,    
 4      @intRowcount     int,       
 5      @intCountProperties    int,   
 6      @intCounter     int 
 7  create  table #tmp_lock_who 
 8     ( id  int  identity( 1, 1),
 9         spid  smallint,
10         bl  smallint
11     ) 
12      IF  @@ERROR <> 0 
13          print  @@ERROR  
14      insert  into #tmp_lock_who(spid,bl) 
15          select   0 ,blocked    from ( select  *  from sysprocesses  where  blocked > 0 ) a    
16              where  not  exists( select  *  from ( select  *  from sysprocesses  where  blocked > 0 ) b     where a.blocked =spid)   
17          union
18          select spid,blocked  from sysprocesses  where  blocked > 0 
19      IF  @@ERROR <> 0 
20          print  @@ERROR  
21      --  找到临时表的记录数
22      select   @intCountProperties  =  Count( *), @intCounter  =  1  from #tmp_lock_who  
23      IF  @@ERROR <> 0 
24          print  @@ERROR   
25      if  @intCountProperties = 0  
26          select  ' 现在没有阻塞和死锁信息 '  as message
27      --  循环开始
28      while  @intCounter  <=  @intCountProperties
29          begin
30              --  取第一条记录  
31              select   @spid  = spid, @bl  = bl   from #tmp_lock_who 
32              where Id  =  @intCounter  
33                  begin  
34                      if  @spid  = 0             
35                          select  ' 引起数据库死锁的是:  ' +  CAST( @bl  AS  VARCHAR( 10))  +  ' 进程号,其执行的SQL语法如下 '
36                      else            
37                      select  ' 进程号SPID： ' +  CAST( @spid  AS  VARCHAR( 10)) +  ' 被 '  +  ' 进程号SPID： ' +  CAST( @bl  AS  VARCHAR( 10))  + ' 阻塞,其当前进程执行的SQL语法如下 '  DBCC INPUTBUFFER ( @bl )  end 
38                      --  循环指针下移 
39                      set  @intCounter  =  @intCounter  +  1
40          end
41      drop  table #tmp_lock_who

 

 

杀死死锁和进程

 

View Code

 1  declare  @sql   nvarchar( 500)     
 2  declare  @spid  nvarchar( 20)    
 3  declare #tb  cursor  for         select spid = cast(spid  as  varchar( 20))  from master..sysprocesses  -- where dbid=db_id(@dbname)   
 4  open #tb    
 5  fetch  next  from #tb  into  @spid   
 6  while  @@fetch_status = 0   
 7      begin          
 8          exec( ' kill  ' + @spid)       
 9          fetch  next  from #tb  into  @spid    
10      end     
11  close #tb    
12  deallocate #tb

 查看锁进程

 

View Code

 1  create  table #t
 2 (req_spid  int,
 3 obj_name sysname)
 4  declare  @s  nvarchar( 4000),
 5  @rid  int,
 6  @dbname sysname,
 7  @id  int,
 8  @objname sysname
 9  declare tb  cursor  for     
10      select  distinct req_spid,dbname = db_name(rsc_dbid),rsc_objid    
11      from master..syslockinfo  where rsc_type  in( 4, 5)
12  open tb
13  fetch  next  from tb  into  @rid, @dbname, @id
14  while  @@fetch_status =0 begin    
15      set  @s = ' select @objname=name from [ ' + @dbname + ' ]..sysobjects where id=@id '    
16      exec sp_executesql  @s,N ' @objname sysname out,@id int ', @objname out, @id    
17      insert  into #t  values( @rid, @objname)    
18      fetch  next  from tb  into  @rid, @dbname, @id
19  end
20  close tb
21  deallocate tb
22  select 进程id =a.req_spid
23 ,数据库 = db_name(rsc_dbid)
24 ,类型 = case rsc_type  when  1  then  ' NULL 资源（未使用） '
25      when  2  then  ' 数据库 '
26      when  3  then  ' 文件 ' 
27      when  4  then  ' 索引 '
28      when  5  then  ' 表 '
29      when  6  then  ' 页 ' 
30      when  7  then  ' 键 '
31      when  8  then  ' 扩展盘区 ' 
32      when  9  then  ' RID（行 ID) '
33      when  10  then  ' 应用程序 '
34  end
35 ,对象id =rsc_objid
36 ,对象名 =b.obj_name
37 ,rsc_indid  from master..syslockinfo a  left  join #t b  on a.req_spid =b.req_spid
38  go
39  drop  table #t

查看阻塞信息

 

View Code

 1  SELECT SPID =p.spid, 
 2 
 3        DBName  =  convert( CHAR( 20),d.name), 
 4 
 5        ProgramName  = program_name, 
 6 
 7        LoginName  =  convert( CHAR( 20),l.name), 
 8 
 9        HostName  =  convert( CHAR( 20),hostname), 
10 
11        Status  = p.status, 
12 
13        BlockedBy  = p.blocked, 
14 
15        LoginTime  = login_time, 
16 
17        QUERY  =  CAST( TEXT  AS  VARCHAR( MAX)) 
18 
19  FROM   MASTER.dbo.sysprocesses p 
20 
21         INNER  JOIN MASTER.dbo.sysdatabases d 
22 
23           ON p.dbid  = d.dbid 
24 
25         INNER  JOIN MASTER.dbo.syslogins l 
26 
27           ON p.sid  = l.sid 
28 
29         CROSS APPLY sys.dm_exec_sql_text(sql_handle) 
30 
31  WHERE  p.blocked  =  0 
32      and d.name = ' 数据库名字 '
33 
34         AND  EXISTS ( SELECT  1 
35 
36                     FROM   MASTER..sysprocesses p1 
37 
38                     WHERE  p1.blocked  = p.spid)

 

枚举索引

 1 SELECT  索引名称 = a.name ,
 2         表名 = c.name ,
 3         索引字段名 = d.name ,
 4         索引字段位置 = d.colid,
 5         c.crdate
 6 FROM    sysindexes a
 7         JOIN sysindexkeys b ON a.id = b.id
 8                                AND a.indid = b.indid
 9         JOIN sysobjects c ON b.id = c.id
10         JOIN syscolumns d ON b.id = d.id
11                              AND b.colid = d.colid
12 WHERE   a.indid NOT IN ( 0, 255 )  
13 -- and   c.xtype='U'   and   c.status>0 --查所有用户表  
14         --AND c.name = 'ORDER' --查指定表  
15 ORDER BY 
16         c.crdate desc,
17         c.name ,
18         a.name ,

View Code