Tempdb related Summary

/*--0. High-speed compression tempdb is the initial value of use TEMPDBDBCC shrinkfile (2,truncateonly); */--1. Tempdb the following are not reclaimed temporarily tables, some version numbers may not be able to find data use Tempdb;select * from sys.objects o where o.type like '%u% ';--Chapter 7-knowing tempdb- -[email protected]--Show tempdb usage by type across all Filesselect SUM (user_object_reserved_page_count) as User_ Object_pages, SUM (internal_object_reserved_page_count) as Internal_object_pages, sum (version_store_reserved_ Page_count) as version_store_pages, total_in_use_pages = SUM (user_object_reserved_page_count) + SUM (internal  _object_reserved_page_count) + sum (version_store_reserved_page_count), sum (Unallocated_extent_page_count) as        Total_free_pagesfrom sys.dm_db_file_space_usage;--Find the top 5 sessions running tasks that use Tempdbselect Top 5 *from sys.dm_db_task_space_usagewhere session_id > 50ORDER by User_objects_alloc_page_count + internal_obje Cts_alloc_page_count DESC;--return currently running T-SQL with execution Plansselect session_id, text, Query_planfrom sys.dm_exec_requests cross APPLY sys.dm_exec_sql_te        XT (sql_handle) Cross APPLY sys.dm_exec_query_plan (plan_handle); --view historic tempdb usage by sessionselect *from sys.dm_db_session_space_usagewhere session_id > 50ORDER by us Er_objects_alloc_page_count + Internal_objects_alloc_page_count DESC;--Temp Tables Creation rate SELECT *from sys  . dm_os_performance_counterswhere counter_name = ' Temp Tables Creation rate ';

Tempdb related Summary