Post reply

Missing Index

  • October 10, 2012 at 8:06 am

    #268404

    Hi

    I'm currently trying to dig around to tweak the performance of our database. One of the tasks being removing unused indexes and creating missing indexes.

    Using the sy missing index tables I've identified a missing index that confuses me as an almost identical one already exists and is regularly used. The only difference between the two indexes is the order in which the seek columns appear in the index creation script. Can the order of the columns in the creation script really make a difference as to whether the index is used or not?

    Existing index creation script on DB

    CREATE INDEX IDX_Claim_ColumnA_ColumB ON [Schema].[Table] ([ColumnA],[ColumnB]) INCLUDE ([Id]) ON [INDEXES]

    Apparent Missing Index

    CREATE INDEX IDX_Claim_ColumnB_ColumA ON [Schema].[Table] ([ColumnB],[ColumnA]) INCLUDE ([Id]) ON [INDEXES]

    Thanks for any light you can shed on this.

    Sarah

  • October 10, 2012 at 8:14 am

    #1547673

    Per your example the order does not matter. What is the size of the index and the table? What is the number of reads over writes for that index? Before dropping any index please verify if it is being used by any report (that runs once/twice monthly, quarterly or over a longer time frame).

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • October 10, 2012 at 8:51 am

    #1547706

    Of course the order matters.

    Picture a phone book. The index column order is Last Name, First Name.

    Now if I asked you to find everyone who's last name is 'Smith' you could easily find them. However, to find everyone who's first name is 'Michael' would involve reading every single page i.e. an index scan.

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • October 10, 2012 at 9:09 am

    #1547716

    Absolutely, the order that index columns are specified matters hugely.

    http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

    That said, you need to have a look at the queries that use that table and see if both indexes really are necessary. The missing index feature looks for the absolute best index for each and every individual query, it doesn't take more than one query into account when producing those suggestions. You need to do some deeper analysis yourself, you cannot just take those suggestions and implement them blindly.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 10, 2012 at 9:50 am

    #1547735

    Hi

    Thanks for your replies. Every day is a school day!

    I have been using the missing index functionality as a starting point and then doing some more digging to see if it is really necessary. On that I have discovered that we are only reorganising indexes nightly not rebuilding them when fragmentation gets above 30%. Eureka! I'm going to sort that one out straight away which may improve things drastically before I start looking at which indexes are actually missing.

    Thanks again

    Sarah

  • October 10, 2012 at 11:07 am

    #1547754

    It's also possible you need the other index instead, rather than in addition to, the one you have know.

    You always need to look at the usage stats too when looking at the missing index stats.

    And, as noted, take SQL's recommendations with extreme caution. SQL always wants an index, even if it's not really justified when you consider all information about the table.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • October 29, 2012 at 11:32 pm

    #1553997

    hi , check this if u feel it 's use full to You

    SELECT

    TOP 10

    [Total Cost]

    = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)

    , avg_user_impact

    , TableName = statement

    , [EqualityUsage] = equality_columns

    , [InequalityUsage] = inequality_columns

    , [Include Cloumns] = included_columns

    FROM

    sys.dm_db_missing_index_groups g INNER

    JOIN sys.dm_db_missing_index_group_stats s

    ON s.group_handle = g.index_group_handle INNER

    JOIN sys.dm_db_missing_index_details d

    ON d.index_handle = g.index_handle

    ORDER

    BY [Total Cost] DESC;

    Thanks
    Naga.Rohitkumar

  • October 30, 2012 at 3:01 am

    #1554043

    I know it's already been covered, but just to put in cleartext:

    The reason why you need to be careful about which indexes to add is that adding indexes can actually harm performance if it's not the indexes you really need and are going to use. The indexes themselves need to be updated and maintained, so if you're not really using them then you're just wasting time and resources doing work that's not good for anything.

    Kind regards,

    Vegard Hagen
    Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
    @vegard_hagen on Twitter
    Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

    It is better to light a candle than to curse the darkness. (Chinese proverb)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply