/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch

Lock Escalation
2012-01-11
Script 1 - Table creation
*****************************************************/
set nocount on
go

create table dbo.Example
(
	ID int not null,
	primary key(ID)
)
go

-- Session 1 code
begin tran
	insert into dbo.Example values(1)
	
	select resource_type, request_session_id, 
		request_mode, request_type 
	from sys.dm_tran_locks
	where resource_database_id = DB_ID() 

	-- Run Session 2 code
rollback
go

-- Session 2 code
begin tran
	insert into dbo.Example values(2)
	
	select resource_type, request_session_id, 
		request_mode, request_type 
	from sys.dm_tran_locks
	where resource_database_id = DB_ID() 
rollback
go

create table dbo.Data
(
	UserId int not null,
	Id int not null,
	Value int not null,
	
	Placeholder char(200) not null
		constraint Def_Data_Placeholder
		default 'a',
			
	constraint PK_Data
	primary key clustered(UserId, ID)
)
go

;with CTE(ID)
as
(
	select 1 
	
	union all
	
	select ID + 1
	from CTE
	where ID < 100000
)
insert into dbo.Data(UserId, Id, Value)
	select ID / 1000, ID, ID
	from CTE
option (MAXRECURSION 0)
go 

-- Let's disable lock escalation
alter table dbo.Data set (LOCK_ESCALATION = DISABLE)
go

-- clearing all cache. Can do on lock manager node level if needed
DBCC FREESYSTEMCACHE('ALL') 
go

set transaction isolation level repeatable read
begin tran
	select COUNT(*)
	from dbo.Data
	
	select COUNT(*) as [Lock Count] from sys.dm_tran_locks
	
	select top 10 Type AS [Memory Clerk Type], SUM(single_pages_kb) AS [Memory, KB] 
	from sys.dm_os_memory_clerks 
	group by Type
	order by SUM(single_pages_kb) desc 
commit
go

alter table dbo.Data set (LOCK_ESCALATION = AUTO)
go

-- clearing all cache. Can do on lock manager node level if needed
DBCC FREESYSTEMCACHE('ALL') 
go

set transaction isolation level repeatable read
begin tran
	select COUNT(*)
	from dbo.Data
	
	select COUNT(*) as [Lock Count] from sys.dm_tran_locks
	
	select top 10 Type AS [Memory Clerk Type], 
		SUM(single_pages_kb) AS [Memory, KB] 
	from sys.dm_os_memory_clerks 
	group by Type
	order by SUM(single_pages_kb) desc 
	
	select resource_type, request_mode 
		request_type, request_status,
		OBJECT_NAME(Resource_Associated_Entity_Id)
	from sys.dm_tran_locks 
commit
go

-- Where is the tipping point? YMMV
set transaction isolation level repeatable read
begin tran
	select COUNT(*) from dbo.Data where UserId < 6
	
	select COUNT(*) as [Lock Count] from sys.dm_tran_locks
commit
begin tran
	select COUNT(*) from dbo.Data where UserId < 7
	
	select COUNT(*) as [Lock Count] from sys.dm_tran_locks
commit
go	


-- How it affects me:
-- Session 1 code
set transaction isolation level read committed
begin tran
	update dbo.Data set Placeholder = 'b' where UserId = 0	
	
	select top 100 resource_type, request_mode, 
		request_type, request_status 
	from sys.dm_tran_locks 
	where resource_database_id = DB_ID()
	order by request_mode
	-- Run session 2 code 
rollback
go

-- Session 2 code
set transaction isolation level read committed
begin tran
	update dbo.Data set Placeholder = 'b' where UserId = 50	
rollback
go

-- Now let's cross tipping point
-- Session 1 code
set transaction isolation level read committed
begin tran
	update dbo.Data set Placeholder = 'b' where UserId <= 10	
	
	select top 100 resource_type, request_mode, 
		request_type, request_status 
	from sys.dm_tran_locks 
	where resource_database_id = DB_ID()
	order by request_mode
	-- Run session 2 code 
rollback
go

-- Session 2 code
set transaction isolation level read committed
begin tran
	update dbo.Data set Placeholder = 'b' where UserId = 50	
rollback
go

-- Session 3 code
/*
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
	,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 ObjectName
	,TL1.resource_description
	,TL1.request_session_id
	,TL1.request_mode
	,TL1.request_status
	,WT.wait_duration_ms as [Wait Duration (ms)]
	,(
		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)		
		from 
			sys.dm_exec_requests ER 
				cross apply sys.dm_exec_sql_text(ER.sql_handle) S
		where
			TL1.request_session_id = ER.session_id
	 ) as [Query]
from
	sys.dm_tran_locks as TL1 left outer join sys.dm_os_waiting_tasks WT on
		TL1.lock_owner_address = WT.resource_address and TL1.request_status = 'WAIT'
where
	TL1.request_session_id <> @@SPID
order by
	TL1.request_session_id
go

-- Clear wait statistics
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)
go

-- Shows TOP waits in the system
SELECT 
	wait_type, wait_time_ms, 
	convert(decimal(7,4), 100.0 * wait_time_ms / SUM(wait_time_ms) OVER()) AS [Percent]
from 
	 sys.dm_os_wait_stats
where 
	wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
	,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
	,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP',
	'CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
	,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN') 
	--and wait_type not like 'preemptive%'
order by 
	[Percent] Desc
go