MMartin1 (8/7/2014)
Jeff, I wanted to get your take on something Gail posted once :http://www.sqlservercentral.com/Forums/FindPost485499.aspx
The way I read it, a clustered index (pk or not) won't necessarily cause a huge slowdown to the insert process. I think it would lead to fragmentation if done often enough, due to new pages everywhere. Is there a balance to reach here?
Correct. Adding rows to a clustered index won't cause any slowdown to an insert process if the insert process is in the same order as the CI and it's at the logical end of the table.
If it's not in the same order or not at the logical end of the table, you get page splits and those not only cause fragmentation but they can also cause huge slowdowns because (on average) roughly have the page will need to be copied to a new page. None of that is contrary to what Gail stated in that post.
Non-clustered indexes suffer the same fate.
I have seen it where someone adding an NCI with a column of very low selectivity as the leading column will suddenly cause massive timeouts on a busy app. Heh... I know this to be true because I was that "someone" in my early days.
The same thing can happen with clustered indexes.
The "balance" to be sought is to determine whether the table will suffer more inserts than selects and to write your indexes accordingly.
--Jeff Moden
Change is inevitable... Change for the better is not.