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

Question

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())

score 4 · Accepted Answer · edited May 23 '17 at 11:48

4

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.

edited May 23 '17 at 11:48

Community

1
1

answered Oct 25 '12 at 16:11

Martin Smith

438,706
87
741
845

But execution plan shows that SELECT query uses the PK index and foreign keys also refer to the PK index (not the UQ). And Primary Key also guarentees by definition that this column is unique and if it's clustered then logical reads also base on that. If that's true then I believe that the UQ index is not neccesary. – Ziouas Oct 25 '12 at 16:23
1

@Ziouas - Does sound like it then. You can use `sys.dm_db_index_usage_stats` to get an idea if it is ever used by anything. [Example query here](http://weblogs.sqlteam.com/mladenp/archive/2009/04/08/SQL-Server---Find-missing-and-unused-indexes.aspx). That does only have stats that go back at most until the time the instance was last restarted though. – Martin Smith Oct 25 '12 at 16:25
Nice hint with this unused indexes query, I'll run it tomorrow at work and I'll give you feedback if these UQ where ever used. Thanks! – Ziouas Oct 25 '12 at 16:34
Stats shown that usage statistics are the same for BOTH indexes (PK and UQ), so I presume that the DB does the same job twice in this case? But I wonder if it searches first on the UQ index and later fetches data by clustered index. – Ziouas Oct 26 '12 at 20:06
@Ziouas - It shouldn't do as that wouldn't make any sense. If you already have a key to look up there would be no point looking it up in the unique index just to retrieve the exact same key value that you then use to look up in the clustered index. – Martin Smith Oct 27 '12 at 11:26
I've added to main post a small sample from index usage output on two tables and query I used to get that data. Maybe the query is wrong, because it shows that every index on a table is used exactly the same number of times. – Ziouas Oct 29 '12 at 11:35
1

@Ziouas - Your join condition should be `(S.OBJECT_ID = I.OBJECT_ID AND S.index_id = I.index_id)` – Martin Smith Oct 29 '12 at 11:37
1

Ok, my bad, really stupid mistake... Fixed query shows that the UQ are almost never used (except for some bad cases, when the table doesn't have any PK only UQ on id column). So I believe that these UQ's really are redundant in this case and that the mystery is solved :) – Ziouas Oct 29 '12 at 11:44

score 0 · Answer 2 · answered Oct 25 '12 at 16:10

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.

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

2 Answers2