Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

The Ultimate Missing Index Finder Expand / Collapse
Author
Message
Posted Monday, October 6, 2008 11:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 23, 2010 7:04 AM
Points: 18, 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.
Post #581266
Posted Monday, October 6, 2008 11:23 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 21, 2013 4:37 PM
Points: 701, 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.


Post #581272
Posted Thursday, October 9, 2008 5:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:36 PM
Points: 177, Visits: 244
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?
Post #583657
Posted Friday, October 10, 2008 10:40 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 21, 2013 4:37 PM
Points: 701, 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..



Post #584125
Posted Friday, October 10, 2008 5:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:36 PM
Points: 177, Visits: 244
thanks, that's (the non-equality bit) an interesting piece of trivia.
Post #584304
Posted Tuesday, November 25, 2008 1:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 15, 2012 1:47 PM
Points: 11, 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

Post #608671
Posted Wednesday, November 26, 2008 9:26 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 21, 2013 4:37 PM
Points: 701, 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.



Post #609241
Posted Thursday, January 15, 2009 9:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 22, 2012 6:10 AM
Points: 2, 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?
Post #637309
Posted Thursday, January 15, 2009 9:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 22, 2012 6:10 AM
Points: 2, 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.
Post #637319
Posted Thursday, January 15, 2009 10:03 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 21, 2013 4:37 PM
Points: 701, 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.



Post #637342
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse