mysql tempdb_tempdb相关总结

/*-- 0. 快速压缩tempdb为初始值USE tempdbDBCC SHRINKFILE(2,TRUNCATEONLY);*/-- 1. tempdb下面未回收的临时表 ,某些版本可能查不到数据use tempdb;select * from sys.objects o where o.type like %U%;-- Chapter 7 - Knowing Tempdb-- christian@coeo.com

/*

-- 0. 快速压缩tempdb为初始值

USE tempdb

DBCC SHRINKFILE(2,TRUNCATEONLY);

*/

-- 1. tempdb下面未回收的临时表 ,某些版本可能查不到数据

use tempdb;

select * from sys.objects o where o.type like '%U%';

-- Chapter 7 - Knowing Tempdb

-- christian@coeo.com

-- Show tempdb usage by type across all files

SELECT 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_pages

FROM sys.dm_db_file_space_usage ;

-- Find the top 5 sessions running tasks that use tempdb

SELECT TOP 5

*

FROM sys.dm_db_task_space_usage

WHERE session_id > 50

ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC ;

--return currently running T-SQL with Execution Plans

SELECT session_id,

text,

query_plan

FROM sys.dm_exec_requests

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

CROSS APPLY sys.dm_exec_query_plan(plan_handle) ;

--view historic tempdb usage by session

SELECT *

FROM sys.dm_db_session_space_usage

WHERE session_id > 50

ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC ;

-- Temp Tables Creation Rate

SELECT *

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Temp Tables Creation Rate' ;

本文原创发布php中文网，转载请注明出处，感谢您的尊重！