Performance in SQL Server when there is PK and UQ index on the same column
indexing, sql-server
Solution
That might not be a redundant index.
Having a much narrower non clustered index which just contains the `ID`s 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.
Problem
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()) ```