-- ============================================= -- Author: Evan -- 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占用SELECTdatabase_id AS dbid,file_id AS fileid,io_stall AS iostall,io_pending_ms_ticks AS iopendingmstick,scheduler_address AS scheduleraddressfrom sys.dm_io_virtual_file_stats(NULL,null) as t1,sys.dm_io_pending_io_requests AS t2WHERE t1.file_handle=t2.io_handle--访问数据库的服务进程数SELECT hostname,COUNT(1) FROM sys.sysprocesses SWHERE status<>'sleeping'AND status<>'background'--AND blocked<>0--AND status='runnable --AND hostname='WIN-54SP3PM7475'--4--AND hostname='WIN-8EB16MJ4HJE' --10GROUP BY hostname
发现一篇干货，关于优化数据库，成为伪DBA的一篇博客 http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_sp_info_collection.html