-- Long running query, locking/blocking analysis

-- waiting tasks for a particular session 
select * from sys.dm_os_waiting_tasks where session_id=54

-- blocking session
select t1.resource_type
	,db_name(resource_database_id) as [database]
	,t1.resource_associated_entity_id as [blk object]
	,t1.request_mode
	,t1.request_session_id   -- spid	
	,t2.blocking_session_id  -- spid	
from 
	sys.dm_tran_locks as t1, 
	sys.dm_os_waiting_tasks as t2
where 
	t1.lock_owner_address = t2.resource_address
--	and t1.request_session_id = 61

-- object level analysis
select 
	request_session_id, 
	resource_type,
	resource_database_id, 
	(case resource_type
		when 'OBJECT' then object_name(resource_associated_entity_id)
		when 'DATABASE' then ' '
		else (
			select object_name(object_id) from sys.partitions 
			where hobt_id=resource_associated_entity_id)
	end) as object_name, 
	resource_description,
	request_mode,
	request_status
from sys.dm_tran_locks

-- Profiler trace demo on blocking report
sp_configure 'blocked process threshold', 3
reconfigure

-- index operational stats and index usage stats
-- analysis outside of the scope of this webcast
SELECT * FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);


-- SQL waits analysis
-- top 10 resources waited on
select top 10 *
from sys.dm_os_wait_stats
--where wait_type not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')
order by wait_time_ms desc


-- more detailed analysis
declare @totalWait bigint
declare @totalSignalWait bigint
declare @totalResourceWait bigint

select 
	@totalWait = sum([wait_time_ms]), 
	@totalSignalWait = sum([signal_wait_time_ms])
from 
	sys.dm_os_wait_stats 
where 
	[wait_type] not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')

select @totalResourceWait = @totalWait - @totalSignalWait

select top 5
	wait_type, 
	waiting_tasks_count,
	wait_time_ms-signal_wait_time_ms as resource_wait_ms,
	cast(100*(wait_time_ms - signal_wait_time_ms) / @totalResourceWait as numeric(20,1)) as resource_wait_percent,
	signal_wait_time_ms,
	cast(100*signal_wait_time_ms / @totalSignalWait as numeric(20,1)) as signal_wait_percent,
	wait_time_ms,
	cast(100*wait_time_ms / @totalwait as numeric(20,1)) as wait_time_percent
from 
	sys.dm_os_wait_stats
where 
	[wait_type] not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')
order by 4 desc