2 unique indexes

Question

2 unique indexes

tcronin 95651

SSCertifiable

Points: 6735
More actions
November 3, 2014 at 9:46 am

#294831

Maybe I am getting old or just can't see the obvious. I have a 3rd party db with a table called transactions. It has a unique contstraint on the transaction_id of this table. It also had a alternate unique index that consists of the same transaction_id, a transtype, and a transdate. Is there any way that the second index is not irrelevant? The problem is one of the 3rd party app's loads into this table has been getting some blocking issues. I notice in test I can remove 1/2 of the logical reads if I dump the second index.

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

tcronin 95651 (11/3/2014)
Is there any way that the second index is not irrelevant?

Queries that search for a particular transaction_id AND transtype AND transdate can take advantage from this index. Also index-only scans and seeks can happen on this index when transtype and/or transdate are in the SELECT list.

I notice in test I can remove 1/2 of the logical reads if I dump the second index.

Maybe you are getting a suboptimal plan with lots of lookups. Is this the case?

-- Gianluca Sartori

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 2

that i get the issue is it is going to try to force uniqueness when it is not needed, if the transaction_id is unique the engine is forced to look for a 2ndary uniqueness which can never exist

arnipetursson SSCertifiable Points: 6557 More actions · Answer 3

Since transaction_id is unique, it looks like the second index may be extraneous, unless it is used for covered queries.

Have you checked if it is being used? Google 'SQL Server unused indexes'

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 4

I used Brent Ozars blitzindex, not sure if he would ever check for a duplicate unique index, if the transaction_id is unique there is no logical record that would need to be checked for transaction_id, posted, etc just extra baggage.

arnipetursson SSCertifiable Points: 6557 More actions · Answer 5

So are you finding that the index is used or not?

If it is being used, then you may not want to drop it.

However, if it is not being used, then I say drop it and watch how things go.

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 6

when I put it back but drop the unique part I save 30% on logical reads.