August 31, 2014 at 10:50 am
Hi
i've this script that find a block on a table with the syntax.
how can update the script that will show me the lock and the syntax that causing the lock and other info in the same results?
THX
WITH [Blocking]
AS (SELECT w.[session_id]
,s.[original_login_name]
,s.[login_name]
,w.[wait_duration_ms]
,w.[wait_type]
,r.[status]
,r.[wait_resource]
,w.[resource_description]
,s.[program_name]
,w.[blocking_session_id]
,s.[host_name]
,r.[command]
,r.[percent_complete]
,r.[cpu_time]
,r.[total_elapsed_time]
,r.[reads]
,r.[writes]
,r.[logical_reads]
,r.[row_count]
,q.[text]
,q.[dbid]
,p.[query_plan]
,r.[plan_handle]
FROM [sys].[dm_os_waiting_tasks] w
INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
WHERE w.[session_id] > 50
AND w.[wait_type] NOT IN ('DBMIRROR_DBM_EVENT'
,'ASYNC_NETWORK_IO'))
SELECT b.[session_id] AS [WaitingSessionID]
,b.[blocking_session_id] AS [BlockingSessionID]
,b.[login_name] AS [WaitingUserSessionLogin]
,s1.[login_name] AS [BlockingUserSessionLogin]
,b.[original_login_name] AS [WaitingUserConnectionLogin]
,s1.[original_login_name] AS [BlockingSessionConnectionLogin]
,b.[wait_duration_ms] AS [WaitDuration]
,b.[wait_type] AS [WaitType]
,t.[request_mode] AS [WaitRequestMode]
,UPPER(b.[status]) AS [WaitingProcessStatus]
,UPPER(s1.[status]) AS [BlockingSessionStatus]
,b.[wait_resource] AS [WaitResource]
,t.[resource_type] AS [WaitResourceType]
,t.[resource_database_id] AS [WaitResourceDatabaseID]
,DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName]
,b.[resource_description] AS [WaitResourceDescription]
,b.[program_name] AS [WaitingSessionProgramName]
,s1.[program_name] AS [BlockingSessionProgramName]
,b.[host_name] AS [WaitingHost]
,s1.[host_name] AS [BlockingHost]
,b.[command] AS [WaitingCommandType]
,b.[text] AS [WaitingCommandText]
,b.[row_count] AS [WaitingCommandRowCount]
,b.[percent_complete] AS [WaitingCommandPercentComplete]
,b.[cpu_time] AS [WaitingCommandCPUTime]
,b.[total_elapsed_time] AS [WaitingCommandTotalElapsedTime]
,b.[reads] AS [WaitingCommandReads]
,b.[writes] AS [WaitingCommandWrites]
,b.[logical_reads] AS [WaitingCommandLogicalReads]
,b.[query_plan] AS [WaitingCommandQueryPlan]
,b.[plan_handle] AS [WaitingCommandPlanHandle]
FROM [Blocking] b
INNER JOIN [sys].[dm_exec_sessions] s1
ON b.[blocking_session_id] = s1.[session_id]
INNER JOIN [sys].[dm_tran_locks] t
ON t.[request_session_id] = b.[session_id]
WHERE t.[request_status] = 'WAIT'
GO
September 5, 2014 at 3:12 pm
any one can help or share his script to find locked and blocked queries?
THX
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply