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
Scott Herbert-214404
Scott Herbert-214404
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 245
[quote]don kitchen (1/15/2009)
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. Tongue 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])
mike mcneer
mike mcneer
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 209
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!
srihari nandamuri
srihari nandamuri
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 60
/*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 migroups
INNER JOIN sys.dm_db_missing_index_group_stats migroupsstats ON migroupsstats.group_handle = migroups.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON migroups.index_handle = mid.index_handle
order by 1

Srihari Nandamuri

Comments please....

Srihari Nandamuri
Manie Verster
Manie Verster
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: 1637 Visits: 1022
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

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
jmscotty-580195
jmscotty-580195
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 71
Thank you, thank you, thank you. I used this script and just became a hero at work. w00t
YeshuaAgapao
YeshuaAgapao
SSC Eights!
SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)

Group: General Forum Members
Points: 923 Visits: 211
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 work

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



Vincent Central
Vincent Central
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 81
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.
Maramor
Maramor
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 338
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.
alin.selicean
alin.selicean
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 255
Hi folks

I just came across this script and one thing that puzzled me was this piece of code:
   (CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.user_seeks)+CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.unique_compiles))*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_total_user_cost)*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_user_impact/100.0) AS Score



I usually saw the seeks added up with the scans, not with the unique compiles. Is this intended like this, is it a typo (+ instead of *) or it should have been user_scans instead of unique_compiles ?

Thanks,
Alin
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13878 Visits: 885
Thanks for the script.
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