April 22, 2016 at 8:45 am
We have a process that runs once a week to both find "Missing indexes", based on a April 2009 copy of "FindMissingIndexes" script from Brent Ozar (might be part of the sp_BlitzIndex?), as well as the corollary "FindUnusedIndexes". Basically, the job checks to see if TempDB "create date" is greater than 3 days, then runs and puts the data into a table for review with a timestamp, based on the sys.dm_db_missing_index DMVs. We then aggregate the data and review the recommendations.
My question is on a recommendation. We have a table which has the following unique clustered index (only the fields shown are part of the index):
[InvoiceDate] ASC, -- smalldatetime
[InvoiceNumber] ASC, --- varchar(9)
[ItemNo] ASC --- varchar(24)
If "ItemNo" is part of clustered index, why would I see a number of recommendations for a new, non-clustered index with "ItemNo" in them?
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
April 22, 2016 at 8:48 am
The index is not sorted by itemNo.
It is sorted by date.
So you dont have an index on data sorted by itemno.
Imagine a phonebook sorted by Lastname, Then by firstname, then by address.
Your index will be (surname,Firstname,Address)
So I cannot search based on address because the phone book is sorted by surname, it is only possible to search by address if I already have the surname and firstname.
Same thing here.
Your phone book is sorted by date, then by ID then by number and not by ItemNo /Address, as an analogy.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply