|
Hi Everyone, 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.
(comments are locked)
|
|
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. Thank you Grant Fritchey, but that index was created long back(more than one year) and every week my Production server is restarting. So as per knowledge statistics will be updated whenever restating the server, please tell me if I am wrong. As per attachment it`s bug in SQL11 earlier versions. Please help me how to understand statistics and tell when it will be updated.
Mar 02 '12 at 04:33 AM
satya
by statistics I mean the stats on your indexes and tables. These are not updated by restarting the server. They are updated automatically if you have auto_update enabled. If not you need to update them manually. Even though stats are updated automatically, they might need a manual update with a full scan. They are also updated by a in index rebuild. I can't see what you can, so for further help, I'd want to see the output from the missing indexes table and a script of the index in order to be able to compare. You're sure there's not a difference on sort order or the order of columns? Those differences would explain the suggestion.
Mar 02 '12 at 03:59 PM
Grant Fritchey ♦♦
(comments are locked)
|
|
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. Yes, true. But the question was, why was the Missing Index information still in place after an index that matched the criteria was created.
Feb 22 at 08:18 PM
Grant Fritchey ♦♦
(comments are locked)
|
|
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.
(comments are locked)
|
