SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Ultimate Missing Index Finder


The Ultimate Missing Index Finder

Author
Message
arbarnhart-780010
arbarnhart-780010
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 60
I figured it out - a bonehead move on my part. We keep our database in source control by scripting it with DB Ghost. To get changes, I script anything I am working on and check it in, them get all the scripts and run them. So I had a "new" database. I have to run our application and beat on it a while. I did it a little and started getting results.
YeshuaAgapao
YeshuaAgapao
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 211
Ideally you would want to run this on a database with production load. If you can't, QA load is a distant 2nd-place alternative. DEV traffic won't get much insight on global worst offenders.



Scott Herbert-214404
Scott Herbert-214404
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 245
This is a great proc; I've been using something similar for a while, but this adds in a few more useful columns. One thing that's baffling me however is that some of the highest results from this proc I've subsequently added indexes for, and they still turn up (unlike most instances where as soon as the index is added, they disappear from the proc results).

For example, from the proc results:
schema name = Analysis
Table name = Notice
Equality = NULL
Inequality = [NoticeStageID]
Include = [ID], [AnalysisID], [FileID]
Score = 25953.496496

I've then added the index
CREATE INDEX [ixAnalysisHolding_AnalysisID_CustodianBeneficialOwnerID]
ON [Analysis].[AnalysisHolding]
([AnalysisID], [CustodianBeneficialOwnerID])
INCLUDE([ID], [RegisteredHolderBeneficialOwnerID], [BeneficialOwnerInvestorID], [Shares])
WITH ( FILLFACTOR = 90 );

and no difference to the proc results. What's going on? Am I missing the significance of the "Inequality" column, or is this a factor of the way the DMVs work?
YeshuaAgapao
YeshuaAgapao
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 211
We have the issue with provider worklist at Carle Clinic. I think it is a SQL server bug that involves recording data in the DMVs. Might be a data type issue but I doubt it because other non-uses of indexes due to data-type casting don't show up.

I know that SQL server can only use one nonequality column in a seek. Inequality is any seek that is not for '=' -- '>', '<', NOT IN(), LIKE etc..



Scott Herbert-214404
Scott Herbert-214404
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 245
thanks, that's (the non-equality bit) an interesting piece of trivia.
Vincent Central
Vincent Central
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 81
Great util!! One little improvement I made... I added this to the front of the SELECT (just before the first column) to make the create statements, that way you could just copy/paste the results of that into a new qry to run the create statements you want.


'CREATE NONCLUSTERED INDEX IX1_' + object_name(sys.dm_db_missing_index_details.object_id)
+ left(cast(newid() as varchar(max)),5) + char(10) + 'on [dbo].'
+ object_name(sys.dm_db_missing_index_details.object_id) + ' (' +
case
when sys.dm_db_missing_index_details.equality_columns is not null
and sys.dm_db_missing_index_details.inequality_columns is not null
then sys.dm_db_missing_index_details.equality_columns
+ ',' + sys.dm_db_missing_index_details.inequality_columns
when sys.dm_db_missing_index_details.equality_columns is not null
and sys.dm_db_missing_index_details.inequality_columns is null
then sys.dm_db_missing_index_details.equality_columns
when sys.dm_db_missing_index_details.inequality_columns is not null
then sys.dm_db_missing_index_details.inequality_columns
end
+ ')' + char(10)
+
case
when sys.dm_db_missing_index_details.included_columns is not null
then 'Include (' + sys.dm_db_missing_index_details.included_columns + ')'
else ''
end as CreateIndexStmt
--Vincent yovincent@hotmail.com




EDIT:

The missing index DMVs this qry is based on often report missing indexes that, if created, would have too many columns... Imagine the performance impact when you go to do an update or insert on a table with a 50 column index!! Therefore, I also added this to the end of the WHERE clause to keep the qry from returning results with a ton of included columns.


AND isnull(Len(included_columns)-Len(Replace(included_columns,',',''))+1,1)<10
--Vincent yovincent@hotmail.com


YeshuaAgapao
YeshuaAgapao
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 211

The missing index DMVs this qry is based on often report missing indexes that, if created, would have too many columns... Imagine the performance impact when you go to do an update or insert on a table with a 50 column index!! Therefore, I also added this to the end of the WHERE clause to keep the qry from returning results with a ton of included columns.


You also get a few entries where the long list of includes is only differing by one or has one or two additional or missing include columns. That is why it is not a good idea to blindly copy and paste pre-generated index create statements (I wouldn't do this with DTA either), but rather to merge whenever and as much as possible. One want one key and two includes, another want this one key and one other, but no includes. These can be merged.



don kitchen
don kitchen
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 29
It's probably because you created an index against the AnalysisHolding table when the result was complaining about the Notice table.

Scott Herbert (10/9/2008)
This is a great proc; I've been using something similar for a while, but this adds in a few more useful columns. One thing that's baffling me however is that some of the highest results from this proc I've subsequently added indexes for, and they still turn up (unlike most instances where as soon as the index is added, they disappear from the proc results).

For example, from the proc results:
schema name = Analysis
Table name = Notice
Equality = NULL
Inequality = [NoticeStageID]
Include = [ID], [AnalysisID], [FileID]
Score = 25953.496496

I've then added the index
CREATE INDEX [ixAnalysisHolding_AnalysisID_CustodianBeneficialOwnerID]
ON [Analysis].[AnalysisHolding]
([AnalysisID], [CustodianBeneficialOwnerID])
INCLUDE([ID], [RegisteredHolderBeneficialOwnerID], [BeneficialOwnerInvestorID], [Shares])
WITH ( FILLFACTOR = 90 );

and no difference to the proc results. What's going on? Am I missing the significance of the "Inequality" column, or is this a factor of the way the DMVs work?

don kitchen
don kitchen
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 29
First off, I just wanted to say great job on these scripts, I am using them to tune my database at the moment and was hoping for some of your expert advice on trying to create as few indexes as possible from the overload of information SQL Server generates from the DMVs.

Take this sample suggestion for a missing index for example (sorry it's an image, this seems to be the best way to keep the formatting):



The equality columns are a no brainer. Now, considering the inequality columns and the included columns, what would the best way to have these 2 suggestions be handled best by 1 index? From what I understand, the order matters for the seek columns (equality and inequality), but not for the included columns. So is it even possible for both of these to be covered well by 1 index?

Also, if I have lots of index suggestions with the same equality and inequality fields but different included fields, is it better to include more fields or go with less included fields? Again, the goal is to create 1 index vs. 3 (if 3 have different included columns).

Any help or guidance here you can provide would be greatly appreciated. I am trying to avoid having index overload and trying to get a handle on exactly the best way to proceed here. Thanks again for your wonderful scripts, they're a big help!



YeshuaAgapao (11/26/2008)

The missing index DMVs this qry is based on often report missing indexes that, if created, would have too many columns... Imagine the performance impact when you go to do an update or insert on a table with a 50 column index!! Therefore, I also added this to the end of the WHERE clause to keep the qry from returning results with a ton of included columns.


You also get a few entries where the long list of includes is only differing by one or has one or two additional or missing include columns. That is why it is not a good idea to blindly copy and paste pre-generated index create statements (I wouldn't do this with DTA either), but rather to merge whenever and as much as possible. One want one key and two includes, another want this one key and one other, but no includes. These can be merged.

YeshuaAgapao
YeshuaAgapao
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 211
I would do (FranchiseID, AppointmentTypeID, ApptDateTime) INCLUDE (CustomerID)

This is because only one inequality column ( =,<=, IN(), BETWEEN) can be range-scanned. If you wanted to follow BOL documentation for the missing index DMVs, which tells you to put all equality and all inequality columns as seeks, then I would put CustomerID at the end of the seek columns - (FranchiseID, AppointmentTypeID, ApptDateTime, CustomerID).

If you got one of those heavy read/write tables and it is being suggested that you need to include the world, then I wouldn't. Go in the code and look for sloppy queries to clean up (SELECT * in particular, but they can also list 20 columns and then use 5). If you can't change the code, then just cover the WHERE clause and JOIN predicates (put non-range-scannable inequality predicates like OR or <> in as includes) so at least all the filtering can be done prior to the bookmark lookup. If the 'include the world' is spread out over multiple results and the higher offending results have just a few, then just include that just a few. Make sure you do have coverage for every WHERE and JOIN predicate though.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search