SQL Server 2017中的可恢复索引概述

Managing indexes is a critical component of database maintenance but we often don’t think about the indicators behind the index maintenance operations. SQL Server 2017 (CTP 2.0) introduces a very useful index feature, to mitigate the administration overhead of index maintenance which we’ll review and discuss in this article.

管理索引是数据库维护的重要组成部分，但我们通常不考虑索引维护操作背后的指标。 SQL Server 2017（CTP 2.0）引入了一个非常有用的索引功能 ，以减轻索引维护的管理开销，我们将在本文中进行讨论和讨论。

本文概述 (This article outlines)

• Overview of the indicators of disk space issues during heavy index rebuilding maintenance
  重索引重建维护期间磁盘空间问题指标概述
• Index maintenance options available in SQL 2017
  SQL 2017中可用的索引维护选项
• How to use index_resumable_operations
• 如何使用index_resumable_operations
• Application of these settings on SQL partitions
  这些设置在SQL分区上的应用
• A demonstration
  示范
• And more…
  和更多…

背景 (Background)

Being a database administrator, understanding the implication of fragmentation takes precedence over fixing it. Often we don’t think about fragmentation details, and the proceed to rebuild (or defrag) every index in the database. This operation eats up a significant amount of transaction log space, and has a substantial impact on the system resources, backup size, restoration time, etc. Finding the Fill Factor parameter and setting it to a right value works great to deal with fragmentation, but in many cases, finding this value is a tedious job.

作为数据库管理员，了解碎片的含义要优先于解决碎片的问题。 通常，我们不考虑碎片细节，而是着手重建（或整理碎片）数据库中的每个索引。 此操作将占用大量事务日志空间，并且对系统资源，备份大小，恢复时间等产生重大影响。找到“填充因子”参数并将其设置为正确的值非常适合处理碎片，但是在许多情况下，找到这一价值是一件乏味的工作。

Rebuilding indexes requires additional disk space, and an inadequate disk space can negatively impact the I/O, and degrade system performance; sometimes the entire operation may fail.

重建索引需要额外的磁盘空间，而磁盘空间不足可能会对I / O产生负面影响，并降低系统性能。 有时整个操作可能会失败。

The index rebuild operation typically has an option to sort the results in a system database called “TempDB”. The option, SORT_IN_TEMPDB, is set to OFF by default for each index. When using this option, one must ensure that there is enough room to expand TempDB. If not, we must at least make sure that there is enough room on the disk on which the user database resides.

索引重建操作通常具有一个选项，可以对称为“ TempDB”的系统数据库中的结果进行排序。 默认情况下，每个索引的选项SORT_IN_TEMPDB设置为OFF 。 使用此选项时，必须确保有足够的空间来扩展TempDB 。 如果没有，我们至少必须确保用户数据库所在的磁盘上有足够的空间。

If the index rebuild maintenance operation fails due insufficient disk space, the operation has to be started from the beginning. In many instances, administrators struggle to manage the load on the system resources, and wish to pause and resume the operation at a later point.

如果索引重建维护操作由于磁盘空间不足而失败，则必须从头开始操作。 在许多情况下，管理员很难管理系统资源上的负载，并希望在以后暂停并恢复操作。

介绍 (Introduction)

On SQL Server 2007 CTP 2.0 there is an Alter Index feature to pause and resume indexes during maintenance, which offers several ways to maintain the indexes at a very granular level. Previous versions of SQL Server gave a different dimension to index maintenance operations, but this feature has something noteworthy.

在SQL Server 2007 CTP 2.0上，具有“ 更改索引”功能可以在维护期间暂停和恢复索引，该功能提供了几种在非常精细的级别上维护索引的方法。 早期版本SQL Server为索引维护操作提供了不同的维度，但是此功能值得注意。

This feature is very useful for database administrators who perform periodic index maintenances at regular intervals of time. This option provides us with the flexibility to pause, start, and abort the index rebuild operations. Of course, there is overhead on storage, but, in general, this is a pretty cool feature.

对于定期执行索引维护的数据库管理员而言，此功能非常有用。 该选项为我们提供了暂停，启动和中止索引重建操作的灵活性。 当然，存储上会有开销，但是，通常，这是一个非常酷的功能。

Here is an outline of the SQL 2017 Resumable Index Rebuild features and other considerations.

这是SQL 2017可恢复索引重建功能和其他注意事项的概述。

• Better planning and a greater flexibility in the management of indexes during maintenance windows. Provides an option to pause and restart an index rebuild operation multiple times.
  在维护窗口期间，更好的计划和更大的索引管理灵活性。 提供一个选项来暂停和重新启动索引重建操作多次。
• Flexibility to recover from index rebuild failures, since the process does not necessarily have to start from the beginning.
  从索引重建失败中恢复的灵活性，因为该过程不一定必须从头开始。
• Space can be a game-changer (storage constraint), when an index operation is paused—both, the original index and the newly created one require disk space.
  当索引操作暂停时，空间可以改变游戏规则（存储约束），原始索引和新创建的索引都需要磁盘空间。
• Better log space management since it enables truncation of transaction logs during an index rebuild operation.
  更好的日志空间管理，因为它可以在索引重建操作期间截断事务日志。
• SORT_IN_TEMPDB=ON is not supported.
• 不支持SORT_IN_TEMPDB = ON 。

句法 (Syntax)

 
-- Syntax for SQL Server and SQL Database
 
ALTER INDEX { index_name | ALL } ON <object>  
{  
      REBUILD {  
            [ PARTITION = ALL ] [ WITH ( <rebuild_index_option> [ ,...n ] ) ]   
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) [ ,...n ] ]  
      }  
    | RESUME [WITH (<resumable_index_options>,[…n])]
    | PAUSE
    | ABORT
}  
[ ; ] 
<rebuild_index_option > ::=  
{  
    | ONLINE = {   
       ON [ ( <low_priority_lock_wait> ) ]   
     | OFF } 
    | RESUMABLE = { ON | OFF } 
    | MAX_DURATION = <time> [MINUTES}     
 }  
 
<resumable_index_option> ::=
 { 
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION =<time> [MINUTES]
    | <low_priority_lock_wait>  
 }
 

局限性 (Limitations)

• The SORT_IN_TEMPDB=ON option is not supported
• 不支持SORT_IN_TEMPDB = ON选项
• Resumable index operations are not supported on disabled index
  禁用的索引不支持可恢复的索引操作
• Columnstore index is not supported
  不支持列存储索引

示范 (Demonstration)

I’ve chosen a compressed data warehouse fact table named ftb_Material_Issue for the entire demo. The stats and metrics have been gathered at regular intervals, throughout the index rebuilding operation with index resumable option.

我为整个演示选择了一个名为ftb_Material_Issue的压缩数据仓库事实表。 在具有索引可恢复选项的整个索引重建操作中，定期收集统计信息和指标。

Execute an online index rebuild as a resumable operation with MAXDOP=1.

使用MAXDOP = 1执行在线索引重建作为可恢复的操作。

 
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue REBUILD WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON) ;
 

The second run with the same options would reveal that there is already a process with the same options running; we can resume it from where it stopped.

使用相同选项的第二次运行将显示已经有一个运行相同选项的流程。 我们可以从停止的地方恢复它。

 
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue REBUILD WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON) ;
 

After running it for a few seconds, stop or cancel the execution, and check for the statistics using sys.index_resumable_operations.

运行几秒钟后，停止或取消执行，然后使用sys.index_resumable_operations检查统计信息。

The sys.index_resumable_operations is a system view that monitors and checks the current execution status for resumable index rebuild. Let’s now look into the view to understand the state of the operation. This view gives us details of the progress of the entire operation, such as the total number of index pages allocated by the operation. The following query shows our operation that was paused.

sys.index_resumable_operations是一个系统视图，用于监视和检查当前执行状态以恢复可恢复的索引。 现在，让我们进入视图以了解操作状态。 该视图为我们提供了整个操作进度的详细信息，例如该操作分配的索引页总数。 以下查询显示了我们暂停的操作。

 
SELECT 
	name ObjectName,
	index_ID,
	sql_text,
	last_max_dop_used,
	partition_number,
	state,
	state_desc,
	start_time,
	last_pause_time,
	total_execution_time,
	percent_complete,
	page_count
 
 FROM  
	sys.index_resumable_operations WHERE STATE = 1;
 

sys.index_resumable_operations列详细信息 (The sys.index_resumable_operations column details)

Column name	Data type	Description
object_id	int	Every object has an ID, called the object identifier.
index_id	int	This is the unique index identifier; this field is not nullable either; index_id is unique only within the object.
name	sysname	This is the unique name of the index.
sql_text	nvarchar(max)	This is the DDL T-SQL statement text
last_max_dop	smallint	Self-explanatory, this is the last MAX_DOP used (default = 0)
partition_number	int	This is the partition number. For non-partitioned objects, or in case all partitions are being rebuilt, the value of this column is NULL.
state	tinyint	Boolean-like operational state of the resumable index: 0=Running 1=Paused
state_desc	nvarchar(60)	Description of the state of the index (Running or Paused)
start_time	datetime	Self-explanatory, the start time of the index operation.
last_pause_time	datatime	The last pause time of the index operation. If the operation is running and was never paused, the value would be NULL.
total_execution_time	int	Total run time from start time (in minutes)
percent_complete	real	The progress of the index operation (in %).
page_count	bigint	The total number of index pages allocated by the index build operation.

栏名	数据类型	描述
object_id	整型	每个对象都有一个ID，称为对象标识符。
index_id	整型	这是唯一的索引标识符； 此字段也不可为空； index_id仅在对象内是唯一的。
名称	系统名称	这是索引的唯一名称。
sql_text	nvarchar（最大）	这是DDL T-SQL语句文本
last_max_dop	Smallint	不言自明，这是最后使用的MAX_DOP （默认= 0）
partition_number	整型	这是分区号。 对于未分区的对象，或者在所有分区都在重建的情况下，此列的值为NULL 。
州	tinyint	可恢复索引的类似于布尔的操作状态： 0 =正在运行 1 =已暂停
state_desc	nvarchar（60）	索引状态的描述（正在运行或已暂停）
开始时间	约会时间	不言自明，索引操作的开始时间。
last_pause_time	数据时间	索引操作的最后暂停时间。 如果操作正在运行并且从未暂停过，则该值为NULL 。
total_execution_time	整型	从开始时间开始的总运行时间（以分钟为单位）
percent_complete	真实	索引操作的进度（以％为单位）。
page_count	比金特	索引构建操作分配的索引页总数。

Let’s resume the rebuild operations after 10 minutes and gather the stats.

让我们在10分钟后恢复重建操作并收集统计信息。

I’ve canceled the query execution and status of the index rebuilding operation of clustered index is at 52% complete, and the database size has increased to 2200 MB.

我取消了查询执行，并且聚集索引的索引重建操作的状态已完成52％，并且数据库大小已增加到2200 MB。

 
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue RESUME  WITH (MAXDOP=2)
 

The database growth is shown below. It is apparent that the there is a small chunk of database expansion. This is due to the temporary storage of the current state of the index and other metadata.

数据库的增长如下所示。 显然，数据库扩展很小。 这是由于临时存储了索引和其他元数据的当前状态。

Let’s now cancel the query execution and resume the process with MAXDOP 4 with MAX_DURATION of 10 minutes.

现在，让我们取消查询执行，并使用MAXDOP 4 （ MAX_DURATION为10分钟）恢复该过程。

 
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue RESUME  WITH (MAXDOP=4, MAX_DURATION=10)
 

Let’s pause/resume and abort a running resumable online index rebuild

让我们暂停/恢复并中止正在运行的可恢复在线索引重建

 
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue PAUSE
 

 
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue RESUME
 

 
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue ABORT
 

Although online index operations permit concurrent user DML activity, the index operations will take longer if the update activity is heavy. During any online index rebuilding process, the system maintains both, the source and target structures, so the space is going to be a major constraint.

尽管在线索引操作允许并发用户DML活动，但是如果更新活动繁重，则索引操作将花费更长的时间。 在任何在线索引重建过程中，系统都会维护源结构和目标结构，因此空间将成为主要限制。

管理封锁 (Manage blocking )

The periodic locking and blocking can be handled effectively by setting WAIT_AT_LOW_PRIORITY.Let’s resume the online index rebuild by setting MAXDOP to 2, set the execution time for the index being running as resmable to 120 minutes, and in case of an index being blocked on the lock, wait 10 minutes, and after that, kill all blockers.

可以通过设置WAIT_AT_LOW_PRIORITY来有效地处理定期锁定和阻塞。让我们通过将MAXDOP设置为2来恢复联机索引重建，将正在运行的索引的执行时间设置为可重新调整为120分钟，并且如果在锁定，等待10分钟，然后杀死所有阻止者。

 
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue RESUME WITH (MAXDOP=2, MAX_DURATION= 120 MINUTES, WAIT_AT_LOW_PRIORITY (MAX_DURATION=10, ABORT_AFTER_WAIT=BLOCKERS));
 

关于数据压缩和SQL分区的可恢复索引 (Resumable Index on Data-compression and SQL Paritition)

Having tested the resumable option with data compression and SQL partition; the performance is no different than the normal operation.

通过数据压缩和SQL分区测试了可恢复选项； 性能与正常操作无异。

The following SQL gives an idea of how to integrate the resumable option with SQL partitions and data-compression. The performance is acceptable and the process is really seamless. Let’s compare the time that each of these queries took.

以下SQL提供了有关如何将可恢复选项与SQL分区和数据压缩集成的想法。 性能是可以接受的，并且过程是无缝的。 让我们比较一下每个查询花费的时间。

Query	Partition	Compression	MAXDOP	Resumable	Time taken
ALTER INDEX Index_WOCD_INCLUDE ON [dbo].ftb_WOSummary_Daily REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = NONE, ONLINE=ON, MAXDOP=1, RESUMABLE=ON )	All	None	1	On	9 sec
ALTER INDEX Index_WOCD_INCLUDE ON [dbo].ftb_WOSummary_Daily REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = NONE, ONLINE=ON, MAXDOP=1, RESUMABLE=OFF )	All	None	1	Off	9 sec
ALTER INDEX Index_WOCD_INCLUDE ON [dbo].ftb_WOSummary_Daily REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = PAGE, ONLINE=ON, MAXDOP=1, RESUMABLE=ON )	All	Page	1	On	17 sec
ALTER INDEX Index_WOCD_INCLUDE ON [dbo].ftb_WOSummary_Daily REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = PAGE, ONLINE=ON, MAXDOP=1, RESUMABLE=OFF )	All	Page	1	Off	18 sec
ALTER INDEX Index_WOCD_INCLUDE ON [dbo].ftb_WOSummary_Daily REBUILD PARTITION = 3 WITH ( DATA_COMPRESSION = NONE ON PARTITIONS (1), DATA_COMPRESSION = PAGE ON PARTITIONS (2,3), ONLINE=ON, MAXDOP=1, RESUMABLE=ON)	1, 2, 3	None, Page, Page	1	On	9 sec
ALTER INDEX Index_WOCD_INCLUDE ON [dbo].ftb_WOSummary_Daily REBUILD PARTITION = 3 WITH ( DATA_COMPRESSION = NONE ON PARTITIONS (1), DATA_COMPRESSION = PAGE ON PARTITIONS (2,3), ONLINE=ON, MAXDOP=1, RESUMABLE=OFF)	1, 2, 3	None, Page, Page	1	Off	9 sec

询问	划分	压缩	MAXDOP	可恢复	所用的时间
ALTER INDEX Index_WOCD_INCLUDE 开启 [dbo] .ftb_WOSummary_Daily 重建分区 = ALL WITH（ DATA_COMPRESSION = NONE ， ONLINE = ON ， MAXDOP = 1 ， RESUMABLE = ON ）	所有	没有	1个	上	9秒
ALTER INDEX Index_WOCD_INCLUDE 开启 [dbo] .ftb_WOSummary_Daily 重建分区 = ALL WITH（ DATA_COMPRESSION = NONE ， ONLINE = ON ， MAXDOP = 1 ， RESUMABLE = OFF ）	所有	没有	1个	关	9秒
ALTER INDEX Index_WOCD_INCLUDE 开启 [dbo] .ftb_WOSummary_Daily 重建分区 = ALL WITH（ DATA_COMPRESSION = PAGE ， ONLINE = ON ， MAXDOP = 1 ， RESUMABLE = ON ）	所有	页	1个	上	17秒
ALTER INDEX Index_WOCD_INCLUDE 开启 [dbo] .ftb_WOSummary_Daily 重建分区 = ALL WITH（ DATA_COMPRESSION = PAGE ， ONLINE = ON ， MAXDOP = 1 ， RESUMABLE = OFF ）	所有	页	1个	关	18秒
ALTER INDEX Index_WOCD_INCLUDE 开启 [dbo] .ftb_WOSummary_Daily 重建分区 = 3， 带有 （ DATA_COMPRESSION = 分区 无 （ 1 ）， DATA_COMPRESSION = PAGE在 分区 （2，3），ONLINE = ON，MAXDOP = 1，RESUMABLE = ON）	一二三	无，页，页	1个	上	9秒
ALTER INDEX Index_WOCD_INCLUDE 开启 [dbo] .ftb_WOSummary_Daily 重建分区 = 3， 带有 （ DATA_COMPRESSION = 分区 无 （ 1 ）， DATA_COMPRESSION = PAGE在 分区 （2，3），ONLINE = ON，MAXDOP = 1，RESUMABLE = OFF）	一二三	无，页，页	1个	关	9秒

Stop the query execution and measure the progress using sys.index_resumable_operations.

停止查询执行，并使用sys.index_resumable_operations测量进度。

To resume the index rebuilding process, use

要恢复索引重建过程，请使用

 
ALTER INDEX Index_WOCD_INCLUDE on ftb_WOSummary_Daily RESUME
 

摘要 (Summary)

This article discusses all the features of indexing using the resumable option with SQL Server 2017 including all the combinations of partitioning, data compression, MAXDOP, priority management, blocking as well as the resumable option, and its different states.

本文讨论了在SQL Server 2017中使用可恢复选项建立索引的所有功能，包括分区，数据压缩，MAXDOP，优先级管理，阻止以及可恢复选项及其不同状态的所有组合。

Effective t-log management and flexibility to handle indexes at a very granular level eases the huge burden on the database administrator, for the index maintenance operation. Although the online operation is recommended, it’s important that the underlying infrastructure is evaluated, and the implications are understood before proceeding.

有效的t-log管理和在非常细粒度的级别上处理索引的灵活性减轻了数据库管理员在索引维护操作方面的巨大负担。 尽管建议使用在线操作，但重要的是评估基础基础结构，并在继续操作之前了解其含义。

The impact of using the resumable option on performance is no different than using the normal command. This is tested with a compressed table and partitioned table. As a recommended practice, you should run the index operations offline, or at least during off-business hours, in order to ensure minimal impact to daily operations.

使用可恢复选项对性能的影响与使用普通命令没有什么不同。 使用压缩表和分区表对此进行了测试。 作为推荐的做法，您应该脱机运行索引操作，或者至少在下班时间运行索引操作，以确保对日常操作的影响最小。

目录 (Table of contents)

A Quick start Guide to Managing SQL Server 2017 on CentOS/RHEL Using the SSH Protocol

How to use Python in SQL Server 2017 to obtain advanced data analytics

Data Interpolation and Transformation using Python in SQL Server 2017

An introduction to a SQL Server 2017 graph database

Top string functions in SQL Server 2017

Top 8 new (or enhanced) SQL Server 2017 DMVs and DMFs for DBAs

Overview of Resumable Indexes in SQL Server 2017

Understanding automatic tuning in SQL Server 2017

使用SSH协议在CentOS / RHEL上管理SQL Server 2017的快速入门指南

如何在SQL Server 2017中使用Python获得高级数据分析

在SQL Server 2017中使用Python进行数据插值和转换

SQL Server 2017图形数据库简介

SQL Server 2017中的顶级字符串函数

适用于DBA的前8大新（或增强）SQL Server 2017 DMV和DMF

SQL Server 2017中的可恢复索引概述

了解SQL Server 2017中的自动调整

翻译自: https://www.sqlshack.com/overview-resumable-indexes-sql-server-2017/