SQL SERVER – Force Index Scan on Table – Use No Index to Retrieve the Data – Query Hint

Recently I received the following two questions from readers and both the questions have very similar answers.

Question 1: I have a unique requirement where I do not want to use any index of the table; how can I achieve this?

Question 2: Currently my table uses clustered index and does seek operation; how can I convert seek to scan?

First of all, I am not going to analysis their need of why, in fact, they want to convert seek to scan or use no index here. The requirement is strange as using no index or scanning large table may reduce the performance instead of improving it. We are not going to discuss the ‘It Depends’ condition where scan can be better than seek or seek can be better than scan. The User has to take his/her own decision after looking at performance which one is better for its need.

Now, let us see how we can force the use of no index or force a scan operation on clustered index. Scan operation on clustered index is, in fact, scanning the original table. When querying table sys.indexes and closely looking at the column type, it is found to contain various values. Value 0 (Zero) in column type indicates that it is a heap. A heap is defined as a table without index. We will use the value 0 on the table with index to simulate the effect of no index or force index scan where index seek is happening.

If any object has a clustered or nonclustered index on it, it will not have entry of index_id =0. The value 0(Zero) for index_id indicates the heap. You can find further details at sys.indexes.

Now, let us run the following query, and please note the usage of the Index hint of INDEX(0), it will force query to not to use any index or behave like a query on heap. The value which is passed to the function INDEX is the value of index_id.

USE AdventureWorks
SELECT ProductID, ProductNumber, Color
FROM Production.Product
WHERE ProductID < 500
SELECT ProductID, ProductNumber, Color
FROM Production.Product WITH (INDEX(0))
WHERE ProductID < 500

Let us check the execution plan for this query batch, and we should able to see in the second query that the seek is converted to scan. This answers the question of how to convert seek to scan?

Now, once again, let us run the following similar query, where we can see how INDEX(0) is behaving.

USE AdventureWorks
SELECT ProductID, ProductNumber
FROM Production.Product
WHERE ProductNumber = 'CR-7833'
SELECT ProductID, ProductNumber
FROM Production.Product WITH (INDEX(0))
WHERE ProductNumber = 'CR-7833'

It is clear from result that original index usage is replaced with Clustered Index Scan. Also, from the first example, we have seen that how it is forcing scan even though seek is possible. Well, this indicates that the basic index was not used. I would rather say that we get a result that is same as the result when there is no index on the table.

The strong argument here is that even though you use Index(0) or Index(1) in this later example, there is no difference in the result. The logical reasoning we can reach after considering the first example is that if the use of Index(1) was forcing index seek due to any reason, using Index(0) will eliminate the seek part and clustered index will be scanned just like it would have happened in the case of Heap. If table has clustered index, it is ordered in a certain way and it cannot be used like heap after having clustered index on it; however just like heap, the scan is pushed over it.

I want to know if community had ever faced the above two questions and also your take on these. Moreover, I have a gut feeling that there is something missing in the above explanation, and I request you all to help me complete the explanation.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

9 thoughts on “SQL SERVER – Force Index Scan on Table – Use No Index to Retrieve the Data – Query Hint

  1. Hi Pinal,

    In second example of using “where productNumber = ‘CR-7833′ “.
    And it using the seek on nonclustered index and another one using scan clusteted index.

    Is it possible to use seek Clustered index with same query?

  2. In what kind of scenario would you want to deliberately not use any index on a table? I’m just not sure what the use of this trick is.

    In fact, in your examples the query cost when avoiding the index is quite a bit worse than with the index – an example where it reduced might have been more convincing.

  3. Use reverse(reverse(‘string’)) to eliminate index options. Substring would probably do it too but don’t have time to test.

  4. I can confirm that I had a simmilar issue. A quite complex query that I thought was running fast enough. By mistake a dropped the index in one of the tables and then run in half the time. I now have a with (INDEX(0)) to not use any index on that table and does a table scan.

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #020 | SQL Server Journey with SQL Authority

  6. I faced this question myself in the scenario, where a table was being used optimally in a query when it was under an index scan. However as time would go by, its stats would go out of date, and the optimizer would start computing an index seek instead of an index scan. It was at this time that the query started performing significantly poorly, because in reality the query needed almost all the data from the table, and should have performed in index scan. But because the stats were out of date (and the AUTO UPDATE STATS ASYNC was set, which means the stats updated later, and the query was run first with the older stats information), the optimizer concluded an index seek, and it went through the entire table performing index seeks, essentially slowing down the query.

    In such a condition, forcing the query to perform an index scan instead of seek ensured a consistent high performance

  7. It’s been a while since you wrote this. There is easy to reproduce scenario when index scan performs much better than index seek.

    1. Table needs to be fairly big
    2. There needs to be clustered index
    3. The index needs to be well fragmented. Don’t know the threashold, I was doing my tests on >97% fragmentation.
    4. Query must return most of the rows from the table
    5. Predicate on clustered key column must be in the query.

    The last condition will cause index seek.

    Now if you remove predicate from the query and add NOLOCK or TABLOCK hint, the QO will choose index scan which will outperform seek.

    The reason is that seek operator returns rows in the index order and there is fair amount of IO overhead due to fragmentation. Index scan with NOLOCK or TABLOCK in this case will return rows in allocation order.

    Now if you need the predicate on clustered index key column and still want to return most of the rows, you need to force QO to use scan.

    One way of doing this is to add some expression in the predicate e.g. WHERE ID + 0 > -1 or WHERE ABS(ID) > -1. I believe by doing so the statistics histogram is not being used.

    The other option is INDEX=0 hint which you described in your post.

    In both cases NOLOCK or TABLOCK needs to be still in place so that the result of the query is in allocation and not index order.

    The proper way of optimizing the query is however rebuilding the index so that fragmentation is removed.

    This case scenario shows also that clustered index doesn’t guarantee that rows will be returned in the index order.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s