SQL Server Table Partitioning, what is happening behind the scenes?

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. Wi开发者_Go百科th 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.

---

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:

• inserting the data into the new table
• removing data from the old table

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

---

If the partition function is used by a partitioned table and SPLIT results in partitions where both will contain data, SQL Server will move the data to the new partition. This data movement will cause transaction log growth due to inserts and deletes.

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.