When I am going through the Production database missing indexes data of my project, I observed that one table showing missing index eventhough the table having same index( includes all the equity columns, nonequity columns, include columns) in active mode.
and that index fragmentation percentage is also less than 20%.
Could you please explain me why those columns are showing in missing indexes data eventhough the index is present.
Thanks in Advance.
It could be one of two things. It could be that you created the index after the missing index information was created. That information isn't refreshed constantly.
It could also be that the optimizer hasn't recognized that index on a query, but saw stats that indicated an index would be useful. This can happen if your statistics on the index are badly out of date.
AND, there's a [bug detailed here by Paul Randal.]: http://www.sqlskills.com/BLOGS/PAUL/post/Missing-index-DMVs-bug-that-could-cost-your-sanity.aspx
answered Mar 01 '12 at 06:04 PM
Grant Fritchey ♦♦
When the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a suboptimal query plan, but still stores information about these indexes. these indexes are called "Missing Index" you can check equality column and create the new indexes.
answered Feb 22 '13 at 12:34 PM
One index can be used on any number of tables because of that the indexes are continued on other table ie why look like missing in order.
answered Feb 20 '13 at 12:36 PM