December 4, 2023 at 12:00 am
Comments posted to this topic are about the item Be Careful with Missing Index Requests
December 4, 2023 at 6:54 am
I certainly agree that the Missing Index data in SQL Server is useful but not something that should be applied without further investigation.
One thing to consider is if a non-clustered columnstore index (ncci) could be more useful. You will never see anything in the Missing Index details to suggest using a ncci, you have to accept this is one of the things missing from Missing Indexes.
There are pros and cons of using a ncci and you really need to have Enterprise Edition with online rebuilds to get the pros outweighing the cons. The big issue with a ncci is it needs more regular maintenance than a row-format index in order to maintain performance. However if they are kept in good condition an ncci can do amazing things for performance.
My experience is that SQL Server does not take into account an existing ncci before giving its Missing Index data. It will happily suggest a row-format Missing index when the ncci will out-perform the suggested index.
However, a ncci is not a universal fix for all index problems. Again, my experience on SQL2019 is that SQL server shows the same execution path if it is taking advantage of the column value data (a good thing) compared to scanning the entire ncci as though it were a table (a bad thing). The main difference is in the number of IOs and page reads, which will be much lower if the column value data is used.
Therefore, if you have a ncci on a table, keep looking at the Missing Index data and query performance. It may be that a row-format index is needed in addition to the ncci.
Finally, make sure you have row-format indexes to support all foreign keys. SQL Server seems to be hard-coded to avoid using a ncci if it needs to check Referential Integrity.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 4, 2023 at 7:57 am
The cautionary notes in the editorial (and linked articles) are warranted. However, missing index suggestions also include additional information (other than column names) that mitigates some of the concerns.
The free SQLFacts toolkit (http://www.SQLFacts.com) contains two tools specifically for taking advantage of missing index suggestions.
The IndexNeeds tool uses the DMVs.
The IndexNeedsPlus tool uses the plan cache, and it provides functionality not found elsewhere.
The latter is discussed in this article - https://www.sqlservercentral.com/articles/performance-tuning-with-sqlfacts-tools.
Creator of SQLFacts, a free suite of tools for SQL Server database professionals.
January 19, 2024 at 8:03 pm
Although the Azure Automatic Tuning for Azure SQL Databases is much improved over the Missing Index Hints in execution plans and DMVs, it still has issues, and I would not recommend enabling the automatic implementation of their recommendations either. It's better than nothing for companies that have no DBA, but it is far from perfect.
One thing no tool can do is tell you that you have a missing column in what should be a multi-column join condition making it look like you need an index when what you really need is to fix the code.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply