none
How to force Index Scan? RRS feed

  • Question

  • I have a complex query joining several large tables.  The query plan shows its doing index seeks for all the joins, but I suspect that for some of these, an index scan will be faster (since the entire table in some cases is being read)

    Is there any way I can force an index scan to test this out?  I know you can use FORCESEEK to force a seek, but can you do the reverse?  Thanks.
    Tuesday, June 9, 2009 5:03 PM

Answers

  • If you know the index you want to use, you can add a hint:

    SELECT whatever FROM MyTable WITH (INDEX (IndexToUse))

    But be very sure about what you're doing... oftentimes SQL chooses a path you may not agree with for a good reason.  Compare the plans and read operations of the plan that SQL comes up with compared to what you are forcing it to do.

    Here's more info:

    http://msdn.microsoft.com/en-us/library/ms187373(SQL.90).aspx


    --Brad
    • Marked as answer by Mantros Group Tuesday, June 9, 2009 7:29 PM
    Tuesday, June 9, 2009 5:10 PM
    Moderator
  • You can force a table scan by specifying index(0) as an index hint.  I don't know how to force a specific index scan, though.
    Michael Asher
    • Marked as answer by Mantros Group Tuesday, June 9, 2009 7:29 PM
    Tuesday, June 9, 2009 5:20 PM
  • Adam,

    I don't see how a million separate seeks that wind up reading the whole table is faster than a single scan.  You can say the optimizer is smarter than us, but lots of times I've added a hint and had the query speed up tons.

    In this query, you're right I'm seeign a lot of nested joins.  But they all say 0% cost.   Almost all the cost is (85%) in the index seeks.  There's 7% in one hash match, and the rest is in parallelism operators.

    Change the join to use a merge or hash join and you should see a scan or at least a faster query.  The optimizer is finding it faster to seek the rows using a index nested loop, this can be for a lot of reasons, maybe your statistics are out-of-date and the optimizer is basing descisions off a much smaller number of estimated rows.  Are you reguruly maintaing the indexes and updating stats?  Have you looked at the estimated rows to make sure they make sense?  You have to remember that the optimizer is a tool that can base decisions off of what it believes to be true, not what you know to be true.  Also, just because the index seek uses 85% of the batch does not mean that it is a problem, it just means that part took more time than the rest of the showplan operators.  For example, If a table has 1000 rows and the index seek takes 85% of the cost, this is not really a problem?  I guess what I am asking, is why is this a problem... is it because this process is taking an extreme amount of time to complete?
    http://jahaines.blogspot.com/
    • Marked as answer by Mantros Group Tuesday, June 9, 2009 7:52 PM
    Tuesday, June 9, 2009 7:46 PM
    Moderator

All replies

  • If you know the index you want to use, you can add a hint:

    SELECT whatever FROM MyTable WITH (INDEX (IndexToUse))

    But be very sure about what you're doing... oftentimes SQL chooses a path you may not agree with for a good reason.  Compare the plans and read operations of the plan that SQL comes up with compared to what you are forcing it to do.

    Here's more info:

    http://msdn.microsoft.com/en-us/library/ms187373(SQL.90).aspx


    --Brad
    • Marked as answer by Mantros Group Tuesday, June 9, 2009 7:29 PM
    Tuesday, June 9, 2009 5:10 PM
    Moderator
  • Wind direction, Kent?  North by Northwest I think.

    Brad, thanks for the reply.  I know its using the right index in this case (one on a FK int value), but to fill the query, it has to retrieve every single FK from a table of 1.5 million rows.  To me it seems like it would be faster to just scan that index for all the values, rather than doing 1.5 million lookups.  Is there any way I can test that tho ?
    Tuesday, June 9, 2009 5:16 PM
  • You can force a table scan by specifying index(0) as an index hint.  I don't know how to force a specific index scan, though.
    Michael Asher
    • Marked as answer by Mantros Group Tuesday, June 9, 2009 7:29 PM
    Tuesday, June 9, 2009 5:20 PM
  • It works...but that simply forces a particular join order.  I don't see how you could use it to turn a seek into a scan.
    Michael Asher
    Tuesday, June 9, 2009 7:10 PM
  • I suppose if you really want to force an index scan you can try (1) hint the index and (2) make the query not sargable.  This sounds awful; it is likely that you will be doing both (1) the index scan plus (2) either the rid lookup or the key lookup; if #2 happens this will be far slower than a table scan.

    If the query is joined by a.id = b.id
    try something like cast(cast(a.id as varchar(12)) as int) = cast(cast(b.id as varchar(12)) as int)
    this should increase should really get the query to run slow.
    Kent Waldrop
    Tuesday, June 9, 2009 7:29 PM
  • Guess its not possible.  Thanks anyway.
    Tuesday, June 9, 2009 7:29 PM
  • Seeks are not the problem here.  The optimizer has deemed a seek to be cheaper and more efficient than a table/index scan.  You may be better off forcing the join, although I dont like too. The optimizer made its choices for a reason and is usually smarter than DBA/developers.  Are you seeing a lot of nested loop joins?  If so, this is where you can gain the most performance by forcing a merge (great if data is sorted) or a hash join.  A nested loop join evaluates an expression for every row in the outer table, which can be expensive depending on the proportion of the data being sifted through. There are pros and cons to each type of join and each one should be evaluated.

    Here is a link that will help:
    http://msdn.microsoft.com/en-us/library/ms191318(SQL.90).aspx

    Edit: here is the link to join hints http://msdn.microsoft.com/en-us/library/ms173815.aspx.  You are more likely to see a index scan if you force a particular join.

    http://jahaines.blogspot.com/
    Tuesday, June 9, 2009 7:29 PM
    Moderator
  • Adam,

    I don't see how a million separate seeks that wind up reading the whole table is faster than a single scan.  You can say the optimizer is smarter than us, but lots of times I've added a hint and had the query speed up tons.

    In this query, you're right I'm seeign a lot of nested joins.  But they all say 0% cost.   Almost all the cost is (85%) in the index seeks.  There's 7% in one hash match, and the rest is in parallelism operators.
    Tuesday, June 9, 2009 7:34 PM
  • Again, it would help if you were forthcoming with some information; would you care to share some of the plan text?
    Kent Waldrop
    Tuesday, June 9, 2009 7:38 PM
  • The XML plan is over 500K.  Let me see if I can cut out some of the joins and still reproduce the problem, then i'll post it.   Thanks Kent.
    Tuesday, June 9, 2009 7:45 PM
  • Adam,

    I don't see how a million separate seeks that wind up reading the whole table is faster than a single scan.  You can say the optimizer is smarter than us, but lots of times I've added a hint and had the query speed up tons.

    In this query, you're right I'm seeign a lot of nested joins.  But they all say 0% cost.   Almost all the cost is (85%) in the index seeks.  There's 7% in one hash match, and the rest is in parallelism operators.

    Change the join to use a merge or hash join and you should see a scan or at least a faster query.  The optimizer is finding it faster to seek the rows using a index nested loop, this can be for a lot of reasons, maybe your statistics are out-of-date and the optimizer is basing descisions off a much smaller number of estimated rows.  Are you reguruly maintaing the indexes and updating stats?  Have you looked at the estimated rows to make sure they make sense?  You have to remember that the optimizer is a tool that can base decisions off of what it believes to be true, not what you know to be true.  Also, just because the index seek uses 85% of the batch does not mean that it is a problem, it just means that part took more time than the rest of the showplan operators.  For example, If a table has 1000 rows and the index seek takes 85% of the cost, this is not really a problem?  I guess what I am asking, is why is this a problem... is it because this process is taking an extreme amount of time to complete?
    http://jahaines.blogspot.com/
    • Marked as answer by Mantros Group Tuesday, June 9, 2009 7:52 PM
    Tuesday, June 9, 2009 7:46 PM
    Moderator
  • Yes, the query is taking 8 minutes to complete.  Is there a way I can update stats just for the columns or tables used in this particular query to see what happens?  Or do I need to update the whole DB at once?
    Tuesday, June 9, 2009 7:52 PM
  • You can use update statistics to update the statistics.  You may need to create statistiscs to help improve query performance. Also, as an FYI you can run your query through the DTA and it will tell you if you need to create statistics and if there are better indexes for your query.  Also, if you query is taking 8 minutes to process there may be additional optimization techniques that we can help you with, but you would have to get us psuedo code.

    USE MyDB;
    GO
    UPDATE STATISTICS dbo.MyTable;
    GO
    


    Update Stas:
    http://msdn.microsoft.com/en-us/library/ms187348.aspx

    Create Stats:
    http://msdn.microsoft.com/en-us/library/ms188038.aspx

    Creating Stats to help query performance:
    http://msdn.microsoft.com/en-us/library/ms190397.aspx

    Edit: forgot to mention, if you can spare it, I would do a full scan, as this gives the optimizer the best information.


    http://jahaines.blogspot.com/
    Tuesday, June 9, 2009 8:03 PM
    Moderator
  • Would it be possible to break this query in smaller chunks? 8 minutes is a really long time for any query to run. also, if you have a single primary-key value in the resulting dataset, you could create an indexed view to "materialize" the join. It would cost a lot of disk space (and insert/update time) but could make the reads a lot faster.

    but returning to the subject of this post, as of SQL 2008 R2 SP1 it IS possible to force an index scan. see the new "forcescan" hint at http://msdn.microsoft.com/en-us/library/ms187373(v=sql.105).aspx

    cya,

    http://thelonelydba.wordpress.com/


    Wednesday, October 31, 2012 7:16 PM