SQLServer臨時表的使用：
什麼情況下會使用臨時表：
1、使用者物件， 比如臨時表，表變數等（#，##，@開頭的一些變數）
2、遊標
3、內部中的一些列印和排序操作
4、用於快照隔離的行版本控制機制
5、線上的索引重建操作
6、啟用MARS (Multiple Active Resultsets) 機制或者操作
7、觸發器
以上的一些機制或者操作，會使用到系統的臨時表空間。
1. 查詢臨時資料庫中還有多少資源空餘：
SELECT SUM(unallocated_extent_page_count) AS [Temp Free Pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [Temp Free space in MB]
FROM sys.dm_db_file_space_usage;
2. 查詢TempDB中有多少資源用版本控制的：
SELECT SUM(version_store_reserved_page_count) AS [TempDB version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [TempDB version store space in MB]
FROM sys.dm_db_file_space_usage;
3. 查詢TempDB中有多少資源是被內部物件使用的：
SELECT SUM(internal_object_reserved_page_count) AS [TempDB internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [TempDB internal object space in MB]
FROM sys.dm_db_file_space_usage;
4. 查詢TempDB中有多少資源是被使用者級別的物件使用的：
SELECT SUM(user_object_reserved_page_count) AS [TempDB user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [TempDB user object space in MB]
FROM sys.dm_db_file_space_usage;
在上面的語句中，我們將查詢出來的page除以128來獲取值，那是因為SQL server 每個page的大小是8K，那麼除以128就可以得到單位是M的資料值。
我們可以結合以下幾張系統管理表，獲取當前session使用TempDB情況：
dm_db_file_space_usage – 返回tempdb中檔案的空間使用情況
dm_db_session_space_usage – 返回每個會話分配和釋放分配的頁數
dm_db_task_space_usage – 返回任務頁面分配和釋放活動
透過該語句我們可以看到tempdb的整體的資源分配情況：
SELECT ssu.session_id,
ssu.internal_objects_alloc_page_count,
ssu.user_objects_alloc_page_count,
ssu.internal_objects_dealloc_page_count ,
ssu.user_objects_dealloc_page_count,
es.*
FROM sys.dm_db_session_space_usage ssu ,sys.dm_exec_sessions as es
WHERE ssu.session_id = es.session_id
AND (ssu.internal_objects_alloc_page_count>0
OR ssu.user_objects_alloc_page_count>0
OR ssu.internal_objects_dealloc_page_count>0
OR ssu.user_objects_dealloc_page_count>0)
我們可以透過以下的語句，得到當前正在使用Tempdb的session的SQL語句：
SELECT ssu.session_id,
st.text
FROM sys.dm_db_session_space_usage as ssu,
sys.dm_exec_requests as er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
WHERE ssu.session_id = er.session_id
AND ssu.session_id > 0
AND (ssu.internal_objects_alloc_page_count > 0
OR ssu.user_objects_alloc_page_count > 0
OR ssu.internal_objects_dealloc_page_count > 0
OR ssu.user_objects_dealloc_page_count > 0)
我們可以透過下面的語句，來獲取當前有哪些session正在使用tempdb，以及一些其他的資訊：
SELECT
sys.dm_exec_sessions.session_id AS [SESSION ID]
,DB_NAME(database_id) AS [DATABASE Name]
,HOST_NAME AS [System Name]
,program_name AS [Program Name]
,login_name AS [USER Name]
,status
,cpu_time AS [CPU TIME (in milisec)]
,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
,total_elapsed_time AS [Elapsed TIME (in milisec)]
,(memory_usage * 8) AS [Memory USAGE (in KB)]
,(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
,(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
,CASE is_user_process
WHEN 1 THEN 'user session'
WHEN 0 THEN 'system session'
END AS [SESSION Type], row_count AS [ROW COUNT]
FROM
sys.dm_db_session_space_usage
INNER join
sys.dm_exec_sessions
ON sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id
ORDER BY status ASC
資源使用的情況：
SELECT
st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,
dmv_tsu.request_id,
dmv_tsu.exec_context_id,
(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er
ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)
INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) >= 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) DESC
對於tempdb使用率的高情況，能給出的建議如下：
1、儘量避免使用觸發器，使用觸發器時相關的操作儘可能的小。
2、如果記憶體資源充足，可以使用CTE取代表變數。
3、表和索引的排序儘量設計的合理，避免大量的臨時排序。
4、適當放大Tempdb的檔案的大小，並將增長模式改為按照固定大小。