05.4.1.4 tempdb 与系统数据库
所属:全栈开发面试题库 / 05 数据库与存储 / 05.4 SQL Server / 05.4.1 架构与存储引擎
覆盖范围
- tempdb 的用途:临时对象、版本存储、排序/哈希溢出、游标与工作表
- tempdb 多文件配置、自动增长与 PFS/GAM/SGAM 闩锁争用
- 临时表、表变量的作用域与统计信息差异
- tempdb 监控、容量规划与最佳实践
- model、msdb、master、resource 等系统数据库职责
- 与 MySQL 临时表、PG 临时表空间对照
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.4.1.4-001 | tempdb 的用途 | 进阶 | ★★★★☆ |
| 05.4.1.4-003 | tempdb 多文件与增长配置 | 进阶 | ★★★★☆ |
| 05.4.1.4-004 | PFS/GAM 页闩锁争用 | 高级 | ★★★★☆ |
| 05.4.1.4-005 | 临时表 vs 表变量 | 进阶 | ★★★★★ |
| 05.4.1.4-006 | 版本存储与 tempdb 容量 | 高级 | ★★★★☆ |
| 05.4.1.4-015 | tempdb 监控 DMV | 进阶 | ★★★☆☆ |
系统库分工
实例级
master 登录、实例配置、库列表;损坏影响实例启动
model 新建数据库的模板
msdb 代理作业、备份历史、警报、SSIS 目录
resource 只读系统资源库(mssqlsystemresource)
tempdb 实例内所有会话共享的临时工作区,重启重建
distribution 复制分发库(启用复制时);SSISDB(SSIS 目录)
05.4.1.4-001 tempdb 用来存什么?
频率 ★★★★☆ | 难度 进阶
一句话结论
tempdb 是实例共享的临时工作区,存放临时表/表变量、行版本、排序与哈希溢出表、游标工作区等;所有数据库的会话共用。
概念解释
- 临时对象:
#temp、##global、表变量。 - 行版本:RCSI / 快照隔离产生的旧版本(见 05.4.1.3-007)。
- 溢出工作表:排序、哈希连接、聚合内存不足时落盘到 tempdb。
- 其他:游标、暂存中间结果、某些索引/版本操作的内部工作表。
原理流程
- tempdb 被所有用户数据库共享;其容量与争用会影响整个实例。
- 重启实例后 tempdb 按配置重建,不保留历史数据。
代码 / 命令
-- 查看 tempdb 文件与空间使用
SELECT name, physical_name, size * 8 / 1024 AS size_mb, growth, is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
-- tempdb 各对象占用(用户对象 / 内部对象 / 版本存储)
SELECT user_object_reserved_page_count * 8 / 1024 AS user_obj_mb,
internal_object_reserved_page_count * 8 / 1024 AS internal_obj_mb,
version_store_reserved_page_count * 8 / 1024 AS version_store_mb
FROM sys.dm_db_file_space_usage;
常见误区
- 误区:tempdb 只存用户建的临时表。正解:版本存储、排序/哈希溢出等内部对象占比往往更大。
- 误区:不同业务库互相隔离,tempdb 也隔离。正解:tempdb 实例级共享,一个大查询的 spill 会挤占他人空间。
追问
- 问:tempdb 数据文件和日志文件都要规划吗?答:是;数据文件争用与版本/spill 占用,日志文件记录 tempdb 内事务。
- 问:tempdb 可以放到网络存储吗?答:不建议;低延迟本地盘是基本要求。
05.4.1.4-002 为什么 tempdb 不能备份,重启后数据还在吗?
频率 ★★★☆☆ | 难度 基础
一句话结论
tempdb 是可重建的工作库,不支持备份/还原;实例重启时按 model 模板与配置重新创建,其中所有临时对象与版本清空。
概念解释
- tempdb 内容都是易失性工作数据,无需持久保留。
- 重启后大小按配置的初始大小重建,自动增长配置保留。
原理流程
- 服务启动 → 重建 tempdb → 会话连接后在其中创建临时对象。
代码 / 命令
-- 对 tempdb 执行备份会报错(不支持备份)
BACKUP DATABASE tempdb TO DISK = N'x.bak'; -- 实际执行将被引擎拒绝
常见误区
- 误区:临时表数据在实例重启后还在。正解:tempdb 重建,全部丢失。
- 误区:tempdb 日志需要周期性备份。正解:简单恢复模式下自动截断,不纳入用户库日志备份链。
追问
- 问:tempdb 恢复模式是什么?答:简单恢复模式,日志自动回收,不可切换为完整模式。
- 问:为什么临时对象不建在用户库里?答:隔离工作负载、避免污染用户库空间与备份链。
05.4.1.4-003 tempdb 数据文件数量和增长应该怎么配?
频率 ★★★★☆ | 难度 进阶
一句话结论
tempdb 应按 CPU 核心数配置多个等大数据文件(通常不超过物理核心数),初始大小按需预估,固定 MB 增长,多文件同大小同步增长以分散分配争用。
概念解释
- 多文件:每个文件有独立的 GAM/PFS/SGAM 系统页,分配请求可分散,缓解全局分配页争用。
- 等大同步增长:文件大小一致、按相同比例增长,避免某一文件成为热点。
- 初始大小:按峰值 spill 与版本存储预估,避免上线后频繁 autogrow。
原理流程
单文件:所有新页分配都争用同一套 GAM/PFS 页
多文件:分配轮询/比例分散到各文件,系统页争用被摊薄
代码 / 命令
-- 查看 tempdb 文件数与大小
SELECT name, size * 8 / 1024 AS size_mb, growth
FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');
-- 添加等大 tempdb 数据文件(示例,路径按环境规划)
ALTER DATABASE tempdb ADD FILE (
NAME = N'tempdev2', FILENAME = N'/data/tempdev2.ndf',
SIZE = 4GB, FILEGROWTH = 256MB);
常见误区
- 误区:文件越多越好。正解:文件过多增加维护开销,经验上按逻辑/物理核心数取合理上限,逐一压测。
- 误区:多文件大小不一致也能分散 IO。正解:大小不均会导致比例填充失衡,热点重新集中。
追问
- 问:tempdb 文件数与 NUMA 节点有关系吗?答:规划时可结合 NUMA 拓扑,目标是让分配与 IO 分布均衡。
- 问:调整 tempdb 文件后需要重启吗?答:新增/调整文件即时生效;初始大小收缩类操作可能需重启才完全生效(按版本核对,待确认)。
05.4.1.4-004 什么是 PFS / GAM / SGAM 页闩锁争用?怎么识别?
频率 ★★★★☆ | 难度 高级
一句话结论
高并发分配新页时,多个线程争抢同一 GAM/PFS/SGAM 页的闩锁,表现为 PAGELATCH_EX/PAGELATCH_SH 等待集中在分配系统页;多 tempdb 文件是主要缓解手段。
概念解释
- 闩锁(Latch):保护内存页结构的轻量同步,与事务锁不同。
- 分配页争用:所有文件前部的系统页负责空闲记录,并发分配时成为热点。
- 等待类型:
PAGELATCH_EX(写闩锁)常见于分配记录更新。
原理流程
- 并发会话同时申请新页 → 查 GAM/PFS 找空闲。
- 同一系统页的闩锁被排队持有。
- 等待累积,CPU 不高但分配延迟上升。
代码 / 命令
-- 查看 PAGELATCH 等待排行
SELECT wait_type, waiting_tasks_count, wait_time_ms,
average_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH%'
ORDER BY wait_time_ms DESC;
-- 定位等待的页类型(结合 DBCC PAGE 判断是否 GAM/PFS/SGAM)
SELECT session_id, resource_description, wait_type, wait_duration_ms
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%';
常见误区
- 误区:
PAGELATCH等待就是磁盘 IO 慢。正解:PAGELATCH是内存闩锁等待;IO 等待看PAGEIOLATCH。 - 误区:加 tempdb 日志文件能缓解数据页争用。正解:争用在数据文件系统页,需增加数据文件。
追问
- 问:怎么确认等待页是 GAM/PFS?答:用
resource_description的页号结合 DBCC PAGE 查看页类型。 - 问:除多文件外还有哪些缓解手段?答:减少临时对象与大 spill、优化查询避免大量排序落盘、按版本评估相关跟踪标记。
05.4.1.4-005 临时表和表变量有什么区别?怎么选?
频率 ★★★★★ | 难度 进阶
一句话结论
临时表(#temp)有完整统计信息与可被优化器复用的执行计划,适合数据量较大、需多次操作的场景;表变量统计信息有限、编译期定型,适合小数据集与简单流程。
概念解释
| 维度 | 临时表 #temp |
表变量 @tv |
|---|---|---|
| 统计信息 | 有,可被优化器估算 | 基本无行数统计(按固定估算) |
| 作用域 | 创建会话/过程内;## 全局 |
当前批处理/过程 |
| 索引/约束 | 可建索引、主键 | 只能声明时建主键/唯一约束 |
| 事务行为 | 参与事务回滚 | 部分操作不随事务回滚 |
| 重编译 | 可能引发语句重编译 | 计划更稳定但估算易偏差 |
原理流程
- 数据量小、单次填充即读取:表变量开销低。
- 数据量大、需 JOIN/聚合/多次过滤:临时表的统计信息让计划更准确。
代码 / 命令
-- 临时表:可建索引、有统计信息
CREATE TABLE #OrderAgg (CustomerId INT, TotalAmount MONEY);
INSERT INTO #OrderAgg
SELECT CustomerId, SUM(Amount) FROM dbo.Orders GROUP BY CustomerId;
CREATE INDEX IX_Temp_Customer ON #OrderAgg (CustomerId);
-- 表变量:声明时约束
DECLARE @OrderAgg TABLE (CustomerId INT PRIMARY KEY, TotalAmount MONEY);
INSERT INTO @OrderAgg SELECT CustomerId, SUM(Amount) FROM dbo.Orders GROUP BY CustomerId;
常见误区
- 误区:表变量一定比临时表快。正解:大数据量下表变量行数估算偏差可能导致差计划;不能一概而论。
- 误区:表变量上可以随意建索引。正解:只能在声明时定义主键/唯一约束,事后不能
CREATE INDEX。
追问
- 问:表变量参与事务回滚吗?答:其数据修改在事务内,但部分版本行为有差异;涉及回滚语义需按目标版本核对(待确认)。
- 问:局部临时表和全局临时表区别?答:
#仅当前会话可见;##所有会话可见,连接结束即清理。
05.4.1.4-006 行版本存储如何影响 tempdb 容量规划?
频率 ★★★★☆ | 难度 高级
一句话结论
开启 RCSI/快照隔离后,所有更新的旧版本都进 tempdb;长事务与批量更新会让版本存储持续膨胀,tempdb 容量必须按版本峰值预留。
概念解释
- 版本存储增长速度 ≈ 更新产生旧版本的速度;清理速度取决于最早活动事务。
- 批量更新/归档会瞬间产生大量旧版本,tempdb 峰值陡增。
原理流程
批量 UPDATE 百万行 → 百万旧版本写入 tempdb
长事务未结束 → 旧版本不能清理 → 版本存储持续增长
代码 / 命令
-- 版本存储占用与最老活动事务
SELECT version_store_reserved_page_count * 8 / 1024 AS version_mb,
user_object_reserved_page_count * 8 / 1024 AS user_obj_mb,
internal_object_reserved_page_count * 8 / 1024 AS internal_mb
FROM sys.dm_db_file_space_usage;
-- 查找阻止版本清理的长事务
SELECT s.session_id, t.transaction_begin_time, t.name,
DATEDIFF(SECOND, t.transaction_begin_time, GETDATE()) AS trx_sec
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions st ON st.transaction_id = t.transaction_id
JOIN sys.dm_exec_sessions s ON s.session_id = st.session_id
ORDER BY trx_sec DESC;
常见误区
- 误区:开了 RCSI 只影响用户库,与 tempdb 无关。正解:版本副本落在 tempdb,是 tempdb 容量规划的核心项。
- 误区:版本提交后立即释放空间。正解:必须等所有更早快照事务结束。
追问
- 问:批量更新如何降低版本峰值?答:分批小事务提交,让旧版本可被逐步清理。
- 问:tempdb 空间不足会怎样?答:相关写入/版本分配失败,实例报错;需提前扩容并监控增长。
05.4.1.4-007 tempdb 配置有哪些最佳实践?
频率 ★★★☆☆ | 难度 进阶
一句话结论
多等大数据文件、固定增长、独立低延迟磁盘、按峰值预留空间、关闭不必要的临时对象滥用,并纳入监控。
概念解释
- 独立磁盘:避免与用户数据/日志争 IO。
- 等大同增长:
tempdb文件大小一致,自动增长增量一致。 - 即时文件初始化:减少数据文件扩展等待(日志不享受)。
原理流程
- 规划步骤:评估峰值 spill + 版本存储 → 定初始大小与文件数 → 固定 MB 增长 → 监控空间与等待。
代码 / 命令
-- 查看 tempdb 配置基线
SELECT name, size * 8 / 1024 AS size_mb, growth, is_percent_growth
FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');
常见误区
- 误区:tempdb 用默认配置上线即可。正解:默认单小文件在高并发下极易成为争用与容量瓶颈。
- 误区:把 tempdb 放最快的盘就不用监控。正解:spill 与版本增长需持续看趋势。
追问
- 问:tempdb 放在 SSD 上有明显收益吗?答:顺序/随机 IO 都更稳,溢出与版本写入延迟下降;仍需容量规划。
- 问:多个 tempdb 文件分布在不同磁盘吗?答:有条件可分盘分散 IO;同盘多文件主要缓解系统页争用。
05.4.1.4-008 model 数据库起什么作用?
频率 ★★☆☆☆ | 难度 基础
一句话结论
model 是新建数据库的模板:新库复制 model 的文件大小、选项、用户对象与数据库级设置。
概念解释
- 改 model 的默认选项(如恢复模式、排序规则)会影响后续新建库。
- model 本身必须可访问,实例才能创建新库。
原理流程
CREATE DATABASE → 复制 model 结构与设置 → 按新库名/文件路径实例化
代码 / 命令
-- 查看 model 的恢复模式与选项
SELECT name, recovery_model_desc, collation_name
FROM sys.databases WHERE name = N'model';
常见误区
- 误区:改 model 会把已有用户库一并改掉。正解:只影响新建库;旧库需单独修改。
- 误区:model 里可以放业务表。正解:模板库应保持精简,避免每个新库都被带入无用对象。
追问
- 问:想让所有新库默认 RCSI 怎么配?答:在 model 上开启对应选项,新库继承。
- 问:model 损坏会怎样?答:可能无法创建新库,需按系统库恢复流程处理。
05.4.1.4-009 msdb 存什么?
频率 ★★☆☆☆ | 难度 基础
一句话结论
msdb 存代理作业、调度、备份历史、警报、操作员、SSIS 目录与策略等实例级运维信息。
概念解释
- SQL Server Agent:作业步骤、调度、历史记录。
- 备份历史:库/文件备份记录与介质信息。
- SSISDB 是独立目录库,存放 SSIS 项目与执行记录;msdb 承担其余运维元数据。
原理流程
- 作业调度与备份完成后,记录写入 msdb;运维报表多从 msdb 取数。
代码 / 命令
-- 查看代理作业(msdb 上下文)
SELECT name, enabled, description
FROM msdb.dbo.sysjobs ORDER BY name;
常见误区
- 误区:备份用户库不需要管 msdb。正解:备份历史与策略记录在 msdb,恢复时可能需要其信息。
- 误区:msdb 可以随意收缩。正解:作业历史膨胀可清理历史记录,文件收缩需谨慎。
追问
- 问:作业历史过长怎么清理?答:用代理清理历史记录的作业/存储过程按周期删除。
- 问:msdb 应该备份吗?答:生产环境应备份,以保留作业与备份历史。
05.4.1.4-010 master 数据库为什么关键?
频率 ★★★☆☆ | 难度 进阶
一句话结论
master 记录实例级元数据:登录账号、数据库清单、端点、实例配置与系统级信息;master 损坏可能导致实例无法启动。
概念解释
- 登录与权限:实例级登录名存于 master。
- 库清单:实例上有哪些数据库及其文件位置。
- 配置:服务器级配置、链接服务器、端点等。
原理流程
- 实例启动先恢复 master,才能定位并打开其他数据库。
代码 / 命令
-- 实例上的数据库清单与文件位置(master 上下文)
SELECT name, physical_name, state_desc
FROM sys.master_files f JOIN sys.databases d ON d.database_id = f.database_id;
常见误区
- 误区:用户业务数据存在 master 里。正解:master 只存元数据,业务对象应建在用户库。
- 误区:master 像普通用户库一样随便改库结构。正解:实例级配置变更需走规范流程并备份 master。
追问
- 问:master 损坏如何恢复?答:按系统库恢复流程,用最近 master 备份重建后再恢复用户库;具体步骤需按官方文档操作。
- 问:新增登录名写在哪个库?答:实例级登录存 master,用户映射在各用户库。
05.4.1.4-011 resource 数据库是什么?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
resource(mssqlsystemresource)是只读的内部资源库,存放系统过程与系统对象定义;对用户透明,升级版本时由安装程序替换。
概念解释
- 系统视图/存储过程的实际定义位于 resource 库,在用户库中以系统对象形式呈现。
- 只读、隐藏,不建议直接操作。
原理流程
- 版本升级替换 resource 库文件,完成系统对象更新。
代码 / 命令
-- resource 库在 sys.databases 中通常标记为只读/隐藏
SELECT name, state_desc, is_read_only
FROM sys.databases WHERE name = N'mssqlsystemresource';
常见误区
- 误区:可以把自定义系统函数装进 resource。正解:它是只读内部库,自定义对象应放用户库或专门工具库。
- 误区:resource 备份需要纳入用户备份链。正解:随版本安装维护,不作为常规业务备份对象。
追问
- 问:resource 与 master 区别?答:master 存元数据与配置;resource 存系统对象源码定义。
- 问:为什么它对用户不可见?答:避免误改内部系统对象。
05.4.1.4-012 distribution、SSISDB 等其他系统库是什么?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
distribution 是复制分发库,存复制元数据与队列;SSISDB 存 SSIS 项目、包与执行记录;按需创建,不是实例默认必备。
概念解释
- distribution:快照/日志读取器、分发代理的元数据与待分发命令。
- SSISDB:Integration Services 目录,部署包、环境与执行日志。
原理流程
- 启用对应功能时才创建;不使用复制/SSIS 目录则不存在。
代码 / 命令
-- 查看当前实例数据库清单,判断是否存在 distribution / SSISDB
SELECT name, state_desc, create_date FROM sys.databases ORDER BY name;
常见误区
- 误区:每个实例都有 distribution。正解:只有配置复制分发服务器时才创建。
- 误区:SSIS 包记录写在 msdb。正解:项目部署模型用 SSISDB;旧包部署模型才用 msdb。
追问
- 问:复制延迟与哪个库相关?答:distribution 中待分发命令队列可反映积压。
- 问:迁移实例时这些库要迁移吗?答:复制拓扑与 SSIS 项目需按对应方案迁移配置与包。
05.4.1.4-013 临时表的作用域和清理时机是什么?
频率 ★★★☆☆ | 难度 基础
一句话结论
局部临时表 #temp 在创建它的会话/存储过程结束时自动清理;全局 ##temp 在最后一个引用会话结束时清理;表变量随批处理结束释放。
概念解释
- 局部临时表:会话隔离,其他会话不可见。
- 全局临时表:跨会话可见,命名需注意冲突。
- 存储过程内临时表:过程退出即销毁(嵌套调用另有规则)。
原理流程
会话创建 #t → 本会话可见使用 → 会话断开 / 过程退出 → 自动删除
##t:所有连接可见 → 最后一个连接断开后删除
代码 / 命令
CREATE TABLE #SessionTemp (Id INT);
-- 过程内:
CREATE PROCEDURE dbo.ProcA AS
BEGIN
CREATE TABLE #Inner (Id INT);
-- 过程结束 #Inner 自动清理
END;
常见误区
- 误区:临时表必须显式
DROP TABLE。正解:作用域结束自动清理;显式 DROP 可提前释放空间。 - 误区:全局临时表创建后永久存在。正解:引用它的最后一个会话断开即删除。
追问
- 问:临时表名在不同会话会冲突吗?答:局部临时表按会话隔离,引擎追加会话后缀,不冲突。
- 问:存储过程重编译会重建临时表吗?答:涉及临时表的语句可能触发重编译,影响性能时需评估。
05.4.1.4-014 什么是排序/哈希溢出(spill)?
频率 ★★★★☆ | 难度 高级
一句话结论
排序、哈希连接/聚合需要的内存授予不足时,中间结果落盘到 tempdb 工作表,即 spill;磁盘 IO 替代内存操作,性能明显下降。
概念解释
- 内存授予(Memory Grant):执行计划为排序/哈希预留的内存量。
- spill:预留内存放不下 → 写 tempdb → 分批处理。
- 计划中可见
Spill相关告警或磁盘使用提示。
原理流程
估算内存 → 授予 work_mem 类内存
数据量 > 授予内存 → tempdb 工作表落盘 → 读取合并
代码 / 命令
-- 查看查询是否落盘:实际执行计划中 Sort/Hash 算子的 spill 信息
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- 查看内存授予与等待相关 DMV
SELECT session_id, requested_memory_kb, granted_memory_kb,
required_memory_kb, wait_type
FROM sys.dm_exec_query_memory_grants;
常见误区
- 误区:spill 一定是索引缺失导致。正解:可能是统计不准低估行数、内存授予不足或结果集确实过大。
- 误区:增大内存授予就能消除所有 spill。正解:大结果集仍需落盘;应先优化查询减少扫描量。
追问
- 问:怎么判断 spill 发生在哪个算子?答:实际计划中 Sort/Hash Match 算子的溢出明细与磁盘读写。
- 问:tempdb 慢会直接拖慢 spill 查询吗?答:是,spill 读写都在 tempdb,IO 延迟直接进入查询耗时。
05.4.1.4-015 如何监控 tempdb 空间与争用?
频率 ★★★☆☆ | 难度 进阶
一句话结论
用文件空间 DMV 看用户对象/内部对象/版本存储占用,用等待 DMV 看分配闩锁与 spill 等待,结合文件增长历史趋势规划容量。
概念解释
- 空间视图:
sys.dm_db_file_space_usage按文件统计页占用。 - 会话视图:
sys.dm_db_session_space_usage看各会话分配量。 - 等待视图:
PAGELATCH_*、PAGEIOLATCH_*、排序/哈希等待类型。
原理流程
空间增长趋势 → 容量规划
分配闩锁等待 → 系统页争用
spill 等待 + 内部对象占用 → 查询内存/索引问题
代码 / 命令
-- 各会话 tempdb 分配页
SELECT session_id,
user_object_alloc_page_count * 8 / 1024 AS user_obj_mb,
internal_object_alloc_page_count * 8 / 1024 AS internal_mb
FROM sys.dm_db_session_space_usage
ORDER BY internal_mb DESC;
-- tempdb 数据文件空间概览
SELECT file_id, SUM(extent_page_count) OVER () AS total_pages
FROM sys.dm_db_file_space_usage;
常见误区
- 误区:tempdb 总量只看数据文件大小。正解:要拆分用户对象、内部对象、版本存储分别看来源。
- 误区:单看一次快照就能判断趋势。正解:需周期采样观察增长曲线。
追问
- 问:哪个会话在占 tempdb?答:
sys.dm_db_session_space_usage按会话聚合,再关联会话与 SQL。 - 问:版本存储暴涨对应哪个指标?答:版本存储保留页计数(见 05.4.1.4-006)。
05.4.1.4-016 系统数据库的备份策略与用户库有什么不同?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
master、msdb 应定期备份;model 建议备份;resource 不单独备份;tempdb 不备份。
概念解释
| 系统库 | 是否备份 | 说明 |
|---|---|---|
| master | 是 | 元数据与登录,变更后应备份 |
| msdb | 是 | 作业、备份历史、策略 |
| model | 建议 | 模板设置,体积小 |
| resource | 否 | 随安装版本恢复 |
| tempdb | 否 | 重启重建 |
原理流程
- 实例级配置变更(建登录、改作业)后及时备份 master/msdb。
代码 / 命令
BACKUP DATABASE master TO DISK = N'/backup/master.bak';
BACKUP DATABASE msdb TO DISK = N'/backup/msdb.bak';
常见误区
- 误区:只备份用户库就够灾备。正解:master 丢失会导致实例无法定位数据库与登录。
- 误区:resource 也要纳入定期备份。正解:它由安装介质/版本修复恢复。
追问
- 问:重建实例后怎么恢复登录?答:还原 master,或用脚本迁移登录名与权限。
- 问:tempdb 损坏怎么处理?答:通常重启实例重建;配置错误时按文档调整文件后重启。
05.4.1.4-017 内存授予与 tempdb spill 是什么关系?
频率 ★★☆☆☆ | 难度 高级
一句话结论
内存授予不足是 spill 的直接诱因;授予大小由优化器按统计信息估算,估算偏低或内存紧张都会导致中间结果落盘 tempdb。
概念解释
- 授予内存:执行计划运行时拿到的排序/哈希工作内存。
- 估算偏差:统计过期导致低估行数,授予偏小。
- 内存压力:并发查询多,总内存授予受限。
原理流程
统计信息估算行数 → 计算所需内存 → 申请授予
授予 < 实际需求 → 溢出写 tempdb
代码 / 命令
-- 查看正在等待/已获得内存授予的查询
SELECT r.session_id, r.request_id, g.granted_memory_kb,
g.required_memory_kb, t.text
FROM sys.dm_exec_query_memory_grants g
JOIN sys.dm_exec_requests r ON r.session_id = g.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t;
常见误区
- 误区:spill 后 SQL 结果错误。正解:spill 只影响性能,结果正确性由执行算法保证。
- 误区:调大
max server memory就一定消除 spill。正解:还需修正统计与查询,否则授予估算依旧偏低。
追问
- 问:怎么定位导致 spill 的大查询?答:计划中找 spill 算子,关联内存授予 DMV 与慢查询会话。
- 问:排序 spill 与哈希 spill 哪个更常见?答:视 workload 而定;大数据量排序与大表哈希连接都易发生。
05.4.1.4-018 SQL Server tempdb 与 MySQL 临时表、PG 临时表空间怎么对照?
频率 ★★★☆☆ | 难度 高级
一句话结论
三者都有临时工作区,但定位不同:SQL Server tempdb 实例级共享且承载版本存储;MySQL 内部临时表在内存/磁盘临时目录;PG 临时表存于用户表空间或专用临时表空间。
概念解释
| 维度 | SQL Server tempdb | MySQL 临时表 | PostgreSQL 临时表空间 |
|---|---|---|---|
| 级别 | 实例共享 | 会话级临时表 + 内部临时表 | 会话级临时表,可配专用表空间 |
| 版本存放 | 旧版本进 tempdb | undo 在 InnoDB 表空间 | 版本留在堆,VACUUM 回收 |
| 溢出位置 | tempdb 工作表 | tmp_table_size/磁盘临时目录 | 临时表空间 |
| 备份 | 不备份 | 用户临时表不备份 | 不纳入常规备份 |
原理流程
- 对照主线:临时数据不落业务库,但实现位置与回收机制各有不同。
代码 / 命令
-- SQL Server:确认 tempdb 文件布局
SELECT name, size * 8 / 1024 AS size_mb FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
常见误区
- 误区:三者"临时表"语义完全相同。正解:SQL Server 临时对象与版本/spill 共用 tempdb;MySQL/PG 临时表主要服务会话临时数据。
- 误区:PG 也把 MVCC 旧版本放临时表空间。正解:PG 版本留在堆中,靠 VACUUM 回收。
追问
- 问:MySQL 内部临时表落盘受哪个参数控制?答:
tmp_table_size、max_heap_table_size,详见 05.2.1-020。 - 问:PG 专用临时表空间解决什么?答:隔离临时对象 IO,避免膨胀用户表空间。
参考来源
- Microsoft Learn:tempdb 数据库:https://learn.microsoft.com/sql/relational-databases/databases/tempdb-database
- Microsoft Learn:优化 tempdb 性能:https://learn.microsoft.com/sql/relational-databases/performance/optimizing-tempdb-performance
- Microsoft Learn:系统数据库:https://learn.microsoft.com/sql/relational-databases/databases/system-databases
- Microsoft Learn:内存授予:https://learn.microsoft.com/sql/relational-databases/performance/memory-grants