--https://minyurov.com/2016/07/24/mssql-tempdb-opt/


SELECT SUM(user_object_reserved_page_count)*8 as usr_obj_kb,

SUM(internal_object_reserved_page_count)*8 as internal_obj_kb,

SUM(version_store_reserved_page_count)*8 as version_store_kb,

SUM(unallocated_extent_page_count)*8 as freespace_kb,

SUM(mixed_extent_page_count)*8 as mixedextent_kb

FROM tempdb.sys.dm_db_file_space_usage

 

SELECT es.session_id

, ec.connection_id

, es.login_name

, es.host_name

, st.text

, su.user_objects_alloc_page_count

, su.user_objects_dealloc_page_count

, su.internal_objects_alloc_page_count

, su.internal_objects_dealloc_page_count

, ec.last_read

, ec.last_write

, es.program_name

FROM tempdb.sys.dm_db_session_space_usage su

INNER JOIN sys.dm_exec_sessions es ON su.session_id = es.session_id

LEFT OUTER JOIN sys.dm_exec_connections ec ON su.session_id = ec.most_recent_session_id

OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st

 

 

SELECT files.physical_name, files.name,

stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,

stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms

FROM sys.dm_io_virtual_file_stats(2, NULL) as stats

INNER JOIN master.sys.master_files AS files

ON stats.database_id = files.database_id

AND stats.file_id = files.file_id

WHERE files.type_desc = 'ROWS'