--------------------TEMPDB数据库的空间问题--------------------------------------

 

今天不经意看见了sys.dm_db_session_space_usage这个DMV，就想想可以通过这个DMV来确定 tempdb 中的可用空间量，以及如何确定版本存储区、内部对象和用户对象使用的空间量。

SELECT
SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM
sys.dm_db_file_space_usage;

/*
free pages free space in MB
-------------------- ---------------------------------------
808 6.312500
*/

此SQL可以查询出tempdb 中所有文件的总可用页数和总可用空间量。

那么版本存储区使用的总页数和总空间量应该怎么查呢？

SELECT
SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM
sys.dm_db_file_space_usage;

/*version store pages used version store space in MB
------------------------ ---------------------------------------
0 0.000000

(1 行受影响)*/

内部对象使用的总页数和总空间量可以这么查：

SELECT
SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM
sys.dm_db_file_space_usage;

/*
internal object pages used internal object space in MB
-------------------------- ---------------------------------------
16 0.125000

(1 行受影响)
*/

用户对象使用的总页数和总空间量这么查:

SELECT
SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM
sys.dm_db_file_space_usage;

/*
user object pages used user object space in MB
---------------------- ---------------------------------------
40 0.312500

(1 行受影响)
*/

tempdb 中所有文件使用的磁盘空间总量这么查：

SELECT
SUM(size)*1.0/128 AS [size in MB]
FROM
tempdb.sys.database_files

/*
size in MB
---------------------------------------
8.750000

(1 行受影响)
*/

要监视查询使用的空间可以参考联机丛书，上面写得比较详细。

主要方法有两种。

第一种方法是检查批处理级数据，此方法比第二种方法使用的数据少。

第二种方法可用于标识占用磁盘空间的特定查询、临时表或表变量，但要获得答案必须收集更多数据。

如果批处理涉及到大量的查询。需要用JOB来进行轮询

代码来自联机丛书：

A. 获取每个会话中当前运行的所有任务中的内部对象占用的空间

下面的示例创建视图 all_task_usage。执行查询后，视图将返回 tempdb 中当前运行的所有任务中的内部对象使用的总空间量。

CREATE VIEW all_task_usage
AS
SELECT session_id,
SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;
GO

B. 获取当前会话中正在运行的任务和已完成任务的内部对象占用的空间

下面的示例创建视图 all_session_usage。执行查询后，视图将返回 tempdb 中正在运行的任务和已完成任务中的所有内部对象使用的空间。

CREATE VIEW all_session_usage
AS
SELECT R1.session_id,
R1.internal_objects_alloc_page_count
+ R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
R1.internal_objects_dealloc_page_count
+ R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;
GO

还有很多内容，就不写了，请参考联机丛书。