• 排查数据库性能的常用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=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

    查看阻塞信息

    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
  • 相关阅读:
    怎样才能充分利用SQL索引
    MS SQL存储过程编写经验和优化措施
    ASP調用存講過程總結
    調用外部的DLL(DllImportAttribute)
    MS SQL中的行轉列
    SQL Server乐观锁定和悲观锁定实例
    如何使用 JScript 從 HTML 網頁自動化 Excel
    了解SQL Server锁争用:NOLOCK 和 ROWLOCK 的秘密
    C#語法學習結構(Struct)
    四项技术 助你提高SQL Server的性能
  • 原文地址:https://www.cnblogs.com/cxd4321/p/2828113.html
Copyright © 2020-2023  润新知