details on partition switch with aligned indexes


  • This is a "what's happening under the covers" type of question.  I have a table with a clustered index and two non clustered indexes, all partition aligned.  When I perform a partition switch I can see, by looking at the partition_id and the partition row count, that the physical switched partition from the clustered index is replaced with a new empty partition, that is what I would expect to see since I switched that partition out. 

    When I look at the nonclustered indexes, the physical partition which i've identified by it's partition_id, the one I would expect to be gone, is actually still there with a row count of zero.  Since the physical partition is still in place with a rowcount of zero did sql server perform some kind of delete to remove the rows from the index?  If so, I would expect that kind of operation would incur a performance hit if we are talking about an index partition with millions of rows.

    Does anyone have any ideas about what's happening.


    Friday, October 11, 2013 8:29 PM

All replies