主页 / dba / 问题 / 104460
Accepted
RayofCommand
Asked: 2015-06-19 06:38:44 +0800 CST

备份内部 - 备份作业运行时会发生什么 - 就 SQL Server 中的锁定和性能开销而言？

  • 772

对于 MySQL，我知道数据库是在 SQL 语句中逐表备份的，这会导致锁定，如果您在备份时更新列，您可能会遇到完整性问题。

据我了解，这不适用于 Microsoft SQL Server，但 SQL Server 如何处理这个问题？是否有一些内部冻结以保持数据库一致？

另外我听说备份是单线程的，这意味着它只使用一个内核，假设您备份到单个文件。还假设您有一台多核机器，例如 16 个内核，或者至少比一个多得多。

根据我的个人经验，我在进行备份时从未遇到过问题，既没有锁定问题也没有开销问题，但我的经验是有限的。这就是为什么我总是建议在服务器属性中打开备份压缩。

那么当备份作业运行时会发生什么？不同版本也有显着差异吗？例如 2008、2012 和 2014（不是许可证）。

sql-server sql-server-2012

3 个回答

  1. Best Answer

    您的所有观点都包含在备用神话中-保罗·兰德尔（Paul Randal）

    30-01) 备份操作导致阻塞

    不会。备份操作不会锁定用户对象。备份确实会导致 I/O 子系统上的读取负载非常重，因此看起来工作负载被阻塞了，但实际上并非如此。它只是被放慢了速度。有一种特殊情况，即必须获取批量记录的扩展区的备份将采用文件锁定，这可能会阻止检查点操作——但 DML 永远不会被阻止。

    另外我听说备份是单线程的，这意味着它只使用一个内核，假设您备份到单个文件。

    对单个文件或设备进行备份时将使用 1 个写入器线程。因此，如果您要备份到多个文件/设备（即多个 .bak 文件），每个文件/设备将有一个写入线程。

    提高备份性能的最简单方法是允许备份操作并行化，这称为备份条带化。默认情况下，每个要读取的驱动器号或挂载点都有一个数据读取器线程，每个要写入的备份设备都有一个数据写入器线程。

    查看

    1. SQL Server 2008 Microsoft 认证大师 (MCM) 准备视频，尤其是备份内部知识。
    2. 了解备份内部结构以及如何跟踪备份和恢复吞吐量（第 1 部分） - 作者：Jonathan Kehayias
    3. 了解备份内部结构以及如何跟踪备份和恢复吞吐量（第 2 部分）- 作者：Jonathan Kehayias
    • 10

  2. Paul 撰写的关于备份内部的文章非常出色，您必须阅读它。添加其他人所说的内容并强调您问题的特定部分

    另外我听说备份是单线程的，这意味着它只使用一个内核，假设您备份到单个文件。还假设您有一台多核机器，例如 16 个内核，或者至少比一个多得多。

    备份操作can use parallelism，但请记住，这不是SQL Server 中的优化器驱动的并行性，它由所涉及的磁盘数量驱动，其中备份必须读取数据文件，备份写入数据文件的位置以及创建的备份文件的数量。

    MAXDOP进行 SQL Server 备份时不能使用提示

    您无法在 SSMS 中为简单的 TSQL 备份操作生成执行计划。

    SQL Server 中查询优化器驱动的并行性基本上适用于所涉及的运算符（实际上它更复杂，但为了简单起见，您可以使用它），因为备份操作不涉及任何运算符，因此它不能使用优化器驱动的并行性。

    我在 Technet Wiki 上写了一篇关于备份和并行性的文章，其中我使用简单的示例来解释 SQL Server 备份期间的并行性。以下是结论

    1. 如果数据库文件位于多个磁盘上，则备份操作将在每个设备驱动器的线程上启动以读取数据。同样，如果在多个驱动器/安装点上进行恢复，备份操作将启动每个驱动器/安装点的一个线程

    2. 即使您在同一驱动器上转储多个备份副本，我们也会为每个备份文件转储一个线程。

    3. 与备份相关的并行性与条带有关。每个条带都有自己的工作线程，这实际上是备份/恢复中唯一应该被视为并行操作的部分。

    4. 最大并行度对备份操作没有影响。

    我从 Paul 和 Bob Dorr 那里得到了一些专家意见。

    那么当备份作业运行时会发生什么？不同版本也有显着差异吗？例如 2008、2012 和 2014（不是许可证）。

    我建议您阅读Bob Dorr 的这篇 blog.msdn 文章。他强调的一些重点是

    1. 当备份开始时，它会创建一系列缓冲区，从缓冲池外的内存中分配。每个缓冲区的目标通常是 4MB，导致大约 4 到 8 个缓冲区。有关计算的详细信息位于：http: //support.microsoft.com/kb/904804/en-us

    2. 缓冲区在空闲队列和数据队列之间转换。读取器拉出一个空闲缓冲区，用数据填充它并将其放入数据队列中。writer(s) 从数据队列中提取填充的数据缓冲区，处理缓冲区并将其返回到空闲列表。

    3. 每个备份设备都有一个写入器，每个都从数据队列中检索。因此，具有四 (4) 个磁盘规格的备份命令将具有四个写入器和一个读取器。阅读器使用异步 I/O，因此它可以跟上作者的步伐。

    您可以启用trace flags 3213 and 3605，两者都没有记录，因此请在测试环境中使用它，并查看 SQL Server 错误日志中转储了哪些有趣的消息。会出现类似下面的东西

    Memory limit: 249MB
BufferCount:                7
Sets Of Buffers:            1
MaxTransferSize:            1024 KB
Min MaxTransferSize:        64 KB
Total buffer space:         7 MB
Tabular data device count:  1
Fulltext data device count: 0
Filestream device count:    0
TXF device count:           0
Filesystem i/o alignment:   512
Media Buffer count:            7
Media Buffer size:          1024KB

    我不知道各种版本的备份代码有任何重大变化，这些事情没有记录在案。我只知道SQL Server 2012 SP1 Cumulative Update 2,使用 TSQL 或 SMO 从 SQL Server 的 Windows Azure Blob 存储服务启用备份和还原中引入的增强功能。在这里阅读

    • 8

  3. 基本上，SQL Server 会对磁盘上​​的所有页面进行脏副本。如果存在并发活动或以前存在非检查点活动，则这些页面可能不一致。

    然后，SQL Server 还会复制事务日志的必要部分，以便将过时的页面更新到最新版本并在还原时使所有内容保持一致。

    我无法谈论备份操作的多线程性。我希望它可以并行化。你怎么能在一个 10GB/秒的 IO 子系统上备份一个 10TB 的数据库呢？

    • 5

相关问题