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
GO
SELECT ProductID, ProductNumber, Color
FROM Production.Product
WHERE ProductID < 500
GO
SELECT ProductID, ProductNumber, Color
FROM Production.Product WITH (INDEX(0))
WHERE ProductID < 500
GO
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
GO
SELECT ProductID, ProductNumber
FROM Production.Product
WHERE ProductNumber = 'CR-7833'
GO
SELECT ProductID, ProductNumber
FROM Production.Product WITH (INDEX(0))
WHERE ProductNumber = 'CR-7833'
GO
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 (https://blog.sqlauthority.com)
11 Comments. Leave new
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?
Hi Pinal,
thank you for the great article! I would, however, recommend to your readers another great article on using HINTS. https://www.brentozar.com/archive/2010/03/2-features-of-sql-server-you-should-avoid/
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.
cross apply
(
SELECT
sum(k.totalsales) as totalsaleslast30days
FROM tblretailscans k WITH (INDEX(0))
–The no index is necessary to stop the keylookup and lazy spool
—
WHERE k.selldate between convert(date, dateadd(dd, -90, getdate())) and getdate()
and k.productid = si.productid and k.storeid = si.storeid
) ks
Consider the code above. I know it’s been awhile, but there is a space for this.
In my query plan I saw that I had a lazy spool. Working back I found a key lookup.
The reason is that sometimes totalsales,or predicate is NULL aggregate is NULL. Well, I still need to see those so I don’t really want to replace this cross apply with an inner join.
So I added the index(0) hint and the key lookup turned in to a table scan. Now this table has over ten million rows, but the result was still much faster than the lookup and lazy spool.
Use reverse(reverse(‘string’)) to eliminate index options. Substring would probably do it too but don’t have time to test.
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.
Can I used WITH (INDEX(0)) on INSERT to simulate the performance degradation index cause on INSERT
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
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.
Hi Pinal,
In the above you mentioned that, 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.
But have create a table named Sales and created one constraint Unique and one non clustered index on top of it so we will have two non clustered indexes.
As per above statement the table should not have record in a table with column type value is zero but i observed that we have 3 records with type column values 0,2,2.
Object_ID type
245575913 0
245575913 2
245575913 2
Can you please reply on this.
Well, interestingly, I came here due to an issue related to a clustered index seek. We added a clustered index to a 15m row table that was being used (on the index keys) in a correlated sub-query. After adding the clustered index, the performance degraded dramatically (on my play, returned 2,000 records [of primary table] in 20 minutes). If I forced an index scan, the query returned all 1.4m rows in just over 1 minute.