SQL Server DBA 面试必问的 30 个问题,建议收藏!
👋 我是三笠,一名专注数据库运维与架构的 DBA,平时分享数据库实战经验与技术笔记,个人网站:ora100.com

前言
前面我们分别整理了 Oracle DBA 和 MySQL DBA 面试必问的 30 个问题,今天继续这个系列,聊聊 SQL Server DBA 面试。
SQL Server 在企业信息化系统中应用非常广泛,尤其是在制造业、医疗、ERP、MES、财务以及各类管理系统中,仍然占据着非常重要的位置。
对于 SQL Server DBA 来说,掌握基本的 SQL 查询和数据库管理命令只是入门。
真正的面试重点通常集中在以下几个方面:
- SQL Server 内部架构和存储机制。
- 数据库文件、事务日志和 TempDB 管理。
- 索引、执行计划和 SQL 性能优化。
- 事务隔离、锁等待和死锁排查。
- Always On、故障转移集群和高可用。
- 备份恢复、数据库迁移和日常运维。
例如:
- SQL Server 为什么使用 8KB 数据页?
- MDF、NDF 和 LDF 分别有什么作用?
- SQL Server 为什么会发生事务日志增长?
- 为什么有索引,SQL 还是走了全表扫描?
- SQL Server 中什么是参数嗅探?
- Always On 和故障转移群集有什么区别?
- 数据库误删除数据后,如何通过事务日志恢复?
这些问题不仅是面试考点,也是 SQL Server DBA 日常工作中经常遇到的实际问题。
今天整理了 30 个 SQL Server DBA 面试必问问题,按照六大技术领域进行分类,每道题都附上参考答案和面试加分点。
建议收藏,看看自己能答对多少。
一、SQL Server 基础架构篇(1 ~ 5)
1. SQL Server 的整体架构是什么样的?
参考答案:
SQL Server 采用客户端请求、数据库引擎处理和存储引擎读写相结合的架构。
从主要功能来看,可以分为以下几个部分:
1. Relational Engine(关系引擎)
负责 SQL 请求的解析、优化和执行。
主要包括:
- Parser:解析器。
- Algebrizer:绑定和语义处理。
- Query Optimizer:查询优化器。
- Query Execution Engine:查询执行引擎。
2. Storage Engine(存储引擎)
负责数据访问和存储管理,包括:
- Buffer Manager:缓冲区管理。
- Access Methods:数据访问方法。
- Transaction Manager:事务管理。
- Lock Manager:锁管理。
3. SQL Server 操作系统层
负责线程调度、内存管理、I/O 等底层操作。
面试加分点:
SQL Server 并不是简单地收到 SQL 后直接读取磁盘。
查询执行过程中会涉及执行计划、Buffer Pool、锁、事务日志以及存储引擎等多个组件。
2. SQL Server 中 MDF、NDF 和 LDF 文件有什么区别?
参考答案:
SQL Server 数据库通常包含三种常见文件。
| 文件类型 | 作用 |
|---|---|
| MDF | 主数据文件 |
| NDF | 次要数据文件 |
| LDF | 事务日志文件 |
MDF(Primary Data File)
主数据文件,一个数据库必须有且只有一个主数据文件。
主要保存数据库对象及相关数据。
NDF(Secondary Data File)
次要数据文件,一个数据库可以有多个,也可以没有。
可以通过多个文件和文件组实现数据组织及存储管理。
LDF(Log Data File)
事务日志文件,用于记录数据库事务产生的日志信息,支持事务回滚、崩溃恢复和日志备份。
查看数据库文件:
SELECT
name,
type_desc,
physical_name,
size * 8 / 1024 AS size_mb
FROM sys.database_files;
面试加分点:
MDF 和 NDF 属于数据文件,LDF 属于事务日志文件。
不能通过直接删除 LDF 文件的方式解决事务日志空间不足问题,否则可能导致数据库无法正常恢复。
3. SQL Server 的数据页和区是什么?
参考答案:
SQL Server 使用数据页(Page)作为基本的数据存储和 I/O 管理单位。
标准数据页大小为 8KB。
一个区(Extent)由 8 个连续的数据页组成,大小为 64KB。
SQL Server 常见的数据页类型包括:
- Data Page:数据页。
- Index Page:索引页。
- IAM Page:索引分配映射页。
- PFS Page:页可用空间信息页。
- GAM Page:全局分配映射页。
- SGAM Page:共享全局分配映射页。
简单理解:
数据页负责存储数据,区负责组织数据页的分配,分配管理页负责跟踪数据库空间使用情况。
面试加分点:
SQL Server 的数据页大小是固定的 8KB。
一行数据如果无法存储在单个数据页中,可能涉及行溢出或 LOB 数据存储机制,不能简单理解为所有数据都必须完整存放在一个页中。
4. SQL Server 中 Buffer Pool 有什么作用?
参考答案:
Buffer Pool 是 SQL Server 数据库引擎重要的内存管理机制。
主要作用是缓存从磁盘读取的数据页,减少物理 I/O。
执行查询时:
- 首先检查所需数据页是否已经在 Buffer Pool 中。
- 如果存在,直接从内存读取。
- 如果不存在,则从磁盘读取数据页并加载到 Buffer Pool。
- 如果数据页发生修改,则将其标记为脏页(Dirty Page)。
- 后续由 Checkpoint 或 Lazy Writer 等机制将脏页写入数据文件。
面试加分点:
SQL Server 中的 Buffer Pool 不仅缓存表数据,也缓存索引页。
数据库性能优化时,应该关注 Buffer Cache Hit Ratio、Page Life Expectancy 等指标,但不能仅凭单个指标判断内存是否不足。
5. SQL Server 的事务日志有什么作用?为什么不能直接删除?
参考答案:
SQL Server 使用事务日志记录数据库事务产生的修改信息。
主要作用:
- 保证事务的持久性。
- 支持事务回滚。
- 支持数据库崩溃恢复。
- 支持事务日志备份。
- 支持时间点恢复。
- 支持部分高可用和数据同步机制。
SQL Server 采用 Write-Ahead Logging(WAL,预写日志)机制。
简单理解:
在相关数据页写入数据文件之前,必须先保证对应的日志记录已经持久化到事务日志中。
查看事务日志使用情况:
DBCC SQLPERF(LOGSPACE);
面试加分点:
事务日志不能通过操作系统直接删除文件来清理。
如果日志空间不断增长,应该先分析恢复模式、日志截断条件、长事务、备份状态等原因。
二、存储与文件管理篇(6 ~ 10)
6. SQL Server 中什么是 Filegroup?有什么作用?
参考答案:
Filegroup(文件组)是 SQL Server 用于组织数据库数据文件的逻辑结构。
主要分为:
- PRIMARY:主文件组。
- USER-DEFINED:用户定义文件组。
- FILESTREAM:用于 FILESTREAM 数据存储的文件组。
一个文件组可以包含多个数据文件。
通过文件组可以:
- 将不同业务对象分配到不同存储位置。
- 实现数据文件的逻辑组织。
- 进行部分备份和恢复。
- 支持大型数据库的存储管理。
例如:
ALTER DATABASE TestDB
ADD FILEGROUP FG_DATA;
向文件组添加数据文件:
ALTER DATABASE TestDB
ADD FILE
(
NAME = N'TestDB_Data2',
FILENAME = N'D:\SQLData\TestDB_Data2.ndf',
SIZE = 1024MB,
FILEGROWTH = 256MB
)
TO FILEGROUP FG_DATA;
面试加分点:
PRIMARY 文件组包含主数据文件和系统对象。
生产环境可以根据业务和存储规划合理划分文件组,但不能认为文件组越多,数据库性能就一定越好。
7. SQL Server 的事务日志为什么会不断增长?
参考答案:
事务日志增长通常与日志截断条件有关。
常见原因包括:
- 数据库使用 FULL 恢复模式,但长期没有进行事务日志备份。
- 存在长时间运行的事务。
- 事务复制或 Always On 等机制导致日志仍被需要。
- 数据库存在复制相关日志读取延迟。
- 日志文件初始容量或增长设置不合理。
- 大量 DML 操作产生了大量日志。
查看日志使用情况:
DBCC SQLPERF(LOGSPACE);
查看日志截断等待原因:
SELECT
name,
recovery_model_desc,
log_reuse_wait_desc
FROM sys.databases;
常见状态包括:
- NOTHING
- LOG_BACKUP
- ACTIVE_TRANSACTION
- REPLICATION
- AVAILABILITY_REPLICA
处理建议:
根据具体原因采取措施,例如执行日志备份、处理长事务、排查复制延迟或调整存储规划。
面试加分点:
FULL 恢复模式下,正常情况下需要定期备份事务日志。
但执行日志备份并不意味着 LDF 文件会自动缩小,只是可能释放内部可重复使用的日志空间。
8. SQL Server 的 TempDB 有什么作用?为什么它非常重要?
参考答案:
TempDB 是 SQL Server 的系统数据库之一。
主要用于存储临时数据和执行过程中的中间结果。
常见用途:
- 临时表。
- 表变量。
- 排序操作。
- Hash Join。
- 行版本存储。
- 部分查询执行过程中的工作表和工作文件。
查看 TempDB 文件:
USE tempdb;
SELECT
name,
physical_name,
size * 8 / 1024 AS size_mb
FROM sys.database_files;
面试加分点:
TempDB 是 SQL Server 实例级共享资源,多个数据库和会话都可能使用它。
如果 TempDB 出现空间不足、I/O 瓶颈或分配竞争,可能影响整个实例的业务。
SQL Server 2019 及以上版本还引入了内存优化 TempDB 元数据等相关能力。
9. SQL Server 中什么是 VLF?为什么需要关注?
参考答案:
VLF(Virtual Log File)是 SQL Server 事务日志文件内部的逻辑管理单元。
事务日志文件在物理上是一个文件,但内部会划分为多个 VLF。
当事务日志增长时,SQL Server 会根据增长策略创建新的 VLF。
如果日志文件频繁自动增长,可能导致 VLF 数量过多。
潜在影响包括:
- 数据库恢复时间增加。
- 日志管理复杂度提高。
- 某些日志相关操作效率下降。
查看 VLF 数量:
SELECT COUNT(*) AS VLF_COUNT
FROM sys.dm_db_log_info(DB_ID());
面试加分点:
VLF 数量并不是越少越好,也不是越多越好。
应该结合日志文件大小、增长策略和实际运行情况进行规划。
建议预先合理设置日志文件初始大小,并使用固定 MB 的增长单位,避免频繁小幅自动增长。
10. SQL Server 的 Recovery Model 有哪几种?有什么区别?
参考答案:
SQL Server 有三种恢复模式:
1. SIMPLE(简单恢复模式)
- 通常不支持事务日志备份。
- 不支持基于事务日志的时间点恢复。
- 已不再需要的日志记录通常可以在检查点后被截断。
2. FULL(完整恢复模式)
- 支持事务日志备份。
- 可以结合完整备份、差异备份和日志备份实现时间点恢复。
- 需要定期执行事务日志备份。
3. BULK_LOGGED(大容量日志恢复模式)
- 对部分大容量操作采用最小日志记录机制。
- 仍然支持事务日志备份。
- 特定大容量操作可能影响精确的时间点恢复能力。
查看数据库恢复模式:
SELECT
name,
recovery_model_desc
FROM sys.databases;
修改恢复模式:
ALTER DATABASE TestDB
SET RECOVERY FULL;
面试加分点:
如果数据库从 SIMPLE 切换到 FULL,通常需要先执行一次数据备份,建立后续日志备份链的基础。
不能只修改恢复模式,就认为已经具备完整的日志恢复能力。
三、事务与锁机制篇(11 ~ 15)
11. SQL Server 中事务的 ACID 特性是什么?
参考答案:
ACID 是数据库事务的四个基本特性。
A:Atomicity(原子性)
事务中的操作要么全部成功,要么全部回滚。
C:Consistency(一致性)
事务执行前后,数据库应该满足约束和业务规则。
I:Isolation(隔离性)
多个事务并发执行时,应该按照设定的隔离级别控制彼此之间的影响。
D:Durability(持久性)
事务提交后,已确认提交的数据应该具有持久性。
SQL Server 主要通过事务日志、锁、行版本控制和恢复机制等实现 ACID。
面试加分点:
事务日志是保证事务持久性和崩溃恢复的重要基础。
不能简单认为事务提交时,所有脏数据页都已经写入 MDF 文件。
12. SQL Server 有哪些事务隔离级别?
参考答案:
SQL Server 支持以下几种常见事务隔离级别:
| 隔离级别 | 特点 |
|---|---|
| READ UNCOMMITTED | 允许读取未提交数据 |
| READ COMMITTED | 默认隔离级别,避免脏读 |
| REPEATABLE READ | 防止已读取的数据被其他事务修改 |
| SNAPSHOT | 基于行版本控制实现事务级一致性读 |
| SERIALIZABLE | 最高隔离级别,防止相关范围内的幻读 |
设置隔离级别:
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED;
面试加分点:
SQL Server 的 READ COMMITTED 可以通过锁实现,也可以通过启用 READ_COMMITTED_SNAPSHOT 使用行版本控制实现。
两者的并发行为不同,需要结合数据库配置分析。
13. SQL Server 中有哪些常见锁类型?
参考答案:
SQL Server 常见锁类型包括:
按资源层级划分:
- RID:堆表中的行标识锁。
- KEY:索引键锁。
- PAGE:数据页锁。
- EXTENT:区锁。
- HoBT:堆或 B-Tree 锁定结构。
- TABLE:表锁。
- DATABASE:数据库锁。
按锁模式划分:
- Shared(S):共享锁。
- Exclusive(X):排他锁。
- Update(U):更新锁。
- Intent Shared(IS):意向共享锁。
- Intent Exclusive(IX):意向排他锁。
- Shared with Intent Exclusive(SIX):共享意向排他锁。
面试加分点:
SQL Server 的锁粒度可以根据执行情况发生变化。
行锁并不意味着一定不会发生锁升级,锁升级可能将大量细粒度锁转换成更高层级的锁。
14. 什么是 SQL Server 的锁升级(Lock Escalation)?
参考答案:
锁升级是指 SQL Server 在满足一定条件时,将大量较低粒度的锁转换为更高粒度的锁。
例如:
大量 KEY 锁
|
v
TABLE 锁
锁升级的主要目的是减少锁管理所需的内存资源。
可能触发锁升级的情况包括:
- 单条语句获取大量锁。
- 锁管理内存压力较大。
- 满足相关锁升级阈值。
排查方向:
可以通过 Extended Events 监控 lock_escalation 事件。
优化建议:
- 控制单次事务处理的数据量。
- 合理设计索引。
- 避免长事务。
- 根据业务特点分批处理大批量 DML。
面试加分点:
不能简单地认为 SQL Server 锁升级就是性能故障。
锁升级本身是正常的资源管理机制,但如果导致大量业务阻塞,就需要进一步分析。
15. SQL Server 发生死锁,应该如何排查?
参考答案:
死锁是指两个或多个事务互相持有对方需要的资源,形成循环等待。
例如:
事务 A:
BEGIN TRAN;
UPDATE Account
SET Balance = Balance - 100
WHERE ID = 1;
UPDATE Account
SET Balance = Balance + 100
WHERE ID = 2;
事务 B:
BEGIN TRAN;
UPDATE Account
SET Balance = Balance - 100
WHERE ID = 2;
UPDATE Account
SET Balance = Balance + 100
WHERE ID = 1;
两个事务以不同顺序访问相同资源,就可能产生死锁。
SQL Server 检测到死锁后,通常会选择一个事务作为牺牲者并回滚,返回错误 1205。
排查方法:
- 查看 SQL Server Error Log。
- 获取死锁图(Deadlock Graph)。
- 分析涉及的 SQL、对象和锁资源。
- 确认事务访问顺序。
- 检查索引和事务执行时间。
可以使用 Extended Events 捕获 xml_deadlock_report 事件。
面试加分点:
死锁和阻塞不是一回事。
阻塞可能是正常事务等待,而死锁属于循环等待,必须通过死锁检测机制处理。
四、SQL 性能优化篇(16 ~ 20)
16. SQL Server 中聚集索引和非聚集索引有什么区别?
参考答案:
SQL Server 索引主要包括聚集索引和非聚集索引。
Clustered Index(聚集索引)
聚集索引的叶子层存储实际数据行。
一个表只能有一个聚集索引,因为表的数据行只能按照一种聚集索引顺序组织。
Nonclustered Index(非聚集索引)
非聚集索引单独存储索引结构,并通过行定位信息找到对应的数据行。
一个表可以创建多个非聚集索引。
创建索引:
CREATE CLUSTERED INDEX
IX_Employee_ID
ON Employee(ID);
创建非聚集索引:
CREATE NONCLUSTERED INDEX
IX_Employee_Name
ON Employee(Name);
面试加分点:
对于具有聚集索引的表,非聚集索引的叶子记录通常包含聚集键作为行定位信息。
对于堆表,则使用 RID 定位数据行。
17. SQL Server 什么是覆盖索引?Include 列有什么作用?
参考答案:
覆盖索引是指索引能够直接满足 SQL 查询需要的所有列,不需要额外访问基础数据行。
SQL Server 支持通过 INCLUDE 添加非键列。
例如:
CREATE NONCLUSTERED INDEX
IX_Orders_UserID
ON Orders(UserID)
INCLUDE(OrderDate, Amount);
查询:
SELECT
UserID,
OrderDate,
Amount
FROM Orders
WHERE UserID = 100;
如果优化器选择该索引,就可以直接从索引中获取查询所需字段。
优势:
- 减少 Key Lookup。
- 降低数据访问成本。
- 减少不必要的基础数据行读取。
面试加分点:
INCLUDE 列只存储在索引叶子层,不参与索引键的排序和查找。
不应将大量字段无节制地加入 INCLUDE,否则会增加索引空间和维护成本。
18. SQL Server 中什么是执行计划?如何查看?
参考答案:
执行计划描述了 SQL Server 执行 SQL 时采用的具体操作步骤。
常见操作包括:
- Clustered Index Scan。
- Clustered Index Seek。
- Index Scan。
- Index Seek。
- Key Lookup。
- Nested Loops。
- Hash Match。
- Sort。
查看实际执行计划可以在 SSMS 中启用:
Include Actual Execution Plan
也可以使用:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT *
FROM Orders
WHERE UserID = 100;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
面试加分点:
实际执行计划比单纯的预估执行计划包含更多运行时信息,例如实际行数和执行次数。
分析 SQL 性能时,应该重点比较 Estimated Number of Rows 和 Actual Number of Rows。
19. SQL Server 中什么是参数嗅探(Parameter Sniffing)?
参考答案:
参数嗅探是 SQL Server 优化器在编译或重新编译参数化语句时,利用当时参数值估算数据分布并生成执行计划的机制。
例如:
CREATE PROCEDURE GetOrders
@Status INT
AS
BEGIN
SELECT *
FROM Orders
WHERE Status = @Status;
END;
假设:
- Status=1 的数据占 90%。
- Status=2 的数据占 1%。
如果第一次编译时使用 Status=1,优化器可能选择一种适合大结果集的执行计划。
之后执行 Status=2 时,原有执行计划可能并不理想。
常见处理方法:
- 检查统计信息。
- 评估数据分布和直方图。
- 使用 OPTION (RECOMPILE)。
- 评估 OPTIMIZE FOR。
- 根据场景使用参数化 SQL 或其他执行计划管理策略。
面试加分点:
参数嗅探本身不是 Bug,而是优化器利用参数值生成执行计划的正常机制。
真正的问题是数据分布差异较大,导致同一个缓存计划无法适应不同参数值。
20. SQL Server 数据库 CPU 使用率突然飙高,应该如何排查?
参考答案:
建议从 SQL、执行计划和系统资源三个层面排查。
第一步:查看当前高 CPU SQL。
SELECT TOP 20
qs.total_worker_time / 1000 AS total_cpu_ms,
qs.execution_count,
qs.total_worker_time /
NULLIF(qs.execution_count, 0) / 1000 AS avg_cpu_ms,
SUBSTRING(
st.text,
(qs.statement_start_offset / 2) + 1,
(
CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset
) / 2 + 1
) AS sql_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;
第二步:查看执行计划。
重点检查:
- 大量扫描。
- 不合理的 Join。
- 估算行数与实际行数差异。
- 隐式类型转换。
- 排序和 Hash 操作。
第三步:检查系统资源。
- CPU 使用率。
- 内存压力。
- 磁盘 I/O。
- 并发会话数量。
- 等待事件。
面试加分点:
sys.dm_exec_query_stats 主要提供缓存中执行计划的累计统计信息,并不包含所有历史执行记录。
对于已经从缓存中淘汰的执行计划,需要结合 Query Store、Extended Events 或其他监控数据分析。
五、高可用与灾难恢复篇(21 ~ 25)
21. SQL Server Always On Availability Groups 是什么?
参考答案:
Always On Availability Groups(AG)是 SQL Server 提供的高可用和灾难恢复技术。
通过将一组用户数据库配置为可用性组,在多个 SQL Server 实例之间同步或异步传输事务日志,实现数据库级别的高可用。
主要特点:
- 支持主副本和辅助副本。
- 支持同步提交和异步提交。
- 支持自动或手动故障转移,具体取决于配置。
- 支持可读辅助副本,取决于版本和配置。
- 可以通过 Listener 提供统一连接入口。
面试加分点:
AG 是数据库级别的高可用方案,不等于整个 SQL Server 实例的所有系统数据库和实例配置都会自动同步。
22. SQL Server Always On 和 Failover Cluster Instance(FCI)有什么区别?
参考答案:
两者都是 SQL Server 高可用技术,但实现方式不同。
| 对比项目 | Always On AG | FCI |
|---|---|---|
| 保护对象 | 数据库 | SQL Server 实例 |
| 存储架构 | 通常各副本有独立数据副本 | 通常依赖共享存储 |
| 故障转移 | 副本之间切换 | 实例在节点之间切换 |
| 辅助副本读取 | 支持,取决于配置 | 不支持同时在另一节点读取同一实例 |
| 是否需要 WSFC | Windows 部署通常需要 | Windows 部署需要 |
AG 的优势:
可以提供独立数据库副本,适合高可用和灾备场景。
FCI 的优势:
保护整个 SQL Server 实例,包括实例级资源和数据库文件访问能力。
面试加分点:
AG 和 FCI 可以组合使用。
例如,通过 FCI 保护本地实例,再通过 AG 实现跨站点数据库级灾备。
23. Always On 中同步提交和异步提交有什么区别?
参考答案:
Always On AG 支持两种主要的数据同步提交模式。
同步提交(Synchronous Commit)
主副本提交事务时,需要等待同步辅助副本确认相关日志已经持久化到辅助副本的事务日志中。
优势:
- 可以提供较强的数据保护。
- 满足条件时支持自动故障转移。
- 适合低延迟网络环境。
缺点:
- 主副本提交性能可能受到网络延迟影响。
异步提交(Asynchronous Commit)
主副本提交事务时,不需要等待辅助副本确认日志持久化。
优势:
- 适合远距离灾备。
- 对主副本事务提交延迟影响相对较小。
缺点:
- 主副本故障时,可能丢失尚未传输或持久化到辅助副本的事务日志。
面试加分点:
同步提交并不意味着所有情况下都绝对零数据丢失。
还需要考虑同步副本数量、同步状态、故障转移条件以及辅助副本是否已经具备可恢复状态。
24. SQL Server AG 故障转移后,应用程序如何连接新的主副本?
参考答案:
SQL Server AG 通常通过 Listener 提供统一的客户端连接入口。
Listener 包含:
- 虚拟网络名称。
- IP 地址。
- 对应的端口。
应用程序连接 Listener,而不是直接连接某一个物理节点。
当主副本发生故障转移后,Listener 会根据新的主副本状态提供连接路由。
客户端重新连接 Listener,即可访问新的主副本。
面试加分点:
故障转移并不代表已有数据库连接会自动恢复。
应用程序仍然需要具备连接重试机制,并根据业务需要设置合理的连接超时和重试策略。
25. SQL Server Log Shipping 是什么?与 Always On 有什么区别?
参考答案:
Log Shipping(日志传送)是一种基于事务日志备份和还原的灾难恢复技术。
基本流程:
- 主数据库定期执行事务日志备份。
- 将日志备份文件复制到辅助服务器。
- 辅助服务器按照计划还原事务日志。
- 发生故障时,手动恢复辅助数据库并切换业务。
与 Always On 相比:
| 对比项目 | Log Shipping | Always On AG |
|---|---|---|
| 数据同步方式 | 备份、复制、还原日志 | 持续传输和应用日志 |
| 故障切换 | 通常需要手动处理 | 支持自动或手动切换 |
| 监控 | 可配置监控告警 | 提供集群状态监控 |
| 辅助数据库状态 | 通常处于恢复状态 | 可以配置只读访问 |
| 架构复杂度 | 相对简单 | 相对复杂 |
面试加分点:
Log Shipping 并不要求必须使用 Windows Server Failover Clustering。
它适合一些成本敏感、可以接受人工切换和一定恢复时间的灾备场景。
六、备份恢复与日常运维篇(26 ~ 30)
26. SQL Server 有哪些常见的备份类型?
参考答案:
SQL Server 常见备份类型包括:
1. Full Backup(完整备份)
备份数据库中的数据,并包含恢复所需的日志信息。
2. Differential Backup(差异备份)
备份自最近一次完整备份以来发生变化的数据区。
3. Transaction Log Backup(事务日志备份)
备份事务日志记录,用于恢复和时间点恢复。
4. Copy-Only Backup(仅复制备份)
用于临时备份,不改变常规备份链相关的基准状态。
例如:
完整备份:
BACKUP DATABASE TestDB
TO DISK = 'D:\Backup\TestDB_full.bak'
WITH COMPRESSION, CHECKSUM;
差异备份:
BACKUP DATABASE TestDB
TO DISK = 'D:\Backup\TestDB_diff.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;
日志备份:
BACKUP LOG TestDB
TO DISK = 'D:\Backup\TestDB_log.trn'
WITH COMPRESSION, CHECKSUM;
面试加分点:
完整备份、差异备份和事务日志备份承担不同作用。
差异备份以最近一次完整备份为基准,而事务日志备份需要结合日志备份链进行恢复。
27. SQL Server 如何进行时间点恢复?
参考答案:
SQL Server 可以通过完整备份、差异备份和事务日志备份,将数据库恢复到指定时间点。
例如:
RESTORE DATABASE TestDB
FROM DISK = 'D:\Backup\TestDB_full.bak'
WITH NORECOVERY;
恢复差异备份:
RESTORE DATABASE TestDB
FROM DISK = 'D:\Backup\TestDB_diff.bak'
WITH NORECOVERY;
最后恢复事务日志:
RESTORE LOG TestDB
FROM DISK = 'D:\Backup\TestDB_log.trn'
WITH STOPAT = '2026-11-18T10:30:00',
RECOVERY;
其中:
- NORECOVERY:保持数据库处于恢复状态,以便继续应用后续备份。
- RECOVERY:完成恢复并允许数据库正常访问。
- STOPAT:指定恢复到的时间点。
面试加分点:
时间点恢复需要完整的备份链和可用事务日志。
如果发生误操作,应该尽可能先保护当前日志,评估是否需要进行尾日志备份(Tail-Log Backup),避免进一步丢失可恢复的数据。
28. SQL Server 中什么是 Tail-Log Backup?
参考答案:
Tail-Log Backup(尾日志备份)是指在数据库发生故障或准备进行恢复之前,对尚未备份的事务日志进行最后一次备份。
主要作用是尽可能保存故障发生前尚未备份的日志记录。
例如:
数据库文件发生损坏,但事务日志文件仍然可访问。
这时可以考虑先执行尾日志备份,再进行数据库恢复。
示例:
BACKUP LOG TestDB
TO DISK = 'D:\Backup\TestDB_tail.trn'
WITH NORECOVERY, CHECKSUM;
面试加分点:
尾日志备份是否能够执行,取决于事务日志是否可访问以及数据库当前状态等条件。
如果日志文件本身已经损坏或丢失,就不一定能够完成尾日志备份。
29. SQL Server 数据库出现 SUSPECT 状态,应该如何处理?
参考答案:
SUSPECT 表示 SQL Server 在数据库恢复过程中遇到了无法正常完成恢复的问题。
常见原因:
- 数据文件损坏。
- 事务日志文件丢失或损坏。
- 磁盘空间不足。
- 存储设备异常。
- 数据库文件无法访问。
- 数据库恢复过程中出现严重错误。
排查步骤:
第一步:查看数据库状态。
SELECT
name,
state_desc
FROM sys.databases;
第二步:检查 SQL Server Error Log。
EXEC xp_readerrorlog;
第三步:检查数据文件和日志文件。
确认文件路径、磁盘空间、文件权限以及底层存储状态。
第四步:评估恢复方案。
优先考虑使用可靠备份恢复数据库。
如果数据库文件损坏,需要根据损坏范围和可恢复性制定方案。
面试加分点:
不要一看到 SUSPECT 就直接执行:
ALTER DATABASE TestDB
SET EMERGENCY;
EMERGENCY 和 DBCC CHECKDB 的相关修复能力是特殊情况下的恢复手段,不应该代替正常备份恢复流程。
执行任何可能丢失数据的修复操作之前,都应该先保留数据库文件副本并评估风险。
30. SQL Server 如何检查数据库是否存在一致性问题?
参考答案:
SQL Server 提供 DBCC CHECKDB 用于检查数据库的逻辑和物理一致性。
执行命令:
DBCC CHECKDB('TestDB')
WITH NO_INFOMSGS;
主要检查:
- 数据页一致性。
- 索引结构一致性。
- 系统目录一致性。
- 数据库对象之间的相关一致性。
也可以使用:
DBCC CHECKDB('TestDB')
WITH PHYSICAL_ONLY;
PHYSICAL_ONLY 主要进行物理一致性检查,通常比完整检查开销更小,但不能代替完整的 CHECKDB。
面试加分点:
DBCC CHECKDB 是 SQL Server 数据库健康检查的重要手段。
生产环境应该定期执行,并结合备份恢复演练验证数据库的可恢复性。
如果发现一致性错误,应优先分析错误范围,并使用可靠备份恢复。
写在最后
以上 30 个问题,基本覆盖了 SQL Server DBA 面试中比较重要的六大技术领域。
| 技术领域 | 题目数量 |
|---|---|
| 基础架构 | 5 |
| 存储与文件管理 | 5 |
| 事务与锁机制 | 5 |
| SQL 性能优化 | 5 |
| 高可用与灾难恢复 | 5 |
| 备份恢复与日常运维 | 5 |
| 合计 | 30 |
如果你正在准备 SQL Server DBA 面试,我建议重点掌握以下几个问题:
- MDF、NDF 和 LDF 分别有什么作用?
- SQL Server 为什么会发生事务日志增长?
- 聚集索引和非聚集索引有什么区别?
- 参数嗅探是什么?如何解决?
- SQL Server 发生死锁,应该如何定位?
- Always On AG 和 FCI 有什么区别?
- SQL Server 如何进行时间点恢复?
- 数据库出现 SUSPECT 状态,应该如何处理?
这些问题不仅是面试考点,也是生产环境中 DBA 必须掌握的技术。
最后送给正在准备 SQL Server DBA 面试的朋友一句话: 会写 SQL,只是数据库工作的开始。
真正优秀的 DBA,不仅要理解数据库内部机制,还要能够处理性能瓶颈、保障数据安全,并在生产故障发生时快速恢复业务。
本文主要以 SQL Server 2016、2019、2022 为背景整理,不同版本在 Always On、执行计划、监控视图及部分功能上存在差异,实际操作时应结合对应版本进行验证。
写文章之外,我也在持续完善自己的数据库学习与运维平台 ORA100。
平时整理的数据库实战案例、常用 SQL、运维脚本和实用工具,都可以在这里找到。
网站地址:ora100.com
感兴趣的话,可以来看看。

- 点赞
- 收藏
- 关注作者
评论(0)