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 Thursday, January 15, 2009 2:58 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
[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. :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])
Post #637621
Posted Wednesday, January 21, 2009 9:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 2, 2012 10:19 AM
Points: 99, 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!
Post #640887
Posted Monday, January 26, 2009 6:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 19, 2012 6:44 AM
Points: 273, Visits: 56
/*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
Post #643377
Posted Thursday, April 9, 2009 8:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 12:18 AM
Points: 1,205, Visits: 924
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.

Manie 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)
Post #694023
Posted Thursday, July 2, 2009 7:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:50 AM
Points: 5, Visits: 69
Thank you, thank you, thank you. I used this script and just became a hero at work.
Post #746238
Posted Thursday, July 2, 2009 10:53 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
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.



Post #746476
Posted Tuesday, January 19, 2010 10:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 15, 2012 1:47 PM
Points: 11, 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.
Post #849938
Posted Monday, January 7, 2013 1:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:04 AM
Points: 47, Visits: 313
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.
Post #1403846
Posted Wednesday, July 9, 2014 6:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 3, 2014 5:57 AM
Points: 48, Visits: 225
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
Post #1590706
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse