SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Jesse Roberge / The Ultimate Missing Index Finder / Latest Posts

RE: The Ultimate Missing Index Finder

Maramor — Mon, 07 Jan 2013 13:22:18 GMT

Is there a way to modify the procedure to be outside the database, perhapse another argument to select the database. I have a admin database that I store reports and procedures in and then run those against the databases I need results for. This keeps custom objects outside of applications where it would sometimes void warranty and support agreements.

RE: The Ultimate Missing Index Finder

Vincent Central — Tue, 19 Jan 2010 10:54:26 GMT

After a year of using this in production (see my post in Nov. 2008) and comparing results with the Performance Dashboard report on missing indexes, I don't see any advantage to this proc over the dashboard report, nor do I really understand why they often get different results.

RE: The Ultimate Missing Index Finder

YeshuaAgapao — Thu, 02 Jul 2009 10:53:52 GMT

This just peeks into the dynamic management views and does the joins for you, possibly replaces the comma delimiters with another of you chossing (if you pick something other than the default comma) for csv export, computes a ranking score, and sorts it. The indexes that come out will match sys.dm_db_missing_index_details . The numbers come from sys.dm_db_missing_index_group_stats . The query optimizer puts the missing indexes in when it finds that it has to scan the table. I don't know what causes them to get taken out. resetting the missing index DMVs 'may' fix the problem of the false positives, if they are not being taken out correctly. Otherwise I don't know what will get around the problem.DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR) appears to workbut..DBCC SQLPERF('sys.dm_db_missing_index_details',CLEAR);DBCC SQLPERF('sys.dm_db_missing_index_groups',CLEAR);DBCC SQLPERF('sys.dm_db_missing_index_group_stats',CLEAR);Does ot appear to work.The only way to reset the missing_index DMVs appears to be restarting the SQL Server instance.Here are other limitations of the missing index DMVs from BOL (The 500-limit, and the non-coverage of trivial plans - queries without joins - are the biggies)*It is not intended to fine tune an indexing configuration. (Only good for quick and dirty tuning with a small timeframe, though it spots terrible offenders pretty good)*It cannot gather statistics for more than 500 missing index groups. (Requires a DMV reset; only by server restart; to get around)*It does not specify an order for columns to be used in an index. (You can derive this a little by merging entries)*For queries involving only inequality predicates, it returns less accurate cost information.*It reports only include columns for some queries, so index key columns must be manually selected.*It returns only raw information about columns on which indexes might be missing.*It does not suggest filtered indexes.*It can return different costs for the same missing index group that appears multiple times in XML Showplans.*It does not consider trivial query plans.These are the 3 main ways I use to tune indexes from quickest/least accurate to most time consuming/most accurate; accuracy affects primarily include column selection and effectiveness of composite indexes):*The missing index DMVs (Util_Missing Indexes)*Analyze query plans of SQL profiler trace worst offender aggregate reports*Trace and crawl through the application, weighing by user-traffic if possible to know.Another think that can cause an otherwise perfect index to get ingnored, especially in databases without foreign key constraints (at least in the Dev/QA environments): incompatible types in JOIN and WHERE predicates. If you provide a int predicate for a varchar column (where the varchar values is all int-castable), it will want to cast the varchar to int, wrecking any possibility of index usage. Casting your predicates will get around it for a quick-fix, but having your FK-links be identical in type (as required by FK constraints) will eliminate that problem, at least for JOINs. I don't recall this scenario adding missing index entries and creating 'false postives' But it is something that the missing index DMVs won't find.

RE: The Ultimate Missing Index Finder

jmscotty-580195 — Thu, 02 Jul 2009 07:20:20 GMT

Thank you, thank you, thank you. I used this script and just became a hero at work. :w00t:

RE: The Ultimate Missing Index Finder

Manie Verster — Thu, 09 Apr 2009 08:06:15 GMT

I'm afraid all the results in your query is indexes that already exist in my database.Am I missing something? I'll have to go through your query step by step to see what exactly it is that it does but time is tight just now.:w00t:

RE: The Ultimate Missing Index Finder

srihari nandamuri — Mon, 26 Jan 2009 06:12:13 GMT

/*1)Below query is to Findout the Missing Indexes on the tables.This will generate the Create Index script based on the queries that are executed on the server by checking the columns used in equal/inequal /where clause. */SELECT statement, migroupsstats.avg_total_user_cost as AvgTotalUserCostThatCouldbeReduced, migroupsstats.avg_user_impact as AvgPercentageBenefit,'CREATE INDEX missing_IX_' + CONVERT (varchar, object_name(mid.object_id)) + CASE WHEN mid.equality_columns IS NOT NULL THEN '_' ELSE '' END + ISNULL (replace(replace(replace(mid.equality_columns,', ','_'),']',''),'[',''),'') + CASE WHEN mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END + ISNULL (replace(replace(replace(mid.inequality_columns,', ','_'),']',''),'[',''), '')+ ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')+ ')' + CASE WHEN mid.included_columns IS NOT NULL THEN + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') ELSE '' END AS create_index_statement FROM sys.dm_db_missing_index_groups migroupsINNER JOIN sys.dm_db_missing_index_group_stats migroupsstats ON migroupsstats.group_handle = migroups.index_group_handleINNER JOIN sys.dm_db_missing_index_details mid ON migroups.index_handle = mid.index_handleorder by 1Srihari Nandamuri

RE: The Ultimate Missing Index Finder

mike mcneer — Wed, 21 Jan 2009 09:35:49 GMT

Could you give a quick rundown on how to interpret the results properly. I am not entirely clear on how to read these the best way.Thanks!

RE: The Ultimate Missing Index Finder

Scott Herbert-214404 — Thu, 15 Jan 2009 14:58:55 GMT

[quote][b]don kitchen (1/15/2009)[/b][hr]It's probably because you created an index against the AnalysisHolding table when the result was complaining about the Notice table.Yep, that's embarrassing. :P Copy and paste error from two examples of the same problem. The index I meant to post was CREATE NONCLUSTERED INDEX [IX_Notice_NoticeStageID] ON [Analysis].[Notice] ( [NoticeStageID] ASC)INCLUDE ( [ID],[AnalysisID],[FileID])

RE: The Ultimate Missing Index Finder

YeshuaAgapao — Thu, 15 Jan 2009 10:03:45 GMT

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.

RE: The Ultimate Missing Index Finder

don kitchen — Thu, 15 Jan 2009 09:47:38 GMT

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):[img]http://www.donkitchen.com/code/missingindex.png[/img]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 [b]same equality and inequality fields[/b] 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![quote][b]YeshuaAgapao (11/26/2008)[/b][hr][quote]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.[/quote]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.[/quote]

RE: The Ultimate Missing Index Finder

don kitchen — Thu, 15 Jan 2009 09:38:20 GMT

It's probably because you created an index against the AnalysisHolding table when the result was complaining about the Notice table.[quote][b]Scott Herbert (10/9/2008)[/b][hr]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.496496I've then added the indexCREATE 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?[/quote]

RE: The Ultimate Missing Index Finder

YeshuaAgapao — Wed, 26 Nov 2008 09:26:57 GMT

[quote]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.[/quote]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.

RE: The Ultimate Missing Index Finder

Vincent Central — Tue, 25 Nov 2008 13:31:13 GMT

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. [code] '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[/code][b]EDIT:[/b]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.[code]AND isnull(Len(included_columns)-Len(Replace(included_columns,',',''))+1,1)<10--Vincent yovincent@hotmail.com[/code]

RE: The Ultimate Missing Index Finder

Scott Herbert-214404 — Fri, 10 Oct 2008 17:04:53 GMT

thanks, that's (the non-equality bit) an interesting piece of trivia.

RE: The Ultimate Missing Index Finder

YeshuaAgapao — Fri, 10 Oct 2008 10:40:56 GMT

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

RE: The Ultimate Missing Index Finder

Scott Herbert-214404 — Thu, 09 Oct 2008 17:24:18 GMT

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.496496I've then added the indexCREATE 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?

RE: The Ultimate Missing Index Finder

YeshuaAgapao — Mon, 06 Oct 2008 11:23:25 GMT

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.

RE: The Ultimate Missing Index Finder

arbarnhart-780010 — Mon, 06 Oct 2008 11:16:59 GMT

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.

RE: The Ultimate Missing Index Finder

YeshuaAgapao — Mon, 06 Oct 2008 11:06:15 GMT

Are you using -x option for the startup options for the service (or when starting manually from the command line)? It disables some DMV data collection (not sure if it affects sys.dm_db_missing_index_* dmvs but I think it would). It also breaks SET STATISTICS TIM ON and SET STATISTICS IO ON.-x disables all these (from BOL) to squeeze a few percentage points of performance:SQL Server performance monitor countersKeeping CPU time and cache-hit ratio statisticsCollecting information for the DBCC SQLPERF commandCollecting information for some dynamic management viewsMany extended-events event points

RE: The Ultimate Missing Index Finder

arbarnhart-780010 — Mon, 06 Oct 2008 11:00:52 GMT

I am running my own server instance on my machine; I have total control.

RE: The Ultimate Missing Index Finder

arbarnhart-780010 — Mon, 06 Oct 2008 10:58:33 GMT

Still nothing.

RE: The Ultimate Missing Index Finder

YeshuaAgapao — Mon, 06 Oct 2008 10:56:39 GMT

If you are on a shared hosting box, you can ask one of the admins to put this proc into your database for you with an 'EXECUTE AS' clause that impersonates to an account with the 'VIEW SERVER STATE' option. They may want to review the proc (it uses DB_ID() to restrict to the current DB) first because they probably won't want you to be able to peek into other customers' databases.

RE: The Ultimate Missing Index Finder

YeshuaAgapao — Mon, 06 Oct 2008 10:48:06 GMT

I've recreated the problem. It returns no results if you do not have the 'VIEW SERVER STATE' permission. Looked it up in the sys.dm_db_missing_index_* entries in BOL. server-level sysadmin has that right bundled in. Not sure about which of the others. dbo (database owner) does not (it does give 'VIEW DATABASE STATE' though, which is insufficient for sys.dm_db_missing_index_*). I'll update the documentation.

RE: The Ultimate Missing Index Finder

arbarnhart-780010 — Mon, 06 Oct 2008 10:40:53 GMT

That fix does nothing for me; still zero records from a datbase that is very suspect as far as optimization goes. I am not a DBA and this database is the product of a group of developers running rampant. Are there some settings I need to adjust so it captures this information?

RE: The Ultimate Missing Index Finder

YeshuaAgapao — Mon, 06 Oct 2008 10:33:04 GMT

I'll put up a fix. It is working on my system with the missing 'sys.' ,which is probably why I didn't catch it. It is inconsistent with the rest of the code so it is a bug. I'm logged in a non-'sa' accoutn with the sysadmin server role. I can see if it breaks on a non sysadmin account.

RE: The Ultimate Missing Index Finder

Ronnie.Doggart — Mon, 06 Oct 2008 08:57:08 GMT

Had to modify it to get it to work on my system: JOIN sys.dm_db_missing_index_details ON sys.objects.object_id=[b]dm_db_missing_index_details.object_id[/b]modified to be JOIN sys.dm_db_missing_index_details ON sys.objects.object_id=[b]sys.dm_db_missing_index_details.object_id[/b]

RE: The Ultimate Missing Index Finder

arbarnhart-780010 — Mon, 06 Oct 2008 06:06:41 GMT

What steps do you need to take to prepare a database for this to return anything? I run it on our database, which has over 400 tables and over 1000 queries many of which have never had any sort of optimization applied and it returens no records. I expected to get inforrmation overload back.

The Ultimate Missing Index Finder

YeshuaAgapao — Sat, 02 Aug 2008 08:33:56 GMT

Comments posted to this topic are about the item [B]The Ultimate Missing Index Finder[/B]