1

I have given a task to find out the unused indexes in my production environment. I'm using a DMV to find the unused indexes should i include the Primary key Clustered Indexes on it? If a Primary key Index isn't being used, shouldn't that be of some concern? Unless, perhaps, it is a small table that isn't accessed very often?

  • 1
    A PK is also used when inserting into the table and usually when updating or deleting from the table. And you can't simply remove it, even if the table is "stable" (no change at all) for one reason because there may be FKs referencing it. – ypercubeᵀᴹ Jun 26 '13 at 14:32
1

Primary Key and Unique constraints are enforced by a supporting index for performance and to enforce uniqueness. Just because the index is not used for searching does not mean it isn't being used to guarantee uniqueness of the key values.

Whether or not a table has a primary key constraint or any unique constraints is not a performance decision - it is a data integrity decision. The primary key gets a primary key constraint and the candidate keys get unique constraints.

Whether or not the primary key index is clustered or nonclustered, on the other hand, is a performance question. Doing a Binoogle search on that will find many hits, including https://stackoverflow.com/questions/2206541/index-view-how-to-choose-the-clustered-index.

|improve this answer|||||
0

The clustered index is the table, so i would certainly exclude those (unless you don't actually need the table). If you are looking to do index tuning, a great place to start is Kendra Little's sp_blitzindex. She has already done the hard work of querying the dmvs for you and provides advice for how to remedy any problems you might find.

|improve this answer|||||
0

Primary key constraints require a supporting index. It doesn't necessarily have to be a clustered index (it is by default, but you can have a nonclustered index supporting a PK constraint), but whether or not you should include those in unused indexes, I personally wouldn't. What would you use to "replace" the index that supports a PK? Another index.

If a Primary key Index isn't being used, shouldn't that be of some concern?

Just because the clustered index isn't getting any seeks/scans on it just means that you could either have other indexes that satisfy queries, or you just aren't issuing queries that need to seek/scan on that table.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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