Hello,
I found couple of indexes which looks like duplicate indexes. Before dropping any duplicate indexes...is there any additional checks that should be done. please advise.
Index A on Table A has ( col1, col2,col3) - Is it better to INCLUDE col5 in Index A and drop below Index B?
Index B on Table A has ( col2, col3, col5)
Does the order of the columns matter in index? In order for an index to be considered duplicate does the column order also has to match? Please advise.
Sincerely!
Column order is 100000000000% important in indexing.
Those two indexes you have mentioned would not be considered as duplicates due to the column order.
https://www.sqlskills.com/blogs/kimberly/how-can-you-tell-if-an-index-is-really-a-duplicate/
If you had
Col1,Col2,Col3 include Col5
And another
Col1,Col2,Col3,Col5
Then you could consider them duplicates.
You would need to look through the DMV sys.dm_db_index_usage_stats to get metric on usage. You may then also need to scrape the plan cache using XML shredding to find plans using both indexes and see what the conditions are in order to make an informed choice of what to remove.
But remember key length also plays a factor in the optimiser choosing the right index, so the first index has a smaller key length due to not having Col5 in the key columns, so queries, that only search on COL1/2/3 may only use that smaller index, you would have to run those queries forcing the larger index to see if those queries have any negative performance impact due to using the alternative index.
February 1, 2022 at 2:34 pm
Additionally, remember that unique indexes drive choices by the optimizer but may never end up in sys.dm_db_index_usage_stats, so be extra cautious about using that as a reference for which indexes to toss.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 1, 2022 at 10:20 pm
Hello,
I found couple of indexes which looks like duplicate indexes. Before dropping any duplicate indexes...is there any additional checks that should be done. please advise.
Index A on Table A has ( col1, col2,col3) - Is it better to INCLUDE col5 in Index A and drop below Index B?
Index B on Table A has ( col2, col3, col5)
Does the order of the columns matter in index? In order for an index to be considered duplicate does the column order also has to match? Please advise.
Sincerely!
Just to chime in here and to emphasize what Ant-Green stated... these are NOT duplicate indexes that you've posted. They don't have the same leading column, which is a prerequisite for identifying "duplicate" indexes.
--Jeff Moden
February 2, 2022 at 3:02 pm
DBATools.io has a nice function called Find-DbaDbDuplicateIndex
(however it has IMHO aflaw and does not report IsUnique for an index, reporting duplicate indexes composed of the same column(s) and column order. So you may be dropping the wrong index and mess up your data model if you - by chance - pick the unique index to be dropped)
Find-DbaDbDuplicateIndex -SqlInstance $SQLInstance -Database $DbName
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 2, 2022 at 5:00 pm
Someone mentioned that issue on the SQL Slack community also Johan.
Unsure if that was also yourself, but if you file a feature request on http://www.DBATools.io/issues, someone will take a look at it unless you want to contribute to the project yourself.
February 2, 2022 at 11:18 pm
DBATools.io has a nice function called Find-DbaDbDuplicateIndex (however it has IMHO aflaw and does not report IsUnique for an index, reporting duplicate indexes composed of the same column(s) and column order. So you may be dropping the wrong index and mess up your data model if you - by chance - pick the unique index to be dropped)
Find-DbaDbDuplicateIndex -SqlInstance $SQLInstance -Database $DbName
BWAAA-HAAAA-HAAAA!!!! If it has that kind of serious oversight in it, why would you call it "nice"? 😀
--Jeff Moden
February 3, 2022 at 8:38 am
Johan Bijnens wrote:DBATools.io has a nice function called Find-DbaDbDuplicateIndex (however it has IMHO aflaw and does not report IsUnique for an index, reporting duplicate indexes composed of the same column(s) and column order. So you may be dropping the wrong index and mess up your data model if you - by chance - pick the unique index to be dropped)
Find-DbaDbDuplicateIndex -SqlInstance $SQLInstance -Database $DbNameBWAAA-HAAAA-HAAAA!!!! If it has that kind of serious oversight in it, why would you call it "nice"? 😀
It may have its flaws, but darned it is a great tool set and enhances quiet quickly !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 3, 2022 at 8:58 am
Someone mentioned that issue on the SQL Slack community also Johan.
Unsure if that was also yourself, but if you file a feature request on http://www.DBATools.io/issues, someone will take a look at it unless you want to contribute to the project yourself.
I've requested the modification (and provided the modified posh function) here:
"add the attribute "IsUnique" to Find-DbaDbDuplicateIndex"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 4, 2022 at 12:42 am
Jeff Moden wrote:Johan Bijnens wrote:DBATools.io has a nice function called Find-DbaDbDuplicateIndex (however it has IMHO aflaw and does not report IsUnique for an index, reporting duplicate indexes composed of the same column(s) and column order. So you may be dropping the wrong index and mess up your data model if you - by chance - pick the unique index to be dropped)
Find-DbaDbDuplicateIndex -SqlInstance $SQLInstance -Database $DbNameBWAAA-HAAAA-HAAAA!!!! If it has that kind of serious oversight in it, why would you call it "nice"? 😀
It may have its flaws, but darned it is a great tool set and enhances quiet quickly !
To be sure, I was talking about the one function, not the whole tool set.
--Jeff Moden
February 4, 2022 at 7:15 am
Johan Bijnens wrote:Jeff Moden wrote:Johan Bijnens wrote:DBATools.io has a nice function called Find-DbaDbDuplicateIndex (however it has IMHO aflaw and does not report IsUnique for an index, reporting duplicate indexes composed of the same column(s) and column order. So you may be dropping the wrong index and mess up your data model if you - by chance - pick the unique index to be dropped)
Find-DbaDbDuplicateIndex -SqlInstance $SQLInstance -Database $DbNameBWAAA-HAAAA-HAAAA!!!! If it has that kind of serious oversight in it, why would you call it "nice"? 😀
It may have its flaws, but darned it is a great tool set and enhances quiet quickly !
To be sure, I was talking about the one function, not the whole tool set.
No worries, Jeff.
As you know dbatools is community driven and continuously grows and enhances.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 9, 2022 at 6:50 am
et voila .... they already fixed it 🙂
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 9, 2022 at 5:25 pm
et voila .... they already fixed it 🙂
Awesome. I'm impressed.
--Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply