SQLServer2012 tempdb 的作用，短时间（20分钟内）从40G增到100G，且增大未减少是什么原因？

要解决 SQL Server 2012 中 tempdb 短时间暴涨且不减少的问题，需先明确tempdb 的核心作用，再从 “增长诱因” 和 “空间不释放的原因” 两方面拆解分析，最后给出排查方向。

1. SQL Server 2012 tempdb 的核心作用

tempdb 是 SQL Server 的系统数据库，属于 “临时存储区”，生命周期与 SQL Server 服务一致（服务重启后数据清空，文件大小保留配置），主要用途包括 4 类：

作用类别	具体场景
1. 存储临时对象	- 用户创建的`#临时表`（会话级，会话结束删除）、`##全局临时表`（所有会话可见，最后一个引用结束删除） - 表变量（`DECLARE @t TABLE`），虽逻辑上是变量，但物理存储在 tempdb - 临时存储过程、函数生成的临时结果
2. 支撑查询执行	当查询需要大量内存且超出`max server memory`限制时，会将中间结果写入 tempdb： - 排序操作（`ORDER BY`/`GROUP BY`/`DISTINCT`） - 哈希连接（`HASH JOIN`） - 索引创建 / 重建（尤其是非聚集索引、在线索引）
3. 版本存储（Version Store）	用于实现乐观并发控制，保留数据的历史版本，供未提交事务访问： - 快照隔离级别（`SNAPSHOT ISOLATION`） - 读已提交快照隔离（`READ_COMMITTED_SNAPSHOT ON`） - 变更数据捕获（CDC）、触发器中间数据
4. 其他临时操作	- 在线索引重建（`WITH (ONLINE=ON)`）的中间结构存储 - 数据库镜像 / 日志传送的临时元数据 - `DBCC CHECKDB`等维护命令的临时结果

2. tempdb 20 分钟内从 40G 增至 100G 的核心原因

tempdb 短时间暴涨，本质是大量临时数据写入且未及时释放，常见诱因可分为 3 类：

2.1. 临时对象滥用（最常见）

用户或应用程序创建的临时表 / 表变量占用大量空间，且未及时清理：

大数据量临时表：例如从百万 / 千万行的业务表中查询全量数据，插入#临时表后未及时DROP（虽会话结束会自动删除，但若会话持续 20 分钟以上，空间会一直占用）；
循环创建临时对象：存储过程中循环生成临时表，且未在循环内释放，导致临时对象累积；
表变量的隐蔽占用：表变量默认不触发统计信息，若插入大量数据（如 100 万行以上），会占用 tempdb 空间且不易被察觉（需通过sys.dm_db_session_space_usage排查）。

2.2. 查询执行导致的 tempdb 溢出

复杂查询的中间结果超出内存限制，被迫写入 tempdb（即 “tempdb spill”），是短时间暴涨的重要原因：

排序溢出：查询包含ORDER BY/GROUP BY/DISTINCT，且排序数据量远超worktable的内存配额（由max server memory和查询成本决定），例如对 1 亿行数据按多字段排序；
哈希连接溢出：多表关联使用HASH JOIN（默认优化器选择），若哈希表（存储左表数据）超出内存，会将部分数据写入 tempdb 的 “溢出文件”；
大索引操作：创建 / 重建大表索引（尤其是非聚集索引、分区表索引），若未指定SORT_IN_TEMPDB = OFF（默认ON），排序过程会占用大量 tempdb 空间。

2.3. 版本存储（Version Store）累积

若数据库启用快照隔离或读已提交快照隔离（RCSI），长时间运行的事务会导致版本数据无法回收，进而撑大 tempdb：

长事务未提交：例如一个事务执行BEGIN TRANSACTION后，未COMMIT/ROLLBACK，持续 20 分钟以上，SQL Server 需保留该事务启动后所有数据的版本（用于 “快照读”），版本存储会持续增长；
CDC（变更数据捕获）配置不当：CDC 会定期捕获数据变更并存储到 tempdb 的版本存储，若 CDC 清理作业（cdc.cleanup_job）未正常运行，版本数据会累积；
触发器频繁触发：触发器执行过程中会生成临时版本数据，若业务表有高频写入（如每秒千级 INSERT/UPDATE），版本存储会快速增长。

3. tempdb 增大后不减少的关键原因

tempdb 空间 “只增不减” 并非异常，需区分 “SQL Server 内部空闲” 和 “操作系统文件大小” 的差异，核心原因有 3 点：

3.1. tempdb 空间的 “延迟释放” 机制

SQL Server 对 tempdb 的空间管理采用 “复用优先” 策略：

临时对象删除（如DROP #temp）或查询结束后，占用的空间不会立即 “还给操作系统”，而是标记为 “空闲空间”（Free Space），供后续临时对象 / 查询复用；
从操作系统视角看，tempdb 的数据文件大小仍为 100G，但 SQL Server 内部可能有大量空闲空间（需通过sys.dm_db_file_space_usage查看free_space_in_bytes）。

3.2. 自动收缩（Auto Shrink）默认关闭

SQL Server 2012 中，tempdb 的AUTO_SHRINK选项默认是OFF（不推荐开启）：

即使 tempdb 有大量空闲空间，也不会自动收缩数据文件大小；若手动执行DBCC SHRINKFILE，虽能减小文件，但会产生大量磁盘碎片，严重影响后续 tempdb 操作性能（不建议常规使用）。

3.3. 未释放的占用源仍在运行

若 tempdb 的占用源（如长事务、未结束的查询、未释放的临时对象）仍在持续，空间会被锁定无法释放：

例如：一个持续 20 分钟的查询仍在使用 tempdb 的排序缓冲区，或一个未提交的长事务仍需访问版本数据，SQL Server 无法回收这些空间。

4. 排查与验证步骤（关键操作）

若需定位具体原因，可通过以下 SQL 脚本查询 tempdb 的占用详情：

4.1. 查看 tempdb 各文件的空间使用（区分已用 / 空闲）

SELECT 
    DB_NAME(database_id) AS db_name,
    name AS tempdb_file_name,
    physical_name AS file_path,
    size/128.0 AS total_size_mb,  -- 总大小（MB）
    FILEPROPERTY(name, 'SpaceUsed')/128.0 AS used_size_mb,  -- 已用大小（MB）
    (size - FILEPROPERTY(name, 'SpaceUsed'))/128.0 AS free_size_mb  -- 空闲大小（MB）
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

 

若free_size_mb较大（如 50G 以上），说明空间已释放为 “空闲”，无需担心；
若used_size_mb接近total_size_mb，说明确实存在持续占用。

4.2. 定位占用 tempdb 最多的会话 / 查询

方式一：

--查找所有正在使用 tempdb 的会话，包含当前正在执行的sql以及占用的tempdb大小
SELECT 
    s.session_id AS 会话ID,
    s.status AS 会话状态,
    s.login_name AS 登录名,
    s.host_name AS 客户端主机,
    s.program_name AS 应用程序,
    t.user_objects_alloc_page_count * 8 AS 用户对象_KB,  -- 临时表等用户对象占用
    t.internal_objects_alloc_page_count * 8 AS 内部对象_KB,  -- 排序/哈希等内部对象占用
    -- 计算总占用 tempdb 大小（KB）
    (t.user_objects_alloc_page_count + t.internal_objects_alloc_page_count) * 8 AS 总占用_tempdb_KB,
    -- 转换为 MB 更直观（可选）
    (t.user_objects_alloc_page_count + t.internal_objects_alloc_page_count) * 8.0 / 1024 AS 总占用_tempdb_MB,
    -- 提取正在执行的SQL语句（仅活跃请求有值）
    SUBSTRING(
        qt.text, 
        (r.statement_start_offset / 2) + 1, 
        CASE 
            WHEN r.statement_end_offset = -1 THEN DATALENGTH(qt.text) 
            ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1 
        END
    ) AS 当前执行的SQL语句,
    qt.text AS 完整SQL批处理
FROM 
    tempdb.sys.dm_db_task_space_usage t
JOIN 
    sys.dm_exec_sessions s ON t.session_id = s.session_id
LEFT JOIN 
    sys.dm_exec_requests r ON t.session_id = r.session_id
OUTER APPLY 
    sys.dm_exec_sql_text(CASE WHEN r.sql_handle IS NOT NULL THEN r.sql_handle ELSE NULL END) qt
WHERE 
    t.user_objects_alloc_page_count > 0 
    OR t.internal_objects_alloc_page_count > 0
-- 按总占用 tempdb 大小降序排序（最大的排在前面）
ORDER BY 
    (t.user_objects_alloc_page_count + t.internal_objects_alloc_page_count) DESC;

方式二：

SELECT 
    s.session_id AS 会话ID,
    s.status AS 会话状态,
    s.login_name AS 登录名,
    s.host_name AS 客户端主机,
    s.program_name AS 应用程序,
    tsu.user_objects_alloc_page_count * 8 AS 用户对象_KB,
    tsu.internal_objects_alloc_page_count * 8 AS 内部对象_KB,
    (tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) * 8 AS 总占用_tempdb_KB,
    CAST(((tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) * 8.0) / 1024 AS DECIMAL(10, 2)) AS 总占用_tempdb_MB,
    SUBSTRING(qt.text, (er.statement_start_offset / 2) + 1,
        ((CASE er.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE er.statement_end_offset
         END - er.statement_start_offset) / 2) + 1) AS 当前执行的SQL语句,
    qt.text AS 完整SQL批处理
FROM 
    sys.dm_exec_sessions s
LEFT JOIN 
    sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id
LEFT JOIN 
    sys.dm_exec_requests er ON s.session_id = er.session_id
OUTER APPLY 
    sys.dm_exec_sql_text(er.sql_handle) qt
WHERE 
    (tsu.user_objects_alloc_page_count > 0 OR tsu.internal_objects_alloc_page_count > 0)
    OR er.session_id IS NOT NULL
GROUP BY 
    s.session_id, s.status, s.login_name, s.host_name, s.program_name,
    tsu.user_objects_alloc_page_count, tsu.internal_objects_alloc_page_count,
    er.statement_start_offset, er.statement_end_offset, qt.text, er.sql_handle
ORDER BY 
    总占用_tempdb_KB DESC;

4.3. 查看是否存在长事务

SELECT 
    transaction_id,
    database_id,
    DB_NAME(database_id) AS db_name,
    transaction_type,
    transaction_state,
    DATEDIFF(MINUTE, transaction_begin_time, GETDATE()) AS transaction_duration_min  -- 事务持续时间（分钟）
FROM sys.dm_tran_active_transactions
WHERE DATEDIFF(MINUTE, transaction_begin_time, GETDATE()) > 5;  -- 筛选持续5分钟以上的事务

若存在transaction_duration_min > 20的事务，需检查该事务是否正常（是否卡住、未提交）。

5. tempdb 暴涨处理

5.1 核心方法：收缩 `tempdb` 文件

通过 DBCC SHRINKFILE 命令可强制减少数据或日志文件的物理大小，但需注意：

仅适用于空闲空间较多的情况：若 tempdb 内部仍有大量活动对象（如未提交的事务、未释放的临时表），收缩可能失败或仅释放部分空间。
可能产生碎片：频繁收缩会导致文件碎片化，影响后续性能（建议仅在必要时使用）。

操作步骤：

收缩数据文件：

USE tempdb;
DBCC SHRINKFILE (N'tempdev', 1024);  -- 将主数据文件收缩至 1024 MB（根据需求调整）

收缩日志文件：

DBCC SHRINKFILE (N'templog', 256);  -- 将日志文件收缩至 256 MB

 

验证结果：

SELECT 
    name, 
    size/128.0 AS total_size_mb, 
    FILEPROPERTY(name, 'SpaceUsed')/128.0 AS used_size_mb
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

 

注意事项：

单用户模式执行：若收缩失败，可将 SQL Server 启动为单用户模式后重试。
重启服务重置大小：tempdb 在 SQL Server 重启后会基于 model 数据库重新创建，文件大小恢复为最后一次显式配置的值（如通过 ALTER DATABASE 或 DBCC SHRINKFILE 设置的大小）。

5.2 释放版本存储（Version Store）

版本存储用于快照隔离和读已提交快照隔离（RCSI），若因长事务导致其持续增长，需主动清理。

操作步骤：

提交或回滚长事务：

 

-- 查找持续 20 分钟以上的事务
SELECT 
    transaction_id, 
    DB_NAME(database_id) AS db_name, 
    transaction_type, 
    DATEDIFF(MINUTE, transaction_begin_time, GETDATE()) AS duration_min
FROM sys.dm_tran_active_transactions
WHERE DATEDIFF(MINUTE, transaction_begin_time, GETDATE()) > 20;

若存在异常事务，执行 COMMIT 或 ROLLBACK 释放版本数据。

禁用快照隔离（非必要时）：

ALTER DATABASE YourDatabase SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT OFF;

禁用后，版本存储将不再累积新数据，但需评估对业务的影响。

手动清理版本存储（慎用）：

-- 仅在确认无活动事务时执行
DBCC SHRINKDATABASE (tempdb, 0);  -- 尝试释放版本存储占用的空间

5.3 终止占用 `tempdb` 的会话

通过 KILL 命令终止异常会话，释放其占用的临时对象或版本数据。

操作步骤：

定位占用空间的会话：

SELECT 
    s.session_id, 
    s.login_name, 
    s.program_name, 
    t.text AS current_query, 
    us.user_objects_alloc_page_count * 8 AS user_obj_kb, 
    us.internal_objects_alloc_page_count * 8 AS internal_obj_kb
FROM sys.dm_db_session_space_usage us
JOIN sys.dm_exec_sessions s ON us.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE us.user_objects_alloc_page_count > 0 
   OR us.internal_objects_alloc_page_count > 0
ORDER BY (us.user_objects_alloc_page_count + us.internal_objects_alloc_page_count) DESC;

重点关注 user_obj_kb（临时表 / 表变量）和 internal_obj_kb（排序 / 哈希溢出）较高的会话。

终止会话：

KILL 52;  -- 替换为实际的 session_id

终止会话可能导致未提交的事务回滚，需提前确认业务影响。

5.4 重启 SQL Server 服务

这是最彻底的方法，会清空 tempdb 中的所有数据，但文件大小不会自动调整（需结合收缩操作）。

操作步骤：

停止服务：
- 在 Windows 服务管理器中找到 SQL Server (MSSQLSERVER)，右键选择停止。
- 或通过命令行：
  bash
  
  net stop mssqlserver
启动服务：
bash
net start mssqlserver

注意事项：

文件大小重置：重启后 tempdb 数据文件大小恢复为上次显式配置的值（如通过 ALTER DATABASE 设置的初始大小），日志文件恢复为默认的 model 数据库日志大小。
业务中断：需在维护窗口执行，确保无活动连接。

5.5 清理临时对象

手动删除未及时释放的临时表或表变量。

操作步骤：

查找全局临时表：

SELECT name, create_date 
FROM tempdb.sys.tables 
WHERE name LIKE '##%';  -- 全局临时表

删除临时表：

DROP TABLE ##GlobalTempTable;  -- 替换为实际表名

清理表变量缓存：
- 若应用频繁使用表变量，可通过 DBCC FREESYSTEMCACHE 清除缓存（生产环境慎用）：
  DBCC FREESYSTEMCACHE('Temporary Tables & Table Variables');

5.6 优化建议与风险提示

避免手动收缩的高频使用：
- tempdb 的自动增长机制是性能优化设计，手动收缩可能破坏这一机制。若空间持续增长，应优先优化查询或事务逻辑。

监控与预防：

使用动态管理视图（DMV）持续监控：

-- 查看 tempdb 空间分布
SELECT 
    SUM(user_object_reserved_page_count)*8 AS user_objects_kb, 
    SUM(version_store_reserved_page_count)*8 AS version_store_kb, 
    SUM(unallocated_extent_page_count)*8 AS free_space_kb
FROM sys.dm_db_file_space_usage;

长期解决方案：
- 调整文件配置：增大 tempdb 初始大小，减少自动增长频率。
- 禁用不必要的快照隔离：评估业务是否真正需要快照隔离，避免版本存储累积。

5.7 常见问题处理

收缩失败：
- 检查是否有活动事务或查询占用空间，终止相关会话后重试。
- 尝试在单用户模式下收缩。
空间释放后再次增长：
- 说明仍有未优化的查询或事务，需通过 sys.dm_db_session_space_usage 定位根本原因。

6. 总结

tempdb 暴涨：多因临时对象滥用、查询溢出（排序 / 哈希）、版本存储累积，需通过脚本定位具体会话 / 查询；
空间不减少：多数是 “空闲空间未还给操作系统”（正常机制），若确实持续占用，需排查未结束的事务或查询；
优化建议：避免长事务、减少大临时表使用、优化复杂查询（减少排序 / 哈希溢出）、禁用不必要的快照隔离，无需频繁收缩 tempdb（避免碎片）。

posted on 2025-08-28 11:04 xibuhaohao 阅读(41) 评论(0) 收藏举报

刷新页面返回顶部

1. SQL Server 2012 tempdb 的核心作用

2. tempdb 20 分钟内从 40G 增至 100G 的核心原因

2.1. 临时对象滥用（最常见）

2.2. 查询执行导致的 tempdb 溢出

2.3. 版本存储（Version Store）累积

3. tempdb 增大后不减少的关键原因

3.1. tempdb 空间的 “延迟释放” 机制

3.2. 自动收缩（Auto Shrink）默认关闭

3.3. 未释放的占用源仍在运行

4. 排查与验证步骤（关键操作）

4.1. 查看 tempdb 各文件的空间使用（区分已用 / 空闲）

4.2. 定位占用 tempdb 最多的会话 / 查询

4.3. 查看是否存在长事务

5. tempdb 暴涨处理

5.1 核心方法：收缩 tempdb 文件

操作步骤：

注意事项：

5.2 释放版本存储（Version Store）

操作步骤：

5.3 终止占用 tempdb 的会话

操作步骤：

5.4 重启 SQL Server 服务

操作步骤：

注意事项：

5.5 清理临时对象

操作步骤：

5.6 优化建议与风险提示

5.7 常见问题处理

6. 总结

公告

5.1 核心方法：收缩 `tempdb` 文件

5.3 终止占用 `tempdb` 的会话