我手头的任务是移动重建一个大表以将 LOB 页面移动到 SQL Server 2017 企业版上的不同文件组。
我在概念验证环境中测试脚本,我可以看到总共CREATE INDEX .. DROP_EXISTING=ON
需要大约 6 个小时。
CREATE UNIQUE CLUSTERED INDEX [PK_TABLE1]
ON [dbo].[TABLE1] ([Id] ASC)
WITH (DROP_EXISTING = ON , FILLFACTOR = 100, PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, DATA_COMPRESSION = NONE, STATISTICS_NORECOMPUTE = OFF, ONLINE = ON, MAXDOP=2)
ON PS_MOVE_HELPER_D59E24BC73414AA8A5FB2E5D8F93C3D8([Id] );
CREATE UNIQUE CLUSTERED INDEX [PK_TABLE1]
ON [dbo].[TABLE1] ([Id] ASC)
WITH (DROP_EXISTING = ON , FILLFACTOR = 100, PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, DATA_COMPRESSION = NONE, STATISTICS_NORECOMPUTE = OFF, ONLINE = ON, MAXDOP=2)
ON [LOB_DATA];
CREATE UNIQUE CLUSTERED INDEX [PK_TABLE1]
ON [dbo].[TABLE1] ([Id] ASC)
WITH (DROP_EXISTING = ON , FILLFACTOR = 100, PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, DATA_COMPRESSION = NONE, STATISTICS_NORECOMPUTE = OFF, ONLINE = ON, MAXDOP=2)
ON [ROW_DATA];
- 每次索引重建发生时,关联的统计数据是否得到更新?
- 统计更新通常在索引重建完成后异步触发?
- 6 小时的总持续时间是否也包括更新统计信息所花费的时间?或者仅在索引重建完成 6 小时后才触发异步更新统计信息?
- 我可以使用 auto_stats 扩展事件捕获此统计信息更新事件并查看需要多长时间吗?
auto_stats
事件。您可以跟踪扩展事件的进展progress_report_online_index_operation
,但不会显示单独的统计构建事件,因为没有。在线索引构建期间的示例调用堆栈
你的进程很慢,因为你在做大量的资源密集型工作,而不是因为统计数据刷新。在 Microsoft 提供一种方法来直接支持您需要的操作(将各个分配单元移动到不同的文件组)之前,这真的无法避免。
对辅助分区方案的第一次重建将是单线程的,并且具有不必要的排序。
在 LOB 文件组上重建索引的成本很高,即使您这样做只是为了删除分区。SQL Server 不知道你在做什么,所以只是按照你的要求继续重建整个表。
第三次重建也很昂贵,但这个过程确实会让你到达你最终需要去的地方,同时保持表格大部分在线。
加速
BULK_LOGGED
在数据库设置为或恢复模型的情况下执行重建,SIMPLE
以尽可能利用最少的日志记录。SWITCH
如果您能够使用源表,则可以避免一次重建和单线程排序:PRIMARY
。SWITCH
源表到分区表。这应该是一个即时的元数据操作。无论如何指定WAIT_AT_LOW_PRIORITY
选项。LOB_DATA
文件组。LOB_DATA
文件组上的所有分配单元。ROW_DATA
文件组。您现在拥有原始表,其中包含
LOB_DATA
文件组中的 LOB 数据以及文件组中的所有其他内容ROW_DATA
。脚本
重组 Stack Overflow 示例数据库中的用户表的演示(首先创建文件组):
要将 Users 表重置为一切正常
PRIMARY
:* 第一次从非分区表重建到分区表是此规则的一个例外示例。此索引构建可能会导致单独的统计信息刷新,但前提是 SQL Server 认为现有统计信息已过时。到目前为止我还无法重现这一点,所以它仍然只是一种可能性。