验证SQLServer死锁进程

-- =============================================  
-- Author:  Evan  
-- Create date: <Create Date,,>  
-- Description: 验证数据库死锁状况  
-- =============================================  
CREATE PROCEDURE sp_CheckLockState   
   
AS  
BEGIN  
 DECLARE @spid INT ,  
    @bl INT ,  
    @intTransactionCountOnEntry INT ,  
    @intRowcount INT ,  
    @intCountProperties INT ,  
    @intCounter INT  
  
CREATE TABLE #tmp_lock_who  
    (  
      id INT IDENTITY(1, 1) ,  
      spid SMALLINT ,  
      bl SMALLINT  
    )  
  
INSERT  INTO #tmp_lock_who  
        ( spid ,  
          bl  
        )  
        SELECT  0 ,  
                blocked  
        FROM    ( SELECT    *  
                  FROM      sys.sysprocesses  
                  WHERE     blocked > 0  
                ) a  
        WHERE   NOT EXISTS ( SELECT *  
                             FROM   ( SELECT    *  
                                      FROM      sys.sysprocesses  
                                      WHERE     blocked > 0  
                                    ) b  
                             WHERE  a.blocked = spid )  
        UNION  
        SELECT  spid ,  
                blocked  
        FROM    sys.sysprocesses  
        WHERE   blocked > 0  
  
  
-- 找到临时表的记录数  
SELECT  @intCountProperties = COUNT(*) ,  
        @intCounter = 1  
FROM    #tmp_lock_who  
IF @intCountProperties = 0   
    SELECT  '现在没有阻塞和死锁信息' AS message  
  
-- 循环开始  
WHILE @intCounter <= @intCountProperties   
    BEGIN  
-- 取第一条记录  
        SELECT  @spid = spid ,  
                @bl = bl  
        FROM    #tmp_lock_who  
        WHERE   Id = @intCounter   
        BEGIN  
            IF @spid = 0   
                BEGIN  
                    SELECT  '引起数据库死锁的是: ' + CAST(@bl AS VARCHAR(10))  
                            + '进程号,其执行的SQL语法如下'  
                END  
            ELSE   
                BEGIN  
                    SELECT  '进程号SPID:' + CAST(@spid AS VARCHAR(10))  
                            + '其当前进程执行的SQL语法如下:'  
                    DBCC INPUTBUFFER(@spid)  
                    SELECT  '进程号SPID：' + CAST(@spid AS VARCHAR(10)) + '被'  
                            + '进程号SPID：' + CAST(@bl AS VARCHAR(10))  
                            + '阻塞,其当前进程执行的SQL语法如下'  
                END  
            DBCC INPUTBUFFER (@bl)  
        END   
  
-- 循环指针下移  
        SET @intCounter = @intCounter + 1  
    END  
DROP TABLE #tmp_lock_who  
END  

 

--数据库I/O占用
SELECT
database_id AS dbid,
file_id AS fileid,
io_stall AS iostall,
io_pending_ms_ticks AS iopendingmstick,
scheduler_address AS scheduleraddress
from sys.dm_io_virtual_file_stats(NULL,null) as t1,
sys.dm_io_pending_io_requests AS t2
WHERE t1.file_handle=t2.io_handle



--访问数据库的服务进程数
SELECT hostname,COUNT(1) FROM sys.sysprocesses S
WHERE status<>'sleeping'
AND status<>'background'
--AND blocked<>0
--AND status='runnable  
--AND hostname='WIN-54SP3PM7475'--4
--AND hostname='WIN-8EB16MJ4HJE' --10
GROUP BY hostname

发现一篇干货，关于优化数据库，成为伪DBA的一篇博客   http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_sp_info_collection.html