SQL Server TABLE HINT

Last week we discussed SQL Server FORCESEEK Hint and it had some interesting needs from an ISV application deployments. On the same lines, I wanted to also mention the extension to using TABLE hints. The TABLE HINT clause in SQL Server 2008 allows us to specify table-level hints as query hints in the OPTION clause of the query. This feature was added to provide greater flexibility for the set of hints that could be applied via a plan guide where the only choice is to apply a query hint. In all other situations it is preferable to specify the hint using existing syntax (i.e., after the table name).

Below is a classic example of using TABLE HINT in for a classic hint done in regular syntax:

CREATE TABLE tblTest (col1 INT NOT NULL PRIMARY KEY, col2 INT)

CREATE INDEX indCol2 ON tblTest (col2)

GO

SELECT * FROM tblTest WITH (FORCESEEK, INDEX(indCol2))

WHERE col2 = 100

GO

SELECT * FROM tblTest

WHERE col2 = 100

OPTION (TABLE HINT (tblTest, FORCESEEK, INDEX(indCol2)))

GO

The table name or alias is specified in the TABLE HINT clause followed by a list of hints to specify. Because the TABLE HINT was designed to be used with plan guides — use where the DBA needs to control the performance but doesn’t have control over the client application. It is important that any table hints must not change the semantics or correctness of the query.

The hints in the TABLE HINT clause are merged with those table hints specified in the query itself. If a semantic-affecting hint also appears in the query with a WITH clause then it is actually required to include the same hint again in the OPTION/TABLE HINT clause so as to avoid “removing” a semantic-affecting hint that the application actually depends on. If the semantic affecting hint is omitted then you’ll also get an error:

Msg 8722, Level 16, State 1, Line 1

Cannot execute query. Semantic affecting hint ‘nolock’ appears in the ‘WITH’ clause of object ‘tblTest’ but not in the corresponding ‘TABLE HINT’ clause. Change the OPTION (TABLE HINTS…) clause so the semantic affecting hints match the WITH clause.

In order to understand the same, the following queries show how to write this:

– Simple query to add FORCESEEK.

SELECT * FROM tblTest WITH (NOLOCK)

WHERE col1 = 0

GO

– This will generate error 8722 as we showed before

SELECT * FROM tblTest WITH (NOLOCK)

WHERE col1 = 0

OPTION (TABLE HINT (tblTest, FORCESEEK))

GO

– This is the correct way to add the FORCESEEK hint with nolock

SELECT * FROM tblTest WITH (NOLOCK)

WHERE col1 = 0

OPTION (TABLE HINT (tblTest, NOLOCK, FORCESEEK))

GO

You can also use table alias in your table hints. If the hint is not available with the query, then we will get the following error:

Msg 8723, Level 16, State 1, Line 1

Cannot execute query. Object ‘t’ is specified in the TABLE HINT clause, but is not used in the query or does not match the alias specified in the query. Table references in the TABLE HINT clause must match the WITH clause.

If the application developer originally specified a non-semantic affecting table hint (e.g. FORCESEEK, INDEX(name)) but did so in a way that it actually causes slower performance then it may be desirable to override/disable that hint through the plan guide. We can do this by specifying a TABLE HINT that specifies only the table name, as shown below:

SET SHOWPLAN_TEXT ON

GO

– Sample query that used index hint incorrectly

SELECT * FROM tblTest WITH (INDEX(indCol2))

WHERE col1 = 0

GO

– Empty TABLE HINT will disable the hint in WITH clause

SELECT * FROM tblTest WITH (INDEX(indCol2))

WHERE col1 = 0

OPTION (TABLE HINT (tblTest))

GO

The plans for the two queries above are shown below. Note how the plan for the first query used an index scan seek it is not possible to seek on column col1 since it is not indexed. The second plan shows that by overriding the index hint the optimizer chose the expected plan which seeks on col1.

StmtText
———————————————————————————
  |–Index Scan(OBJECT:([tempdb].[dbo].[tblTest].[indCol2]),  WHERE:([tempdb].[dbo].[tblTest].[col1]=(0)))

StmtText
———————————————————————————
  |–Clustered Index Seek(OBJECT:([tempdb].[dbo].[tblTest].[PK__tblTest__357D0D3EB5A0DD91]), SEEK:([tempdb].[dbo].[tblTest].[col1]=(0)) ORDERED FORWARD)

I have seen this last technique as a awesome way to override wrong hint specified by developers. Use these hint techniques with care and I highly recommend you to test the same before using the same in your environments. Do feel free to pass me your comments.

Share this article

Leave a Reply