CODEWITHSUNDEEP
sql-serverindexing
Ziouas
Ziouas

Reputation: 519

Performance in SQL Server when there is PK and UQ index on the same column

In SQL Server 2005 I have encountered a table with unique ID column (with unique index on it) and primary key clustered index on it too (so there are explicitly 2 indexes on this column). Is this a major performance factor on insert/update/delete on this table?

I'm trying to boost performance on a database created long ago and I wonder if removing such reduntant unique indexes could help. Does the database check/rebuild both of these indexes on every content modification? Or would the performance gain be too small to even bother with this?

Here is a sample index usage output:

INDEX   UserSeeks    UserScans  UserLookups UserUpdates
--------------------------------------------------------
1_PK    45517046      42911     245353       0
1_UQ    45517046      42911     245353       0
1_Other 45517046      42911     245353       0   
--------------------------------------------------------
2_PK    21538111      5685      231030      1121
2_UQ    21538111      5685      231030      1121
3_other 21538111      5685      231030      1121

And here is the query I used to get that data:

SELECT OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
   I.NAME AS IndexName,
   S.user_seeks AS UserSeeks,
   S.user_scans AS UserScans,
   S.user_lookups AS UserLookups,
   S.user_updates AS UserUpdates
FROM sys.indexes I 
JOIN sys.dm_db_index_usage_stats S
  ON (S.OBJECT_ID = I.OBJECT_ID)
WHERE(database_id = DB_ID())

And fixed join condition:

SELECT OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
   I.NAME AS IndexName,
   S.user_seeks AS UserSeeks,
   S.user_scans AS UserScans,
   S.user_lookups AS UserLookups,
   S.user_updates AS UserUpdates
FROM sys.indexes I 
JOIN sys.dm_db_index_usage_stats S
  ON (S.OBJECT_ID = I.OBJECT_ID)
  AND(S.index_id = I.index_id)
WHERE(database_id = DB_ID())

Upvotes: 3

Views: 1998

Answers (2)

Martin Smith
Martin Smith

Reputation: 453327

That might not be a redundant index.

Having a much narrower non clustered index which just contains the IDs may well have been put there as a deliberate strategy to benefit certain queries and/or to make foreign key validation more efficient.

I suggested that approach in my answer here to (successfully) resolve a deadlock problem the OP was having.

Upvotes: 4

Andomar
Andomar

Reputation: 238116

Indexes generally trade update/insert/delete performance for better select performance. That's almost always a good thing.

Does the database check/rebuild both of these indexes on every content modification?

It must, or the indexes would be out of date, and return wrong results.

Or would the performance gain be too small to even bother with this?

That depends on the activity on the table.

A unique constraint also has a function: it keeps a column unique. Since you can't have a unique constraint without an index, removing it does not seem like an option in your case.

Upvotes: 0

Related Questions