用户注册



邮箱:

密码:

用户登录


邮箱:

密码:
记住登录一个月忘记密码?

发表随想


还能输入:200字
云代码 - sql代码库

监控SQL Server正在执行的SQL语句和死锁情况

2017-05-19 作者:小章举报

[sql]代码库

SELECT [Individual Query] = SUBSTRING(qt.TEXT, er.statement_start_offset / 2, (
			CASE 
				WHEN er.statement_end_offset = - 1
					THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
				ELSE er.statement_end_offset
				END - er.statement_start_offset
			) / 2),
	[Parent Query] = qt.TEXT,
	[Spid] = session_Id,
	ecid,
	[Database] = DB_NAME(sp.dbid),
	[User] = nt_username,
	[Status] = er.STATUS,
	[Wait] = wait_type,
	Program = program_name,
	Hostname,
	nt_domain,
	start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE session_Id > 50 /* Ignore system spids.*/
	AND session_Id NOT IN (@@SPID)
 
--每秒死锁数量
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Number of Deadlocksc%';
 
--查询当前阻塞
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 

--检查表的更新排他锁
DECLARE @t_lock AS TABLE (
	[spid] [smallint] NULL,
	[dbid] [smallint] NOT NULL,
	[ObjId] [int] NOT NULL,
	[IndId] [smallint] NOT NULL,
	[Type] [nvarchar](4) NULL,
	[Resource] [nvarchar](32) NULL,
	[Mode] [nvarchar](8) NULL,
	[Status] [nvarchar](5) NULL
	)
 
INSERT INTO @t_lock
EXEC sp_lock
 
SELECT *,
	[Database] = DB_NAME([dbid]),
	[Object] = OBJECT_NAME([ObjId], [dbid])
FROM @t_lock
WHERE [spid] > 50 /* Ignore system spids.*/
	AND [spid] NOT IN (@@SPID)
	AND [Type] = 'TAB'
	AND [Mode] IN (
		'U',
		'IU',
		'SIU',
		'UIX',
		'BU',
		'RangeS_U',
		'RangeI_U',
		'X',
		'IX',
		'SIX',
		'UIX',
		'RangeI_X',
		'RangeX_S',
		'RangeX_U',
		'RangeX_X'
		)


分享到:
更多

网友评论    (发表评论)

共1 条评论 1/1页

发表评论:

评论须知:

  • 1、评论每次加2分,每天上限为30;
  • 2、请文明用语,共同创建干净的技术交流环境;
  • 3、若被发现提交非法信息,评论将会被删除,并且给予扣分处理,严重者给予封号处理;
  • 4、请勿发布广告信息或其他无关评论,否则将会删除评论并扣分,严重者给予封号处理。