Results 1 to 20 of 20

Thread: When to use INDEX HINTS!

  1. #1

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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
    Connecticut
    Posts
    18,263

    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
    Connecticut
    Posts
    18,263

    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
    Connecticut
    Posts
    18,263

    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
    Connecticut
    Posts
    18,263

    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

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

    Re: When to use INDEX HINTS!

    I created a table with the same columns and populated it with test data. 30000 records per year, years from 2000 to 2006. Transentry values from 1 to 30000 and random poentry between 1 and 1000. The two money columns are random values between 1 and 100.

    When I executed the query it used the AKLedger1 index without me having to specify it as a hint, but this is probably because my samle data is too different from your data.

  11. #11

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: When to use INDEX HINTS!

    INSIDE MS SQL SERVER 2000 - Kalen Delaney - page 827...

    Index Selection

    During the second phase of query optimization, index selection, the query optimizer determines whether an index exists for a sargable clause [sargable=search argument], assesses the index's usefulness by determining the selectivity of the clause (that is how many rows will be returned), and estimates the cost of finding the qualifying rows. An index is potentially useful if it's first column is used in the search argument.
    That's the rub here, in my opinion - FISCALYR is the first column in each index...

    But it goes on to say that if the INDEX contains all the columns in the QUERY - the SELECT and WHERE portions - then you get an added benefit, the "covering" index can be used without ever visiting the data pages. That means that if I add TRANSENTRY and ENCUMBEREDAMT to the INDEX - as third and fourth columns - the INDEX itself can be used to garner the AGGREGATE SUM of the ENCUMBEREDAMT - that is very good to know.

    It also goes on to say that INDEX STATISTICS (used after the POTENTIALLY USEFUL INDEX STEP) are updated automatically by the query optimizer if they are found to be out-of-date.

    It also goes on to say that if the pages are already in the memory cache, then different choices are made.

    With all that said (and there are about 25 pages in the book about this subject) - I believe that the INDEX HINT is appropriate in this case to ensure that the proper index is used.
    Last edited by szlamany; Sep 21st, 2005 at 08:39 AM.

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

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

    Re: When to use INDEX HINTS!

    Quote Originally Posted by szlamany
    With all that said (and there are about 25 pages in the book about this subject) - I believe that the INDEX HINT is appropriate in this case to ensure that the proper index is used.
    25 pages!!! Can't be many perople that really know how all about how query plans are calulated.

  13. #13

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: When to use INDEX HINTS!

    Quote Originally Posted by kaffenils
    25 pages!!! Can't be many perople that really know how all about how query plans are calulated.
    It is a truly great book, and she is an incredible expert on the SQL engine.

    Back in the 1980's we were forced to develop our own ISAM database engines and objects (on mainframes!) - so I enjoy reading this stuff for nostalgia alone!

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

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

    Re: When to use INDEX HINTS!

    Quote Originally Posted by szlamany
    It is a truly great book, and she is an incredible expert on the SQL engine.

    Back in the 1980's we were forced to develop our own ISAM database engines and objects (on mainframes!) - so I enjoy reading this stuff for nostalgia alone!
    Guess I have to buy that book.

    I know there are many factors that is taken into consideration when SQL Server builds the execution plan. On a SQL Server Bootcamp we were told that SQL Server may use a table scan instead of an existing index if more than 1% of the rows are affected by the query. I have never tested it, but I think think I will tomorrow.

    Another thing they told us, even though it is very useless, is that the "WA" in statistics with names starting with "_WA" stands for Washington. This is where the SQL Server development team is located. Useless as I said.

  15. #15

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: When to use INDEX HINTS!

    I'm kind of surprised that only two other people have posted in this thread...

    Haven't any of the other DB people here used HINTS before?

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

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

    Re: When to use INDEX HINTS!

    Again just curious...

    What are the differences in the execution stats (estimated i/o cost, estimated cpu costs etc.) between the two statements? Significant?

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

    Re: When to use INDEX HINTS!

    Quote Originally Posted by brucevde
    Again just curious...

    What are the differences in the execution stats (estimated i/o cost, estimated cpu costs etc.) between the two statements? Significant?
    It's very significat. I looped the select statement 10 times with both indexes. When using the correct index it ran in "0" seconds, but when I used the other index it took 4 seconds. That's what I call significant.

  18. #18

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: When to use INDEX HINTS!

    What made me notice it in the first place, I was on my laptop and testing the entry of a PO # in a flexgrid. We immediately call a SPROC with that query in it to determinethe amount of ENCUMBRANCE still available. SPROC runs very fast - returns a figure immediately. I'm sure that I added the POENTRY ALTERNATE INDEX just for this SPROC months ago...

    I noticed it was including the "row I was on" in the figure, so I added the TRANSENTRY<>x to the WHERE clause and got a noticeable pause in the return of the figure from SQL. The correct figure coming back but with a "pause" in the movement along the grid.

    The customer would have noticed that also...

    That's when I got into QA and started looking at EXECUTION PLAN - something I hardly ever do - but will do much more often now...

    Also - working with live-production data in our development shop makes a huge difference in testing for speed and user-experience...

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

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

    Re: When to use INDEX HINTS!

    Quote Originally Posted by szlamany
    That's when I got into QA and started looking at EXECUTION PLAN - something I hardly ever do - but will do much more often now...
    The problem, speaking for myself, is that the execution plan for large queries (joining multiple tables, different join types, lots of where clauses etc. etc.) can be very hard to analyze, at least in a reasonable amount of time.

    Have you ever used the Query/Index Optimizer (in Profiler)? I haven't, and perhaps that is silly of me. Guess I'm just a control freak.

  20. #20
    Hyperactive Member
    Join Date
    Oct 2006
    Location
    USA
    Posts
    476

    Re: When to use INDEX HINTS!

    Quote Originally Posted by szlamany
    I'm kind of surprised that only two other people have posted in this thread...

    Haven't any of the other DB people here used HINTS before?

    No, but the person who programmed the proc that I'm fixing did! His hints don't work in 2005 and now I have to make them work, even though I don't think they're really necessary. Grrr. I don't even really know how to write hints in 2005; his stuff is pooing all over the place.

Posting Permissions

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



Click Here to Expand Forum to Full Width