tempdb 相关总结
2024-10-21 02:56:41
/*
-- 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' ;
最新文章
- MySQL 体系结构
- JS-获取URL请求参数
- RDIFramework.NETV2.9版本 Web新增至14套皮肤风格+三套界面组合(共42套皮肤组合)
- 程序的删除kill、killall
- windbg----as、$u0(固定别名、自定义别名)
- linux timezone
- Net4.0---AspNet中URL重写的改进(转载)
- angular入门系列教程目录
- java下tcp的socket连接案例
- PHP - 直接输出对象的版本问题
- 后台验证url是不是有效的链接
- WC2015 k小割(k短路+暴力+搜索)
- Java面试题全集(下)转载
- SNFAutoupdater通用自动升级组件V2.0
- python 数据工程 and 开发工具Sublime
- 普通for循环和增强for循环的区别
- Repository与Factory关系
- LIMIT Query Optimization
- jz2440存储管理实验【学习笔记】
- 动态规划(DP),最大矩阵和