SQL Server 表分区，幕后发生了什么？
我正在仓库中非常大的事实表上进行表分区。我以几种不同的方式执行了该脚本。有和没有非聚集索引。使用索引，它似乎会显着扩展日志文件，而如果没有非聚集索引，它似乎不会扩展日志文件那么多，但由于重建索引而需要更多时间来运行。
我正在寻找的是有关分割表分区时日志文件幕后发生的情况的任何链接或信息。
I'm working with table partitioning on extremely large fact table in a warehouse. I have executed the script a few different ways. With and without non clustered indexes. With indexes it appears to dramatically expand the log file while without the non clustered indexes it appears to not expand the log file as much but takes more time to run due to the rebuilding of the indexes.
What I am looking for is any links or information as to what is happening behind the scene specifically to the log file when you split a table partition.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为（在某种程度上）对正在发生的事情进行理论分析并不难。在幕后，每个分区都有一个不同的 HoBT，用正常语言来说，这意味着每个分区实际上都位于其自己的隐藏表上。
因此，理论上分区的拆分（假设数据正在移动）将涉及：
可以计算出 NC 索引，但取决于是否有聚集索引，理论化将会改变。索引是否分区对齐也很重要。
给出有关表（CL 或堆）的更多信息，我们可以进一步推理这一点
I think it isn't to hard to theorize what is going on (to a certain extent). Behind the scenes each partition is given a different HoBT, which in normal language means each partition is in effect sitting on it's own hidden table.
So theorizing the splitting of a partition (assuming data is moving) would involve:
The NC index can be figured out, but depending on whether there is a clustered index or not, the theorizing will alter. It also matters whether the index is partition aligned or not.
Given a bit more information on the table (CL or Heap) we could theorize this further
这是来自 Microsoft 的一篇文章 分区表和索引策略
所以看起来它从旧分区中删除并插入到新分区中。这可以解释 t-log 的增长。
This is from an article by Microsoft on Partitioned Table and Index Strategies
So looks like its doing a delete from old partition and and insert into the new partition. This could explain the growth in t-log.