Sql-server – Performance impact of having gaps in Identity Column in SQL Server 2005

indexsql-server-2005

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

Best Answer

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.

Related Solutions

SQL Server – Is Avoiding Clustered Index on Incrementing Key a Myth?

The myth goes back to before SQL Server 6.5, which added row level locking. And hinted at here by Kalen Delaney.

It was to do with "hot spots" of data page usage and the fact that a whole 2k page (SQL Server 7 and higher use 8k pages) was locked, rather then an inserted row Edit, Feb 2012

Found authoritative article by Kimberly L. Tripp

"The Clustered Index Debate Continues..."

Hotspots were something that we greatly tried to avoid PRIOR to SQL Server 7.0 because of page level locking (and this is where the term hot spot became a negative term). In fact, it doesn't have to be a negative term. However, since the storage engine was rearchitected/redesigned (in SQL Server 7.0) and now includes true row level locking, this motivation (to avoid hotspots) is no longer there.

Edit, May 2013

The link in lucky7_2000's answer seems to say that hotspots can exist and they cause issues. However, the article uses a non-unique clustered index on TranTime. This requires a uniquifier to be added. Which means the index in not strictly monotonically increasing (and too wide). The link in that answer does not contradict this answer or my links

On a personal level, I have woked on databases where I inserted tens of thousands of rows per second into a table that has a bigint IDENTITY column as the clustered PK.

Sql-server – How to avoid index scans in SQL Server 2005

Any solution :)

No, not really.

As you said yourself - CellRow is just not very selective - 5 possible values, 100'000 rows = roughly 20'000 rows for each possible value.

SQL Server's query optimizer recognizes this and probably figures it's easier and more efficient to do a index scan rather than a seek for 20'000 rows.

The only way to avoid this would be to use a more selective index, i.e. some other column that selects 2%, 3% or max. 5% of the rows for each query.

PS: Check your execution plan - does it get the values straight from the index, or does it need a "Key Lookup" step to go get the data??

You didn't mention what data types your columns are - if the CellValue isn't too big, you could add it to the index (or at least include it in the index) to avoid costly key lookups:

CREATE INDEX IX_CellRow_CellValues
ON dbo.Cell(CellRow) INCLUDE(CellValue)

You'd still have the index scan, though

Best Answer

Related Solutions

SQL Server – Is Avoiding Clustered Index on Incrementing Key a Myth?

Sql-server – How to avoid index scans in SQL Server 2005

Related Question