——————–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

還有很多內容，就不寫了，請參考聯機叢書。