-- I/O bottlenecks
-- sys.dm_os_wait_stats and look for PAGEIOLATCH-es 

select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count
from sys.dm_os_wait_stats  
where wait_type like 'PAGEIOLATCH%'  and waiting_tasks_count > 0
order by wait_type

-- pending I/O requests
-- sys.dm_io_virtual_file_stats() and sys.dm_io_pending_io_requests
select database_id, file_id, io_stall,io_pending_ms_ticks, scheduler_address 
from sys.dm_io_virtual_file_stats(NULL, NULL) t1,
     sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle

-- find IO bound queries
-- sys.dm_exec_query_stats
select top 5 (total_logical_reads/execution_count) as avg_logical_reads,
                   (total_logical_writes/execution_count) as avg_logical_writes,
	     (total_physical_reads/execution_count) as avg_physical_reads,
	     Execution_count, statement_start_offset, p.query_plan, q.text
from sys.dm_exec_query_stats
	cross apply sys.dm_exec_query_plan(plan_handle) p
	cross apply sys.dm_exec_sql_text(plan_handle) as q
order by (total_logical_reads + total_logical_writes)/execution_count Desc

-- change sp_configure
-- reconfigure
-- restart queries
-- obesrve change in behavior

-- TempDB space usage
-- sys.dm_db_file_space_usage
-- mixed extents can be used by user and internal objects, not included here,
-- but present in the DMV
select 	
	sum(user_object_reserved_page_count)*8 as user_objects_kb,
	sum(internal_object_reserved_page_count)*8 as internal_objects_kb,
	sum(version_store_reserved_page_count)*8  as version_store_kb,
	sum(unallocated_extent_page_count)*8 as freespace_kb
from sys.dm_db_file_space_usage
where database_id = 2


-- find space used by user objects
DECLARE userobj_cursor CURSOR FOR 
	select name 
	from sys.objects 
	where object_id > 100 and type_desc = 'USER_TABLE'
open userobj_cursor
go

declare @name varchar(256)
fetch userobj_cursor into @name
while (@@FETCH_STATUS = 0) 
begin
	exec sp_spaceused @objname = @name
	fetch userobj_cursor into @name	
end

close userobj_cursor
deallocate userobj_cursor
go

-- Version store: find the longest transactions
-- sys.dm_tran_active_snapshot_database_transactions
select top 2 
	transaction_id, 
	transaction_sequence_num, 
	elapsed_time_seconds 
from 
	sys.dm_tran_active_snapshot_database_transactions
order by 
	elapsed_time_seconds desc

-- Internal objects
-- If you notice huge amount of tempdb space is allocated, you will need to know which session or tasks are consuming the space 
-- and then possibly take the corrective action.
-- sys.dm_db_session_space_usage
select 
	session_id, 
	internal_objects_alloc_page_count, 
	internal_objects_dealloc_page_count
from 
	sys.dm_db_session_space_usage
order by 
	internal_objects_alloc_page_count desc

-- you can use the following query to find the top user sessions that are 
-- allocating internal objects tempdb including currently active tasks 
-- sys.dm_db_task_space_usage and sys.dm_db_session_space_usage
select 
	t1.session_id,
	(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
	(t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated 
from 
	sys.dm_db_session_space_usage as t1, 
	(select session_id, 
		sum(internal_objects_alloc_page_count) as task_alloc,
		sum (internal_objects_dealloc_page_count) as task_dealloc 
	from 
		sys.dm_db_task_space_usage 
	group by 
		session_id) as t2
where 
	t1.session_id = t2.session_id 
	and t1.session_id >50
order by allocated desc

-- get more information about sessions
-- sys.dm_exec_sessions and sys.dm_exec_requests
sp_session_list

-- excessive DDL/allocations on TempDB
select session_id, wait_duration_ms, resource_description
from sys.dm_os_waiting_tasks
where wait_type like 'PAGE%LATCH_%' and resource_description like '2:%'
