罗罗编程学习记录
本页内容 展开查看

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(写闩锁)常见于分配记录更新。

原理流程

  1. 并发会话同时申请新页 → 查 GAM/PFS 找空闲。
  2. 同一系统页的闩锁被排队持有。
  3. 等待累积,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,避免膨胀用户表空间。

参考来源