当前位置：首页 > 数据库 > MSSQL教程 > SQLServer临时表的使用 > 正文

SQLServer临时表的使用

来源：这里教程网 时间：2026-03-02 12:35:56 作者：
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的文件的大小，并将增长模式改为按照固定大小。

编辑推荐：

相关推荐

最新软件资讯

一图读懂阿里云RDS架构与选型
一图读懂阿里云RDS架构与选型

热文推荐

天极热推