Index overhead between Clustered index Include Index

Question

Index overhead between Clustered index Include Index

Daryl AZ

Hall of Fame

Points: 3087
More actions
November 18, 2011 at 7:44 am

#260356

Working with a developer on a query against a value table. The value table had a clustered index on the unused identity column on the table. We went in and updated (drop/create) the main non-clustered index with two include columns. The performance increase was quite satisfying.
We were talking about dropping the primary key/clustered index on the rarely used identity column for a clustered index on the identifying fields (3 of them). However we have a performance issue with this application in pulling large amounts of data and slow importing. (structural model importing from CAD tool)
Would insert performance be better if the clustered index remains on the identity column?
I do not think we impacting insert performance significantly by adding the include columns. It is a bit more data but still the same step sequence. We are testing that now.
Thoughts?
thanks

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

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

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 1

I would strongly recommend having a clustered index on the table. It doesn't have to be the primary key, but by and large, in most circumstances, a table with a cluster outperforms one without. The cluster defines the storage of the table. It's also an access path to the data. It also acts as the lookup for the data from any non-clustered indexes. Maybe, instead of setting up your existing index as an INCLUDE nonclustered index, you could try making it the clustered index. The INCLUDE would then be automatic since all data is stored with the clustered index.

I also don't recommend just dropping the PK on a table. It's supposed to be there to keep the data unique so that you don't get duplicates and to act as a mechanism for any joins to that table.

Which, by the way, do you join to that table? If so, that clustered pk might not be as unused as you think it is.

"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

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 2

I understand the value of a clustered index.

I was questioning whether switching from a clustered index on an Identity to a clustered index on non-sequential keys would affect performance of large data imports.

thanks

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

Daryl AZ (11/18/2011)
I understand the value of a clustered index.
I was questioning whether switching from a clustered index on an Identity to a clustered index on non-sequential keys would affect performance of large data imports.
thanks

Misunderstood.

Test it is the only way to be sure.

But, yeah, it could. Instead of staking all new data at the end of the pages and constantly adding pages there, which is what happens with an identity column, you'll be putting data wherever it belongs and possibly dealing with more page splitting than before. That will negatively affect the process. How much? Back to testing again.

"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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

It may well do. Depends how you're inserting. One batch, probably not. Multiple batches, likely yes because of the increased page splits. Test and see.

Also remember that the clustering key is in every single nonclustered index, so your nonclusters are now going to be bigger.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 5

That was my thought direction also.

One idea

During the insert since the non-clustered index (includes) is also being created/populated, swapping out the PKEY index would be removing overhead and would be less overhead on the insert.

If that is true the change would be more efficient all around.

Will repost if I can get my developer to add some time checkpoints for the different indexes.

thanks