“重新生成索引”任务(维护计划)Rebuild Index Task (Maintenance Plan)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

使用“重新生成索引任务”对话框可用利用新的填充因子对数据库中的表重新创建索引。Use the Rebuild Index Task dialog to re-create the indexes on the tables in the database with a new fill factor. 填充因子确定索引中每页上的空白空间量,以容纳将来的扩展内容。The fill factor determines the amount of empty space on each page in the index, to accommodate future expansion. 随着向表中添加数据,由于没有维持填充因子,可用空间将逐渐填满。As data is added to the table, the free space fills because the fill factor is not maintained. 重新组织数据页和索引页可以重新建立可用空间。Reorganizing data and index pages can re-establish the free space.

“‘重新生成索引’任务” 使用 ALTER INDEX 语句。The Rebuild Index Task uses the ALTER INDEX statement. 有关此页所述选项的详细信息,请参阅 ALTER INDEX (Transact-SQL)For more info about the options described on this page, see ALTER INDEX (Transact-SQL).

选项Options

ConnectionConnection
选择执行此任务时使用的服务器连接。Select the server connection to use when performing this task.

新建New
创建一个新的服务器连接,在执行此任务时使用。Create a new server connection to use when performing this task. 下面对 “新建连接” 对话框进行了介绍。The New Connection dialog box is described below.

数据库Databases
指定受此任务影响的数据库。Specify the databases affected by this task.

  • “所有数据库”All databases

    生成的维护计划将对除 tempdb 之外的所有 SQL ServerSQL Server 数据库运行维护任务。Generate a maintenance plan that runs maintenance tasks against all SQL ServerSQL Server databases except tempdb.

  • 所有系统数据库All system databases

    生成的维护计划将对除 tempdb 之外的所有 SQL ServerSQL Server 系统数据库运行维护任务。Generate a maintenance plan that runs maintenance tasks against each of the SQL ServerSQL Server system databases except tempdb. 对用户创建的数据库不运行维护任务。No maintenance tasks are run against user-created databases.

  • 所有用户数据库All user databases

    生成的维护计划将对用户创建的所有数据库运行维护任务。Generate a maintenance plan that runs maintenance tasks against all user-created databases. 但不会对 SQL ServerSQL Server 系统数据库运行任何维护任务。No maintenance tasks are run against the SQL ServerSQL Server system databases.

  • 特定数据库These specific databases

    生成的维护计划将只对所选数据库运行维护任务。Generate a maintenance plan that runs maintenance tasks against only those databases that are selected. 如果选择此选项,则必须至少在列表中选择一个数据库。At least one database in the list must be selected if this option is chosen.

    备注

    只能对兼容级别被设置为 80 或更高的数据库运行维护计划。Maintenance plans only run against databases set to compatibility level 80 or higher. 不显示兼容级别设置为 70 或更低的数据库。Databases set to compatibility level 70 or lower are not displayed.

ObjectObject
将“选择”网格限制为显示表、显示视图或同时显示两者。Limit the Selection grid to display tables, views, or both.

选择Selection
指定受此任务影响的表或索引。Specify the tables or indexes affected by this task. 在“对象”框中选择 “表和视图” 时不可用。Not available when Tables and Views is selected in the Object box.

每页的默认可用空间Default free space per page
删除数据库中表上的索引,并使用在创建索引时指定的填充因子重新创建索引。Drop the indexes on the tables in the database and re-create them with the fill factor that was specified when the indexes were created.

将每页可用空间更改为Change free space per page to
删除数据库中表上的索引,并使用新的、自动计算的填充因子重新创建索引,从而在索引页上保留指定的可用空间。Drop the indexes on the tables in the database and re-create them with a new, automatically calculated fill factor, thereby reserving the specified amount of free space on the index pages. 百分比越高,索引页上保留的可用空间就越多,并且索引增长也就越大。The higher the percentage, the more free space is reserved on the index pages, and the larger the index grows. 有效值为 0 到 100。Valid values are from 0 through 100.

对 tempdb 中的结果进行排序Sort results in tempdb
使用 SORT_IN_TEMPDB 选项,该选项可确定在索引创建过程中生成的中间排序结果的临时存储位置。Use the SORT_IN_TEMPDB option, which determines where the intermediate sort results, generated during index creation, are temporarily stored. 如果不需要执行排序操作,或者可以在内存中执行排序,则系统会忽略 SORT_IN_TEMPDB选项。If a sort operation is not required, or if the sort can be performed in memory, the SORT_IN_TEMPDBoption is ignored.

填充索引 (Pad index)Pad index
指定索引填充Specify index padding

保持索引联机Keep index online
使用 ONLINE 选项,用户可以在索引操作期间访问基础表或聚集索引数据以及任何关联的非聚集索引。Use the ONLINE option which allows users to access the underlying table or clustered index data and any associated nonclustered indexes during index operations.

备注

MicrosoftMicrosoftSQL ServerSQL Server的各版本中均不提供联机索引操作。Online index operations are not available in every edition of MicrosoftMicrosoftSQL ServerSQL Server. 有关 SQL ServerSQL Server各版本支持的功能列表,请参阅 SQL Server 2016 各个版本支持的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016.

不要重新生成索引 | 重新脱机生成索引Do not rebuild indexes | Rebuild indexes offline
指定要对无法重新联机生成的索引类型执行的操作。Specify what to do for index types that cannot be rebuilt while they are online.

MAXDOPMAXDOP
指定一个值,以限制在并行计划执行过程中使用的处理器数量。Specify a value to limit the number of processors used in a parallel plan execution.

使用低优先级Low Priority Used
选择此选项,以等待低优先级锁。Select this option to wait for low priority locks.

等待后中止Abort after Wait
指定要在 最大持续时间 过后执行的操作。Specify what to do after the time specified by Max Duration has elapsed.

最大持续时间Max Duration
指定低优先级锁的等待时间。Specify how long to wait for low priority locks.

查看 T-SQLView T-SQL
根据所选选项,查看针对此任务的服务器执行的 Transact-SQLTransact-SQL 语句。View the Transact-SQLTransact-SQL statements performed against the server for this task, based on the selected options.

备注

当受影响的对象很多时,可能需要相当长的时间才可显示。When the number of objects affected is large, this display can take a considerable amount of time.

索引统计信息选项Index stats options

在旧版 Microsoft SQL Server 中,可能会导致系统重新调整或重新生成大型索引,进而导致速度变慢。In earlier versions of Microsoft SQL Server it could cause system slowdown to reorganize or rebuild a large index. SQL Server 2016 大幅提升了这些索引操作的性能。SQL Server 2016 implemented major performance improvements for these index operations.

此外,在旧版中,控制级别不太细致。Also, in earlier versions the granularity of control was less refined. 这导致系统重新调整或重新生成一些索引,即使这些索引的碎片百分比不高,也不例外。但这会造成资源浪费。This caused the system to reorganize or rebuild some indexes even when the indexes were not much fragmented, which was wasteful. 通过维护计划用户界面 (UI) 上的新版控件,可以根据索引统计信息条件来排除不需要刷新的索引。Newer controls on the Maintenance Plan user interface (UI) enable you to exclude indexes which do not need to be refreshed, based on index statistics criteria. 为此,可以在内部使用下列 Transact-SQL 动态管理视图 (DMV):For this the following dynamic management views (DMVs) of Transact-SQL are used internally:

扫描类型Scan type
系统必须使用资源才能收集索引统计信息。The system must consume resources to gather index statistics. 可以选择使用相对较少还是较多的资源,具体取决于所需的索引统计信息精度。You can choose between consuming relatively less or more resources depending on how much precision you feel is needed for index statistics. UI 提供了以下精度级别列表,必须从中选择一个:The UI offers the following list of precision levels from which you must choose one:

  • FastFast
  • 抽样Sampled
  • 详细信息Detailed

仅在以下情况下优化索引:Optimize index only if:
UI 提供了以下可调筛选器,可用于避免刷新不是非常需要刷新的索引:The UI offers the following tuneable filters that you can use to avoid refreshing indexes which do not yet strongly need refreshing:

  • 碎片 > (%) Fragmentation > (%)
  • 页计数 >Page Count >
  • 用于过去(天) Used in last (days)

“新建连接”对话框New Connection Dialog Box

连接名称Connection name
输入新连接的名称。Enter a name for the new connection.

选择或输入服务器名称Select or enter a server name
选择执行此任务时所要连接的服务器。Select a server to connect to when performing this task.

“刷新”Refresh
刷新可用服务器的列表。Refresh the list of available servers.

输入登录服务器所需的信息Enter information to log on to the server
指定如何对服务器进行身份验证。Specify how to authenticate against the server.

使用 Windows 集成安全性Use Windows integrated security
使用 Windows 身份验证连接到 SQL Server 数据库引擎SQL Server Database Engine 的实例。Connect to an instance of the SQL Server 数据库引擎SQL Server Database Engine with Windows Authentication.

使用特定用户名和密码Use a specific user name and password
使用 SQL Server 数据库引擎SQL Server Database Engine 身份验证连接到 SQL ServerSQL Server 的实例。Connect to an instance of the SQL Server 数据库引擎SQL Server Database Engine using SQL ServerSQL Server Authentication. 此选项不可用。This option is not available.

用户名User name
提供一个在进行身份验证时要使用的 SQL ServerSQL Server 登录名。Provide a SQL ServerSQL Server login to use when authenticating. 此选项不可用。This option is not available.

密码Password
提供一个在进行身份验证时要使用的密码。Provide a password to use when authenticating. 此选项不可用。This option is not available.

另请参阅See Also

ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
DBCC DBREINDEX (Transact-SQL) DBCC DBREINDEX (Transact-SQL)
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
用于索引的 SORT_IN_TEMPDB 选项 SORT_IN_TEMPDB Option For Indexes
联机索引操作准则 Guidelines for Online Index Operations
联机索引操作的工作方式 How Online Index Operations Work
联机执行索引操作Perform Index Operations Online