MS SQL Server备份优化

最新推荐文章于 2021-05-02 03:38:33 发布

culuo4781

最新推荐文章于 2021-05-02 03:38:33 发布

阅读量176

点赞数

文章标签：数据库 java linux python mysql

原文链接：https://www.sqlshack.com/ms-sql-server-backup-optimization/

版权

Backup and recovery are some of the most important DBA tasks, although they look simple enough and usually you setup them, leave them running on schedule and only come back if they fail – there is a whole new world in regards to optimization you can do to make them faster, better and … smaller.

备份和恢复是一些最重要的DBA任务，尽管它们看起来很简单，通常您可以设置它们，让它们按计划运行，直到它们失败后才返回-关于优化，您可以做一个全新的世界使它们更快，更好，……更小。

But why bother? Considering that the modern databases grow at such fast pace you may face a situation where you are not able to fit in your maintenance windows or service contract obligations. Let us take a look on the three main areas where we can work on:

但是为什么要打扰呢？考虑到现代数据库的增长速度如此之快，您可能会遇到无法满足维护窗口或服务合同义务的情况。让我们看一下我们可以从事的三个主要领域：

Part one, optimize the database for faster backup and recovery operations, part two, optimize the exact process of database backup and part three, optimize the backup itself.

第一部分， 优化数据库以加快备份和恢复操作，第二部分，优化数据库备份的确切过程 ，第三部分， 优化备份本身。

Before diving deep, let us take a look on the lab environment we will be working today with – We are having couple of azure machines running on MS SQL Server 2014 SP1 running on Windows 2012 R2. The databases data and log files are spread among different disks, the tempdb is having its own disks. In addition we are having a dedicated backup volume as well.

在深入探讨之前，让我们看一下今天将要使用的实验室环境–我们有几台运行于Windows 2012 R2的MS SQL Server 2014 SP1上运行的Azure计算机。数据库数据和日志文件分布在不同的磁盘之间，tempdb具有自己的磁盘。此外，我们还有专用的备份卷。

TIER	CPU CORES	MEMORY	MAX. DATA DISK	MAX. IOPS (300 PER DISK)
A3	4 cores	7 GB	8	8×500

层级	中央处理器核心	记忆	最高数据盘	最高 IOPS （每碟300个）
A3	4芯	7 GB	8	8×500

The database that we will be using for tests is an AdventureWorks2008R2 enlarged* up to 17GB (only).

我们将用于测试的数据库是AdventureWorks2008R2扩大版*，最大可扩展到17GB（仅）。

第一部分，优化数据库 (Part one, Optimize the database)

At the moment we are having one database file, one log file and one backup file. Standard backup operations are happening in just over 36 minutes, backing up 1823434 pages in a 14GB backup file.

目前，我们有一个数据库文件，一个日志文件和一个备份文件。标准备份操作仅需36分钟即可完成，并在14GB的备份文件中备份1823434页。

 
BACKUP DATABASE [AdventureWorks2008R2] 
TO DISK = N'N:\AdventureWorks2008R2.bak'
WITH NOFORMAT, NOINIT
GO
Processed 1823456 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data' on file 1.
Processed 2 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Log' on file 1.
BACKUP DATABASE successfully processed 1823458 pages in 2159.616 seconds (6.596 MB/sec).

To optimize the database it is important for you to be familiar with the objects in it. With AdventureWorks database I will review the two biggest objects Sales.SalesOrderDetailEnlarged and Sales.SalesOrderHeaderEnlarged and sample the possible size reductions if I apply row or page compression for them.

要优化数据库，熟悉其中的对象很重要。使用AdventureWorks数据库，我将检查两个最大的对象Sales.SalesOrderDetailEnlarged和Sales.SalesOrderHeaderEnlarged并进行抽样（如果我对它们应用行压缩或页面压缩）。

Sample the possible savings using the stored procedure “sp_estimate_data_compression_savings”:

使用存储过程“ sp_estimate_data_compression_savings”对可能的节省进行采样：

Object name	IndexID	Type	Current Size(KB)	Estimated(KB)	Sample Current Size(KB)	Estimated Comp Sample(KB)
SalesOrderDetailEnlarged	1	ROW	6445472	4641120	27120	19528
SalesOrderDetailEnlarged	2	ROW	2158240	2464576	9976	11392
SalesOrderHeaderEnlarged	1	ROW	2539304	1764888	39584	27512
SalesOrderDetailEnlarged	1	PAGE	6445472	3312688	26368	13552
SalesOrderDetailEnlarged	2	PAGE	2158240	2459472	9744	11104
SalesOrderHeaderEnlarged	1	PAGE	2539304	1228808	38552	18656

对象名称	索引ID	类型	当前大小（KB）	估计的（KB）	样本当前大小（KB）	估计比较样本（KB）
SalesOrderDetail放大	1个	行	6445472	4641120	27120	19528
SalesOrderDetail放大	2	行	2158240	2464576	9976	11392
SalesOrderHeaderEnlarged	1个	行	2539304	1764888	39584	27512
SalesOrderDetail放大	1个	页	6445472	3312688	26368	13552
SalesOrderDetail放大	2	页	2158240	2459472	9744	11104
SalesOrderHeaderEnlarged	1个	页	2539304	1228808	38552	18656

The sample results represent a portion of the data within the object compressed by the SQL Server – be aware that the final compression may have different ratios.

样本结果代表由SQL Server压缩的对象内数据的一部分–请注意，最终压缩可能具有不同的比率。

Judging by the results we will have best results using PAGE compression. You can apply the compression per index or on the whole table as follows:

从结果来看，使用PAGE压缩将获得最佳结果。您可以按索引或对整个表应用压缩，如下所示：

 
USE [AdventureWorks2008R2]
GO
ALTER TABLE Sales.SalesOrderDetailEnlarged REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE); 
GO
ALTER TABLE Sales.SalesOrderHeaderEnlarged REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE); 
GO

The results from the compression:

压缩的结果：

Table	Original(KB)	Compressed(KB)
SalesOrderDetailEnlarged	6425624	2116376
SalesOrderHeaderEnlarged	2529792	1149608

表	原始文件（KB）	压缩后（KB）
SalesOrderDetail放大	6425624	2116376
SalesOrderHeaderEnlarged	2529792	1149608

Comparing the duration of the backup operation:

比较备份操作的持续时间：

 
BACKUP DATABASE [AdventureWorks2008R2] 
TO DISK = N'N:\AdventureWorks2008R2.bak'
WITH NOFORMAT, NOINIT
GO
Processed 1110760 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data' on file 1.
Processed 2 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Log' on file 1.
BACKUP DATABASE successfully processed 1110762 pages in 1653.560 seconds (5.247 MB/sec).

Processed 1110760 pages for database ‘AdventureWorks2008R2’, file ‘AdventureWorks2008R2_Data’ on file 1.
Processed 2 pages for database ‘AdventureWorks2008R2’, file ‘AdventureWorks2008R2_Log’ on file 1.
BACKUP DATABASE successfully processed 1110762 pages in 1653.560 seconds (5.247 MB/sec).

已处理数据库'AdventureWorks2008R2'的1110760页，文件1中的文件'AdventureWorks2008R2_Data'。
处理了2页的数据库'AdventureWorks2008R2'，文件1中的文件'AdventureWorks2008R2_Log'。
BACKUP DATABASE在1653.560秒（5.247 MB /秒）中成功处理了1110762页。

The backup operation completed in 27 minutes, 8 minutes faster and the backup files is 5.5GB smaller.

备份操作在27分钟内完成，快了8分钟，备份文件小了5.5GB。

We can move even further and split the database in two parts, this will help us by doubling the read operations for the backup process. To achieve this we will create a new database file within a new filegroup named SECONDARY, after that we will move roughly 50% of the database objects in terms of size there.

我们可以更进一步，将数据库分为两部分，这将使备份过程的读取操作加倍，从而对我们有帮助。为此，我们将在名为SECONDARY的新文件组中创建一个新的数据库文件，此后，根据大小，我们将移动大约50％的数据库对象。

Creating the new FILEGROUP:

创建新的FILEGROUP：

 
USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] ADD FILEGROUP [SECONDARY]
GO

Creating a new file J:\AdventureWorks2008R2_Data2.ndf in the filegroup SECONDARY.

在SECONDARY文件组中创建一个新文件J：\ AdventureWorks2008R2_Data2.ndf。

 
USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] MODIFY FILE (NAME = N'AdventureWorks2008R2_Data', SIZE = 16977920KB)
GO
ALTER DATABASE [AdventureWorks2008R2] ADD FILE (NAME = N'AdventureWorks2008R2_Data2', FILENAME = N'J:\AdventureWorks2008R2_Data2.ndf, SIZE = 16977920KB, FILEGROWTH = 1048576KB) 
TO FILEGROUP [SECONDARY]
GO

Rebuilding the Clustered index of the biggest table with specifying the exact filegroup in order to move it to the new file:

通过指定确切的文件组来重建最大表的聚集索引，以便将其移动到新文件：

 
USE [AdventureWorks2008R2]
GO
--- Dropping the clustered index
ALTER TABLE [Sales].[SalesOrderDetailEnlarged] DROP CONSTRAINT [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]
GO
-- Creating the cluster index again
ALTER TABLE [Sales].[SalesOrderDetailEnlarged] ADD  CONSTRAINT [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED 
(	[SalesOrderID] ASC,
	[SalesOrderDetailID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [SECONDARY] --- This one is important
GO

The backup operation is reading from two data files as you can see from the outcome:

从结果中可以看到，备份操作正在读取两个数据文件：

 
BACKUP DATABASE [AdventureWorks2008R2] 
TO DISK = N'N:\AdventureWorks2008R2.bak'
WITH NOFORMAT, NOINIT
GO
Processed 704048 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data' on file 1.
Processed 267384 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data2' on file 1.
Processed 2 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Log' on file 1.
BACKUP DATABASE successfully processed 971434 pages in 1078.168 seconds (7.039 MB/sec).

Benefiting from the two streams we have managed to save 10 more minutes from the duration of the backup operation.

从这两个流中受益，我们已经成功地从备份操作的持续时间节省了10分钟。

You can consider the SQL trace flag –T1117** when using multiple data files – it will cause all the files in a filegroup to auto grow together by their specified auto grow increment. Note that this affects all databases on the server you enable it.

您可以在使用多个数据文件时考虑使用SQL跟踪标志–T1117 ** –这将导致文件组中的所有文件按照指定的自动增长增量一起自动增长。请注意，这会影响您启用它的服务器上的所有数据库。

第二部分，优化备份过程 (Part two, Optimizing the backup process)

Now that we are having two data files there are two read IO steams reading the data during the backup process but a single one writing the data into the backup container. To further improve the process we can back up the database in two backup files. The data will be split among the two containers and you will need both of them during recovery operations.

现在我们有了两个数据文件，在备份过程中有两个读取IO数据流读取数据，但是只有一个读取IO流将数据写入备份容器。为了进一步改进该过程，我们可以在两个备份文件中备份数据库。数据将在两个容器之间分配，并且在恢复操作期间将需要两个容器。

 
BACKUP DATABASE [AdventureWorks2008R2]
TO DISK = N'N:\AdventureWorks2008R2P1.bak',
DISK = N'N:\AdventureWorks2008R2P2.bak'
WITH NOFORMAT, INIT
GO
Processed 704048 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data' on file 1.
Processed 267384 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data2' on file 1.
Processed 2 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Log' on file 1.
BACKUP DATABASE successfully processed 971434 pages in 712.489 seconds (10.651 MB/sec).

Using two write streams saved us some more time, but be cautious as splitting the backup containers into multiple files complicates the recovery operations a bit.

使用两个写入流为我们节省了更多时间，但请谨慎操作，因为将备份容器拆分为多个文件会使恢复操作有些复杂。

Now let’s tweak some of the parameters that are available to the BACKUP command – MAXTRANSFERSIZE and BUFFERCOUNT.

现在，我们调整一些可用于BACKUP命令的参数-MAXTRANSFERSIZE和BUFFERCOUNT。

MAXTRANSFERSIZE specifies the unit of transfer used by the SQL Server to perform the backups. The default value is 1024MB – the possible values are multiples of 65536 bytes (64KB) ranging up to 4MB.

MAXTRANSFERSIZE指定SQL Server执行备份所使用的传输单位。默认值为1024MB –可能的值为65536字节（64KB）的倍数，最大为4MB。

BUFFERCOUNT determines the number of IO buffers used by the backup operations. The values for it are dynamically calculated by the MSSQL Server, however they are not always optimal. However be cautious as very high values may lead to ‘out of memory’ errors.

BUFFERCOUNT确定备份操作使用的IO缓冲区数。它的值由MSSQL Server动态计算，但是并不总是最佳的。但是，请谨慎操作，因为非常高的值可能会导致“内存不足”错误。

The two parameters do work together – BUFFERCOUNT determines how many IO buffers you will be working with and MAXTRANSFERSIZE sets how full this buffers will be. IO buffers = BUFFERCOUNT * MAXTRANSFERSIZE

这两个参数可以一起工作-BUFFERCOUNT确定您将使用的IO缓冲区数，MAXTRANSFERSIZE设置此缓冲区的满度。 IO缓冲区= BUFFERCOUNT * MAXTRANSFERSIZE

You can enable two trace flags to see additional information about your backups and to see the calculated BUFFERCOUNT by the SQL server:
3605 – That send the output to errorlog; and 3213 – Which provide information about backup or restore throughput and other configurations.

您可以启用两个跟踪标志以查看有关备份的其他信息，并查看由SQL Server计算出的BUFFERCOUNT：
3605 –将输出发送到错误日志；和3213 –提供有关备份或还原吞吐量以及其他配置的信息。

To tweak the MAXTRANSFERSIZE we can do the following:

要调整MAXTRANSFERSIZE，我们可以执行以下操作：

 
BACKUP DATABASE [AdventureWorks2008R2]
TO DISK = N'N:\AdventureWorks2008R2P1.bak',
DISK = N'N:\AdventureWorks2008R2P2.bak'
WITH NOFORMAT, INIT, MAXTRANSFERSIZE = 2097152
Processed 704048 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data' on file 1.
Processed 267384 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data2' on file 1.
Processed 2 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Log' on file 1.
BACKUP DATABASE successfully processed 971434 pages in 495.826 seconds (15.306 MB/sec).

And these are the average results using every possible value:

这些是使用所有可能值的平均结果：

The default value SQL server calculated for my backup was 14, I found that the optimal is close to XYZ. In order to tweak the BUFFERCOUNT we can do the following:

为备份计算SQL Server的默认值为14，我发现最佳值接近XYZ。为了调整BUFFERCOUNT，我们可以执行以下操作：

 
BACKUP DATABASE [AdventureWorks2008R2]
TO DISK = N'N:\AdventureWorks2008R2P1.bak',
DISK = N'N:\AdventureWorks2008R2P2.bak'
WITH NOFORMAT, INIT, MAXTRANSFERSIZE = 2097152, BUFFERCOUNT = 50
Processed 704048 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data' on file 1.
Processed 267384 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data2' on file 1.
Processed 2 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Log' on file 1.
BACKUP DATABASE successfully processed 971434 pages in 351.935 seconds (21.564 MB/sec).

And again this are the results using different values.

同样，这是使用不同值的结果。

第三部分，优化备份 (Part three, Optimize the backup)

There are two possibilities for us here – we can increase the backup speed a little bit more, and reduce the size of the backup.

我们这里有两种可能性–我们可以稍微提高备份速度，并减小备份的大小。

The last parameter for the BACKUP command is BLOCKSIZE. It specifies the physical block size, in bytes. The supported sizes are from 512 bytes to 65536 (64 KB) bytes. The default for backup containers on disk drivers is 512 bytes and 64KB for tape devices. The command is as follows:

BACKUP命令的最后一个参数是BLOCKSIZE。它指定物理块大小（以字节为单位）。支持的大小从512字节到65536（64 KB）字节。磁盘驱动程序上的备份容器的默认值为512字节，而磁带设备的默认值为64KB。命令如下：

 
BACKUP DATABASE [AdventureWorks2008R2]
TO DISK = N'N:\AdventureWorks2008R2P1.bak',
DISK = N'N:\AdventureWorks2008R2P2.bak'
WITH NOFORMAT, INIT, MAXTRANSFERSIZE = 2097152
, BUFFERCOUNT = 50, BLOCKSIZE = 8192
Processed 704048 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data' on file 1.
Processed 267384 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data2' on file 1.
Processed 2 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Log' on file 1.
BACKUP DATABASE successfully processed 971434 pages in 390.318 seconds (19.443 MB/sec).

Below are the tests from using all possible values;

以下是使用所有可能值的测试；

To get the last possible increase in terms of speed and size we will be using backup compression. It is now available in Standard edition as well from SQL 2008R2. It uses a zip type compression and the size of the backup is reduced during its creation – meaning that in most scenarios the backup will be not only smaller but faster (considering that you are not storing only jpegs and you use encryption). This is the easiest and most transparent way to optimize the backup operations. We can create a SQL compressed backup as follows:

为了获得速度和大小上的最后一次增加，我们将使用备份压缩。现在，它也可以从SQL 2008R2中获得标准版。它使用zip类型的压缩，并且在创建备份时减小了备份的大小–这意味着在大多数情况下，备份不仅会更小而且会更快（考虑到您不只存储jpeg并且使用了加密）。这是优化备份操作的最简单，最透明的方法。我们可以创建一个SQL压缩备份，如下所示：

 
BACKUP DATABASE [AdventureWorks2008R2]
TO DISK = N'N:\AdventureWorks2008R2P1.bak',
DISK = N'N:\AdventureWorks2008R2P2.bak'
WITH NOFORMAT, INIT, MAXTRANSFERSIZE = 2097152
, BUFFERCOUNT = 50, BLOCKSIZE = 8192, COMPRESSION
Processed 704048 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data' on file 1.
Processed 267384 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data2' on file 1.
Processed 2 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Log' on file 1.
BACKUP DATABASE successfully processed 971434 pages in 230.368 seconds (32.944 MB/sec).

You can also alter the Server wide settings so all backup are created compressed – be sure to check if all your SQL servers support compressed backups.

您还可以更改服务器范围的设置，以便所有备份都以压缩方式创建-请确保检查所有SQL Server是否都支持压缩备份。

The backup operation completed at ~230 seconds, ~90% faster than the original we started with; the size is 2.5GB, 83% smaller than the original. And you are guessing it right – the recovery operations will be faster as well.

备份操作完成了约230秒，比我们开始时的原始速度快了约90％；大小为2.5GB，比原始大小小83％。您猜对了，恢复操作也将更快。

参考资料 (References)

翻译自: https://www.sqlshack.com/ms-sql-server-backup-optimization/

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MS SQL Server备份优化

Backup and recovery are some of the most important DBA tasks, although they look simple enough and usually you setup them, leave them running on schedule and only come back if they fail – there is...
复制链接

扫一扫