SQL Server Enhanced FORCESEEK and FORCESCAN Query hint July 11th, 2013

Vinod Kumar

I wrote a number of posts around SQL Server hints earlier. Some are: SQL Server–Plan Freezing, SQL Server FORCESEEK Hint, SQL Server TABLE HINT. In this blog post, let me revisit some of them and give a spin of the enhancements to these hints from an implementation perspective.

FORCESEEK was first introduced in SQL Server 2008 version. It allows a user to specify on a specific table such that SQL Server Optimizer will only use seeks for this table. However, we cannot specify which index to use and which column to seek on.

The enhanced version of FORCESEEK with SQL Server 2012 will allow us to specify index hint together which columns to seek on. This will give users tighter control on which index and which column to seek on. The syntax is that you need to specify both index name and columns names (forceseek (x (y1, y2)) where x is index name and y1 and y2 are column names.

Forceseek example

Here is a typical example.

CREATE TABLE t_forceseek (col1 INT, col2 INT)

GO

CREATE INDEX IDX_t_forceseek ON t_forceseek(col1, col2)

GO

SELECT * FROM t_forceseek WITH (forceseek (IDX_t_forceseek(col1, col2)))

WHERE col1 = 0 and col2=1

In XML plan, we will see ForceSeek="1" for the particular operator that does the seek. Additionally, NColsForceSeek will indicate how many columns are being seeked on.

<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="1" NColsForceSeek="2" ForceScan="0" NoExpandHint="0" Storage="RowStore">

FORCESCAN Enhancements

It is not always efficient to do seeks. In situations where underestimate occurs, SQL Optimizer will most likely choose a seek plan which may run slower. FORCESCAN was introduced to force a scan on an index or table.

Below is a typical usage:

FROM t_forcescan WITH (INDEX(x), FORCESCAN)

When used with an table, it would look like:

SELECT * FROM t_forcescan WITH (INDEX(IDX_t_forcescan), FORCESCAN) WHERE col1 LIKE ‘vinod%’

XML plan will have ForceScan="1" for the index scan operator.

<IndexScan Ordered="0" ForcedIndex="1" ForceSeek="0" ForceScan="1" NoExpandHint="0" Storage="RowStore">

Other Fine prints

1. We can use index 0 which means scanning base table.

2. We cannot specify on multiple indexes.

3. Partition elimination will work the same way as normal. In other words, the scan will not scan all partitions. It will still skip the partitions that have been eliminated by the plan either at runtime or at execution time.

4. We cannot use this with Spatial index, XML index or Distributed queries.

Just out of curiosity, how many of you use hints of this sort. Do let me know your scenario and it would be great learning for all readers.

Tags: , , , , , ,

This entry was posted on Thursday, July 11th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply