Systems Engineering and RDBMS

FORCESEEK Table Hint in SQL Server 2008

Posted by decipherinfosys on December 20, 2007

In prior versions of SQL Server, there was a way to force the optimizer to consider an index in the execution plan – however, there was no way to always force it to do a seek operation. That has now been added in the next release (tested against CTP5). A point of caution here – don’t be trigger happy and start using the hints. In a majority of the scenarios, the optimizer makes the right decision for the execution plan – you just need to make sure that it has statistics available to form the histograms correctly for the data distribution and it will do it’s job. However, there are always corner cases and that is why SQL Server (as well as Oracle and DB2 LUW) have table level, Query level and Join related hints available. The location of the hints and their syntax and usage is of course different in the three RDBMS. We have discussed some of these hints before like the ORDERED hint in Oracle (same as OPTION(FORCE ORDER) in SQL Server).

The FORCESEEK hint is a table level hint. It can also be applied to views and applies to both the clustered as well as the non-clustered indexes. It can also be applied in conjunction with the index hint i.e. you can force the optimizer to use a specific index as well as force it to use a seek operation on it. Let’s create an artificial situation here to demonstrate the usage. We will create a table with a million records in it which has a column that has only 3 distinct values. The data will be heavily skewed for this column. Yes – we are trying to re-create the bind variable peeking (Oracle) or parameter sniffing (SQL Server) scenario to help demonstrate the usage of this new hint. We had covered that in some of our posts before – you can read them here and here.

Here goes:

SET NOCOUNT ON
GO

CREATE TABLE BIG_TABLE
(
COL1 INT IDENTITY NOT NULL,
COL2 TINYINT NOT NULL
)
GO

DECLARE @I INT
SET @I = 1
WHILE (@I <= 1000000)
BEGIN

IF (@I <=10)
INSERT INTO BIG_TABLE (COL2) VALUES (0)

IF (@I > 10 AND @I <= 100000)
INSERT INTO BIG_TABLE (COL2) VALUES (10)

IF (@I > 100000 AND @I <= 1000000)
INSERT INTO BIG_TABLE (COL2) VALUES (90)

SET @I = @I + 1
END
GO

Here is the data distribution that was created by this small script:

SELECT COL2, COUNT(*) AS REC_CNT
FROM BIG_TABLE
GROUP BY COL2
ORDER BY COL2;

COL2 REC_CNT
 ---- -----------
 0    10
 10   99990
 90   900000

Now, let us create a non-clustered index on it:

CREATE NONCLUSTERED INDEX BIG_TABLE_IND_1 ON BIG_TABLE (COL2)
/*FILEGROUP CLAUSE*/
GO

And now, let’s fire off a parameterized query against this using the data value of 90 which we know from above qualifies for 900,000 records i.e. 90% of the records in the table.

SET SHOWPLAN_TEXT ON
GO
DECLARE @I INT
SET @I = 90
SELECT * FROM BIG_TABLE WHERE COL2 = @I
GO

Here is the execution plan for this statement:

StmtText
—————————————————————————————————————
|–Table Scan(OBJECT:([DEC_TEST_FS].[dbo].[BIG_TABLE]), WHERE:([DEC_TEST_FS].[dbo].[BIG_TABLE].[COL2]=[@I]))

And now, let us change the value that is being passed in to be 0. As we can see from the data distribution from above, the value of 0 qualifies for only 10 records in the table and that has a very good selectivity (0.001 %). The index should jump right at it and use it.

DECLARE @I INT
SET @I = 0
SELECT * FROM BIG_TABLE WHERE COL2 = @I
GO

StmtText
—————————————————————————————————————
|–Table Scan(OBJECT:([DEC_TEST_FS].[dbo].[BIG_TABLE]), WHERE:([DEC_TEST_FS].[dbo].[BIG_TABLE].[COL2]=[@I]))

As we know from previous posts on the topic, since the execution plan is already in the cache (based on the prior execution), the same execution plan gets used again this time even though the parameter has very good selectivity. There are ways to circumvent this issue even in prior versions of SQL Server and we have discussed those on our blog site. Another way to solve this would be to always re-compile this query regardless of the variable or to have a constant in this case instead of a parameter. Usage of plan guides is another way of fixing this.

Yet another way would be to use the FORCESEEK hint in places where we know the code will be getting fired only for the value of 0. Let’s take a look at the execution using this hint:

DECLARE @I INT
SET @I = 0
SELECT * FROM BIG_TABLE WITH (FORCESEEK) WHERE COL2 = @I
GO

StmtText
———————————————————————————————————————————————————-
|–Parallelism(Gather Streams)
|–Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1005]) WITH UNORDERED PREFETCH)
|–Index Seek(OBJECT:([DEC_TEST_FS].[dbo].[BIG_TABLE].[BIG_TABLE_IND_1]), SEEK:([DEC_TEST_FS].[dbo].[BIG_TABLE].[COL2]=[@I]) ORDERED FORWARD)
|–RID Lookup(OBJECT:([DEC_TEST_FS].[dbo].[BIG_TABLE]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

About these ads

One Response to “FORCESEEK Table Hint in SQL Server 2008”

  1. [...] SQL FORCESEEK http://decipherinfosys.wordpress.com/2007/12/20/forceseek-table-hint-in-sql-server-2008/ [...]

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers

%d bloggers like this: