x

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 '12 at 05:43 PM in Default

satya gravatar image

satya
361 18 18 21

(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.][1]

[1]: http://www.sqlskills.com/BLOGS/PAUL/post/Missing-index-DMVs-bug-that-could-cost-your-sanity.aspx
more ▼

answered Mar 01 '12 at 06:04 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

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)
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 '13 at 12:36 PM

surendra gravatar image

surendra
0

(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 '13 at 12:34 PM

chauhan_5041 gravatar image

chauhan_5041
182 3 6 8

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 '13 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x122
x2
x2

asked: Mar 01 '12 at 05:43 PM

Seen: 1279 times

Last Updated: Feb 22 '13 at 08:18 PM