SQLServer 2008数据库-常用SQL语句：查看死锁、堵塞的SQL语句等

每秒死锁数量

  SELECT  *
  FROM    sys.dm_os_performance_counters
  WHERE   counter_name LIKE 'Number of Deadlocksc%';

查看当前被阻塞暂停的事务进程

  select * from sysprocesses where open_tran<>0 and status='suspended'

查询当前阻塞

  WITH    CTE_SID ( BSID, SID, sql_handle )
            AS ( SELECT   blocking_session_id ,
                          session_id ,
                          sql_handle
                 FROM     sys.dm_exec_requests
                 WHERE    blocking_session_id <> 0
                 UNION ALL
                 SELECT   A.blocking_session_id ,
                          A.session_id ,
                          A.sql_handle
                 FROM     sys.dm_exec_requests A
                          JOIN CTE_SID B ON A.SESSION_ID = B.BSID
               )
      SELECT  C.BSID ,
              C.SID ,
              S.login_name ,
              S.host_name ,
              S.status ,
              S.cpu_time ,
              S.memory_usage ,
              S.last_request_start_time ,
              S.last_request_end_time ,
              S.logical_reads ,
              S.row_count ,
              q.text
      FROM    CTE_SID C 
              JOIN sys.dm_exec_sessions S ON C.sid = s.session_id
              CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
      ORDER BY sid

查询锁活动进程

      select    
          request_session_id spid,   
          OBJECT_NAME(resource_associated_entity_id) tableName    
      from    
          sys.dm_tran_locks   
      where    
          resource_type='OBJECT' 

杀死死锁进程

      kill 354

显示死锁相关信息

      exec sp_who2 354

查找用时最久的10条数据

      SELECT TOP 10
         total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
         execution_count as 执行次数,
         (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
            (CASE WHEN statement_end_offset = -1
      THEN LEN(CONVERT(nvarchar(max), text)) * 2
      ELSE statement_end_offset
      END - statement_start_offset)/2)
      FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
      FROM sys.dm_exec_query_stats
      ORDER BY [avg_cpu_cost] DESC 

查看CPU内存语句

      SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'

显示出会话中有多少个worker在等待

      SELECT TOP 10
       [session_id],
       [request_id],
       [start_time] AS '开始时间',
       [status] AS '状态',
       [command] AS '命令',
       dest.[text] AS 'sql语句', 
       DB_NAME([database_id]) AS '数据库名',
       [blocking_session_id] AS '正在阻塞其他会话的会话ID',
       der.[wait_type] AS '等待资源类型',
       [wait_time] AS '等待时间',
       [wait_resource] AS '等待的资源',
       [dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
       [reads] AS '物理读次数',
       [writes] AS '写次数',
       [logical_reads] AS '逻辑读次数',
       [row_count] AS '返回结果行数'
       FROM sys.[dm_exec_requests] AS der 
       INNER JOIN [sys].[dm_os_wait_stats] AS dows 
       ON der.[wait_type]=[dows].[wait_type]
       CROSS APPLY 
       sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
       WHERE [session_id]>50  
       ORDER BY [cpu_time] DESC

在做SQL Server的管理、监控、效能调校时，我们可能会执行以下的SQL指令，去观察SQL Server里的状态

      SELECT * FROM sys.sysprocesses;

      EXEC sp_who2;

      SELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.blocking_session_id,
        req.total_elapsed_time
      FROM sys.dm_exec_requests req (NOLOCK)
      CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

查询涉及某表的所有触发器

      --XXX为表名
      select name,text,xtype
      from sysobjects o, syscomments s 
      where o.id = s.id 
      and text like '%XXX%'
      and o.xtype = 'TR'

查询涉及某表的所有存储过程

      --XXX为表名
      select name,text,xtype
      from sysobjects o, syscomments s 
      where o.id = s.id 
      and text like '%XXX%'
      and o.xtype = 'P'