how to determine if we can delete the index can be removed

Question

Post reply

how to determine if we can delete the index can be removed

892717952

SSC Eights!

Points: 843
More actions
June 6, 2023 at 6:30 am

#4205365

The table InventDim has some indexes, we create one index idx1 which has index column f1 and f2, and creating index idx2 which has index column f1、f2 and f3, index column sequence of idx1 is same with idx2, means the index columns of idx1 totally are included in idx2 and the sequence of index column is same with idx2. in the case, can we delete idx1 ? thanks a lot!

Viewing 12 posts - 1 through 11 (of 11 total)

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

Probably, yes. But:

There can be one or more queries mentioning idx1 in a hint. If you drop the index, these queries will start failing. Had you instead just altered the existing index, this would not be an issue.
There could be a critical query like SELECT f1, f2, COUNT(*) FROM tbl GROUP BY f1.f2. If you drop idx1, this query will use idx2 instead, but since idx2 is wider, performance will degrade somewhat. This can certainly be considered to be a corner case.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

ScottPletcher SSC Guru Points: 101108 More actions · Answer 2

Yes, you can delete idx1. If you use hints that force use of that specific index (which is very rare (or at least should be!)), then you would have to correct those queries afterward.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Before dropping idx1 - verify that it is not being used to support a foreign key constraint or a unique constraint.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ScottPletcher SSC Guru Points: 101108 More actions · Answer 4

The new index should handle any existing fk constraint(s), since both indexes have the same keys.

A unique constraint should be a different structure, i.e., you'd have to use a DROP CONSTRAINT rather than a drop index to get rid of it. If the new index is declared as unique, SQL will enforce that uniqueness, it just won't be a formal unique constraint.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

Jeffery makes a good point about uniqueness. If (f1, f2) is supposed to be unique, dropping that index is not the best of ideas. Then again, in that case, there is little reason to add f3 as a third index key, but f3 should be added as an included column in idx1.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

ScottPletcher SSC Guru Points: 101108 More actions · Answer 6

Oops, I misread, I thought the 3rd column was just INCLUDEd, and that it had the same key columns as the original index.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 7

I'll recommend that you can mitigate risk by (as previously stated) first making sure that the index is not a unique index and ,second, just disabling the index and see if the phone rings. If it doesn't cause an issue over a monthly cycle, you can probably delete it. Just make sure that any index maintenance that you might be doing (for goodness sake, DON'T use the typical industry wide "Best Practices, which have been incorrect from the git) doesn't re-enable them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

892717952 SSC Eights! Points: 843 More actions · Answer 8

Thank you for your patience and help! but I still have some confusion regarding the unique index of idx1. sorry to bother you again, thanks!

if using index hints to use the specific index (such as idx1) and the idx1 is being used to support a foreign key constraint, it will make the performance degraded. if idx1 (f1、f2) is a unqiue key, but idx2 has f1、f2 and f3, then idx2 aslo be a unique key, I don't understand if will make the performance bad if idx1 is a unique index

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 9

I don't think anyone has said that performance will be bad. It is more the functional aspect of it. If (f1, f2) is supposed to be unique, and you drop the index on (f1, f2) in favour of an index on (f1, f2, f3), the new index cannot guarantee that (f1, f2) is unique.

And when it comes to index hints, the problem is that if you have a query like:

SELECT ... FROM tbl (WITH INDEX = idx1) JOIN ... WHERE ...

and you drop idx1, this query will result in an error.

When it comes to foreign keys, that was nothing I brought up, and I don't really see an issue there.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

892717952 SSC Eights! Points: 843 More actions · Answer 10

Thank you Erland Sommarskog. I have done some testing based on different cases ,thank you!

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 11

892717952 wrote:

Thank you Erland Sommarskog. I have done some testing based on different cases ,thank you!

AND? What did you find out?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)