/****************************************************************************/
/*                         SQL Server Internals v3                          */
/*                           Training Materials                             */
/*                                                                          */
/*                  Written by Dmitri V. Korotkevitch                       */
/*                      http://aboutsqlserver.com                           */
/*                        dk@aboutsqlserver.com                             */
/****************************************************************************/
/*                         Diagnostics Scripts                              */
/*                        Locking and Blocking                              */
/****************************************************************************/

use master
go

/* Set blocked process threashold to 5 seconds. Required for Blocked Process event */

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'blocked process threshold', 5
GO
RECONFIGURE
go



use SQLServerInternals
go

-- All active requests to Lock Manager
select * from sys.dm_tran_locks;
go


/*
Shows current locks. Even if it works across all database, ObjectName 
populates for current database only. Could be modified with dynamic SQL if needed

Be careful with Query text for LOCKS with GRANT status. This represents currently active
request for this specific session id which could be different than query which produced locks
It also could be NULL if there are no active requests for this session
*/

select
	TL1.resource_type as [Resource Type]
	,db_name(TL1.resource_database_id) as [DB Name]
	,case TL1.resource_type
		when 'OBJECT' then 
			object_name(TL1.resource_associated_entity_id
				,TL1.resource_database_id)
		when 'DATABASE' then
			'DB'
		else
			case
				when TL1.resource_database_id = db_id() 
				then
				(
					select object_name(object_id
							,TL1.resource_database_id)
					from sys.partitions
					where hobt_id =
						TL1.resource_associated_entity_id
				)
				else
					'(Run under DB context)'
			end
	end as [Object]
	,TL1.resource_description as [Resource]
	,TL1.request_session_id as [Session]
	,TL1.request_mode as [Mode]
	,TL1.request_status as [Status]
	,WT.wait_duration_ms as [Wait (ms)]
	,QueryInfo.sql
	,QueryInfo.query_plan
from
	sys.dm_tran_locks TL1 with (nolock) 
		left outer join sys.dm_os_waiting_tasks WT with (nolock) on
			TL1.lock_owner_address = WT.resource_address 
			and TL1.request_status = 'WAIT'
	outer apply
	(
		select
			substring(
				S.text, 
				(er.statement_start_offset / 2) + 1,
				((
					case 
						er.statement_end_offset
					when -1 
						then datalength(S.text)
						else er.statement_end_offset
					end - er.statement_start_offset) / 2) + 1
			) as sql,
			qp.query_plan
		from 
			sys.dm_exec_requests er with (nolock)
				cross apply sys.dm_exec_sql_text(er.sql_handle) S
				cross apply sys.dm_exec_query_plan(er.plan_handle) qp
		where
			TL1.request_session_id = er.session_id
	)  QueryInfo
where
	TL1.request_session_id <> @@spid
order by
	TL1.request_session_id
option (recompile);
go



/*
Shows blocked and blocking processes. Even if it works across all database, ObjectName 
populates for current database only. Could be modified with dynamic SQL if needed

Be careful with Query text for BLOCKING session. This represents currently active
request for this specific session id which could be different than query which produced locks
It also could be NULL if there are no active requests for this session
*/
-- Filtering out blocking and blocked sessions
select
	TL1.resource_type as [Resource Type]
	,db_name(TL1.resource_database_id) as [DB Name]
	,case TL1.resource_type
		when 'OBJECT' then 
			object_name(TL1.resource_associated_entity_id
				,TL1.resource_database_id)
		when 'DATABASE' then
			'DB'
		else
			case
				when TL1.resource_database_id = db_id() 
				then
				(
					select object_name(object_id
							,TL1.resource_database_id)
					from sys.partitions
					where hobt_id =
						TL1.resource_associated_entity_id
				)
				else
					'(Run under DB context)'
			end
	end as [Object]
	,TL1.resource_description as [Resource]
	,TL1.request_session_id as [Session]
	,TL1.request_mode as [Mode]
	,TL1.request_status as [Status]
	,WT.wait_duration_ms as [Wait (ms)]
	,QueryInfo.sql
	,QueryInfo.query_plan								
from
	sys.dm_tran_locks TL1 with (nolock) 
		join sys.dm_tran_locks TL2 with (nolock) on
			TL1.resource_associated_entity_id =
				TL2.resource_associated_entity_id
		left outer join sys.dm_os_waiting_tasks WT with (nolock) on
			TL1.lock_owner_address = WT.resource_address and 
			TL1.request_status = 'WAIT'
	outer apply
	(
		select
			substring(
				S.text, 
				(er.statement_start_offset / 2) + 1,
				((
					case 
						er.statement_end_offset
					when -1 
						then datalength(S.text)
						else er.statement_end_offset
					end - er.statement_start_offset) / 2) + 1
			) as sql,
			qp.query_plan
		from 
			sys.dm_exec_requests er with (nolock)
				cross apply sys.dm_exec_sql_text(er.sql_handle) S
				cross apply sys.dm_exec_query_plan(er.plan_handle) qp
		where
			TL1.request_session_id = er.session_id
	)  QueryInfo
where
	TL1.request_status <> TL2.request_status and
	(
		TL1.resource_description = TL2.resource_description OR
		(TL1.resource_description is null and 
			TL2.resource_description is null)
	)
option (recompile);
go

-- Get the statement by handle

declare	
	@H varbinary(max) = 
		/* Insert sql_handle from the top line of the execution stack */
	,@S int =  
		/* Insert stmtStart from the top line of the execution stack */
	,@E int = 
		/* Insert stmtEnd from the top line of the execution stack */

select	
	substring(
		qt.text, 
	 	(qs.statement_start_offset / 2) + 1,
		((case qs.statement_end_offset
			when -1 then datalength(qt.text)
			else qs.statement_end_offset
		end - qs.statement_start_offset) / 2) + 1
	) as sql
	,qp.query_plan
	,qs.creation_time
	,qs.last_execution_time
from	
	sys.dm_exec_query_stats qs with (nolock)
		cross apply sys.dm_exec_sql_text(qs.sql_handle) qt
		cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
where	
	qs.sql_handle = @H and 
	qs.statement_start_offset = @S
	and qs.statement_end_offset = @E 
option	(recompile);  
go
