4

We have a number of tables in our database with an identity column as the primary key & clustered index. Over the years records have been added and deleted from these tables and of course there are now gaps in the primary key column.

Is there any impact to the performance of the clustered index on these tables as a result of these "missing" identities?

From my understanding as long as the index b-tree is refreshed then there shouldn't be any problems, but I can't find anything specific to confirm this.

Thanks

Improve this question

2 Answers 2

Reset to default
8

I don't see how gaps in your identity values could have A N Y negative impact on your performance.

After all - the values are still all 4-byte integer, they're still as optimal for JOINs as anything can be ..... and even if you have gaps, the IDENTITY values are ever-increasing....

Of course, if you physically deleted rows from your clustered index, then there will be "holes" in your pages (the "Swiss Cheese" problem :-) ) - but those would be fixed by a nightly database maintenance that you certainly do have in place, don't you??

And even if you do occasionally delete a row from your table, the impact on your index fragmentation will be marginal.

Improve this answer
4
  • 3
    Sure, but is there any performance penalty from having gaps between the letters of the word any? :p
    – Andrew Barber
    Commented Jan 27, 2012 at 6:39
  • @AndrewBarber: not entirely sure on that one :-)
    – marc_s
    Commented Jan 27, 2012 at 7:01
  • 1
    One interesting case where gaps can make a difference
    – Martin Smith
    Commented Jan 28, 2012 at 11:20
  • Thanks @marc_s. I had assumed that was the case and couldn't think of any reason why it wouldn't be. And yes we do have nightly maintenance plans to fix the fragmentation
    – Greg
    Commented Jan 30, 2012 at 2:26
4

Addition and deletion of data over time will fragment your clustered index. These are the gaps you are seeing and they will decrease performance. This fragmentation is fixed by doing an index reorganization or an index rebuild. This is the maintenance @marc_s is talking about.

General guidelines are to use index reorganization for lightly fragmented indexes and index rebuilds for medium to highly fragmented indexes.

I hope this is what you meant by 'index b-tree is refreshed '.

Improve this answer
1
  • Thanks @StanleyJohns. That is what I meant by "index b-tree is refreshed". Couldn't remember the word I was looking for
    – Greg
    Commented Jan 30, 2012 at 23:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.