When to use INDEX HINTS!-VBForums
Results 1 to 20 of 20

Thread: When to use INDEX HINTS!

Hybrid View

  1. #1

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,647

    Lightbulb When to use INDEX HINTS!

    INDEX HINTS are normally not needed - the query optimizer does a great job of determining what index should be used to perform a query.

    I just came across a situation where the wrong index was chosen - and I thought I would share it with the group.

    Some background info - Financial system - LEDGER table.

    Simplied version of columns - FISCALYR (int), TRANSENTRY (int), POENTRY (int), DEBITAMT (money), ENCUMBEREDAMT (money).

    Primary key - clustered - FISCALYR+TRANSENTRY

    TRANSENTRY is a unique number for a fiscal year.

    Alternate key - FISCALYR+POENTRY

    POENTRY is repeated for all rows related to the same PURCHASE ORDER.

    The QUERY used was:

    Code:
    Select -Sum(Encumbamt) From Ledger_T LE
    	Where LE.FiscalYr = 2006
    	and LE.POEntry=917 and LE.TransEntry<>3314
    About 6 rows exist with POENTRY of 917.

    30000 or more rows exist for a fiscal year. We are looking to exclude a single row - based on it's TRANSENTRY value.

    The QUERY OPTIMIZER used the PRIMARY KEY with a TRANSENTRY < 3314 or TRANSENTRY> 3314 execution plan - basically amounting to a TABLE SCAN of 30000 rows. The fact that TRANSENTRY was found in the WHERE clause caused the PRIMARY KEY to be chosen.

    Bad choice - the ALTERNATE KEY on the FISCALYR+POENTRY would amount to just the 6 rows being looked at.

    This is the query with an INDEX HINT - forcing the SQL engine to choose the correct INDEX

    Code:
    Select -Sum(Encumbamt) From Ledger_T LE
    	With (Index (AKLedger1))
    	Where LE.FiscalYr = 2006
    	and LE.POEntry=917 and LE.TransEntry<>3314
    If you are curious how to create ALTERNATE KEY indexes - this is the T-SQL to do so...

    Code:
    CREATE INDEX AKLedger1 on Ledger_T (FiscalYr,POEntry)

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  2. #2
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: When to use INDEX HINTS!

    Just curious if using brackets would cause the optimizer to choose the different index.

    Code:
    Select -Sum(Encumbamt) From Ledger_T LE
    	Where (LE.FiscalYr = 2006 And LE.POEntry=917) And 
                            LE.TransEntry<>3314

  3. #3

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,647

    Re: When to use INDEX HINTS!

    Quote Originally Posted by brucevde
    Just curious if using brackets would cause the optimizer to choose the different index.

    Code:
    Select -Sum(Encumbamt) From Ledger_T LE
    	Where (LE.FiscalYr = 2006 And LE.POEntry=917) And 
                            LE.TransEntry<>3314
    I just checked to make sure - and no it does not.

    My gut reaction was that it would not, as the query must be parsed into objects prior to optimizing anyway...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: When to use INDEX HINTS!

    Quote Originally Posted by szlamany
    Alternate key - FISCALYR+POENTRY
    Code:
    CREATE INDEX AKLedger1 on Ledger_T (FiscalYr,POEntry)
    Not that I think it would matter for the execution plan, but do you really need FISCALYR in this index? The clustered index is always included in other indexes and that means that FISCALYR (and TRANSENTRY) is already included. No need to add it another time since this is not a unique index.

    Also, are you sure the statistics for the indexes are up to date? Try running UPDATE STATISTICS for the indexes.
    Last edited by kaffenils; Sep 21st, 2005 at 12:10 AM.

  5. #5

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,647

    Re: When to use INDEX HINTS!

    All the TRANSENTRY and POENTRY figures start at 1 again, for a given fiscal year - so are not unique in and of themselves.

    We cluster the primary index on FISCALYR so that I/O for data for a given fiscal year is as fast as possible.

    We need to have FISCALYR in each alternate key, since the TRANSENTRY and POENTRY start at 1 each FISCALYR...

    I will try the "UPDATE STATISTICS" - just to see if the execution plan changes.
    Last edited by szlamany; Sep 21st, 2005 at 06:55 PM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: When to use INDEX HINTS!

    Quote Originally Posted by szlamany
    We need to have FISCALYR in each alternate key so that unique rows can be gained access to.
    Are you saying that FISCALYR and POENTRY is a unique value pair in the same way as FISCALYR and TRANSENTRY?
    If it is not unique then my point was that you don't need to add FISCALYR explicitly to the index since SQL Server always adds the columns in the clustered index to any other indexes. Perhaps the execution plan builder gets "confused" when FISCALYR is explicitly added to the index when it already contains FISCALYR from the clustered index, even though it sounds very strange. It's probably just a shot in the dark.

  7. #7

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,647

    Re: When to use INDEX HINTS!

    Quote Originally Posted by kaffenils
    Are you saying that FISCALYR and POENTRY is a unique value pair in the same way as FISCALYR and TRANSENTRY?
    If it is not unique then my point was that you don't need to add FISCALYR explicitly to the index since SQL Server always adds the columns in the clustered index to any other indexes. Perhaps the execution plan builder gets "confused" when FISCALYR is explicitly added to the index when it already contains FISCALYR from the clustered index, even though it sounds very strange. It's probably just a shot in the dark.
    Yes - FISCALYR + POENTRY is a unique value pair.

    Even though you say that the columns of the clustered index are added to any other index, they are not part of the "key" portion of the index. Each leaf is built with FISCALYR+POENTRY as the "key" portion - so that binary lookup of a key can be accomplished.

    I will create a simple new database and set of tables - without the two part key structure and see if the results are the same.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: When to use INDEX HINTS!

    Quote Originally Posted by szlamany
    Even though you say that the columns of the clustered index are added to any other index, they are not part of the "key" portion of the index. Each leaf is built with FISCALYR+POENTRY as the "key" portion - so that binary lookup of a key can be accomplished.
    Of course, only the columns you specify in a unique key are checked for uniqueness, but the clustered index values are also stored with the index, and used in queries, and will be taken into consideration when SQL Server builds an execution plan. At least as far as I know. Hope I haven't misunderstood anything.

  9. #9

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,647

    Re: When to use INDEX HINTS!

    Quote Originally Posted by szlamany
    Yes - FISCALYR + POENTRY is a unique value pair.
    I misunderstood what you asked - FISCALYR + POENTRY is not a unique value pair. FISCALYR+TRANSENTRY is a unique value pair (obviously, since it's the primary clustered index of the LEDGER table).

    The same POENTRY (in my example 917) exists 6 times for FISCALYR 2006. The purpose of the alternate index is not for unique constraint - the purpose of the alternate index is so that this particular SUM() QUERY can be gathered for a particular POENTRY quickly.

    The EXECUTION PLAN - for a WHERE statement of FISCALYR=2006 and POENTRY=917 would always use the ALTERNATE INDEX - as TRANSENTRY is not even mentioned in that WHERE clause. The optimizer looks for an index that fits the WHERE clause.

    When we introduced TRANSENTRY<>3314 into the WHERE clause, we wondered what EXECUTION PLAN might be followed, since now we were referring to columns from two different indexes. I wouild have thought that the =917 would be deemed more specific by the optimizer then the <>3314, causing the ALTERNATE index to be used.

    The optimizer though, stops considering options very quickly, as it does want to start processing the query as soon as possible...

    Maybe I'll delve into Kalen Delaney's book, INSIDE MS SQL Server 2000, when I get to work and see if I can find so definitive reasons for why this happened.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.