tempdb 相关总结
生活随笔
收集整理的這篇文章主要介紹了
tempdb 相关总结
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
/*
-- 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_requestsCROSS 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' ;
轉載于:https://www.cnblogs.com/yutingliuyl/p/7084585.html
總結
以上是生活随笔為你收集整理的tempdb 相关总结的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Analytics API and Cu
- 下一篇： jhipster项目迁移websocke