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

检测死锁：

 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

杀死死锁和进程

 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

 查看锁进程

 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=0begin    
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

查看阻塞信息

 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)

枚举索引

SELECT  索引名称 = a.name ,
        表名 = c.name ,
        索引字段名 = d.name ,
        索引字段位置 = d.colid,
        c.crdate
FROM    sysindexes a
        JOIN sysindexkeys b ON a.id = b.id
                               AND a.indid = b.indid
        JOIN sysobjects c ON b.id = c.id
        JOIN syscolumns d ON b.id = d.id
                             AND b.colid = d.colid
WHERE   a.indid NOT IN ( 0, 255 )  
-- and   c.xtype='U'   and   c.status>0 --查所有用户表  
        --AND c.name = 'ORDER' --查指定表  
ORDER BY 
        c.crdate desc,
        c.name ,
        a.name ,