Why the columns are showing in Missing Indexes data eventhough Index is available on a table with same columns inorder?

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.

more ▼

asked Mar 01, 2012 at 05:43 PM in Default

avatar image

361 18 20 26

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Mar 01, 2012 at 06:04 PM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

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, 2012 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, 2012 at 03:59 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 20, 2013 at 12:36 PM

avatar image


(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 22, 2013 at 12:34 PM

avatar image

182 6 7 10

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, 2013 at 08:18 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 01, 2012 at 05:43 PM

Seen: 1670 times

Last Updated: Feb 22, 2013 at 08:18 PM

Copyright 2018 Redgate Software. Privacy Policy