Clustered index sort order matters when loading?

Question

Clustered index sort order matters when loading?

Ted Manasa

Default port

Points: 1461
More actions
May 28, 2008 at 7:27 am

#207759

I've created a clustered ASCENDING on a continually-increasing date column. Because the data is stored in order of the clustered index, will its sort order impact load perfromance?
Thanks guys.
---------------------------
|Ted Pin >>

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Yes, if you insert records in the order of the clustered index, you will not fragment the index during the data load. It sounds like you have it in the correct order.

Ted Manasa Default port Points: 1461 More actions · Answer 2

Just the reassurance I needed. Thanks, Earl!

---------------------------
|Ted Pin >>

Grant Fritchey SSC Guru Points: 398707 More actions · Answer 3

Short answer, it depends. If the data comes in ordered the same was as the index you'll minimize the amount of work the index has to do as it inserts the data (not eliminate, minimize). If the data is scattered, then the insert operations will sort it for you at a slightly higher cost. Either way, it really depends on the page splits. If the order causes all the data to be inserted at the base of the tree, it will be more of a problem than if it inserts it distributed through the order or at the end.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ted Manasa Default port Points: 1461 More actions · Answer 4

Grant, in our particular situation, all the new values for this clustered date column are the same for each batch, and are always greater than the previous batch. If I'm not mistaken, this should virtually eliminate page splits?

Also, is there any advantage to dropping a clustered index during load and re-creating it afterwards?

I understand that simply disabling a clustered index will render the table unloadable/inaccessible.

Thanks,

---------------------------
|Ted Pin >>

Grant Fritchey SSC Guru Points: 398707 More actions · Answer 5

It doesn't sound like you need to worry about page splits.

I've seen situations where dropping the index and recreate it work better and I've seen places where it made things worse. I couldn't quantify which works best when. I'd simply test the system with the data expected.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ted Manasa Default port Points: 1461 More actions · Answer 6

Will try it out. Thanks, Grant.

---------------------------
|Ted Pin >>