SQL Server 2008 R2 SP1–new table hint options for FORCESEEK and FORECSCAN

 Other than bad code plan stability is one of the main reasons for performance problems I see in client systems. This is often due to bad estimates, bad stats etc you can watch my SQLbits session on when a query plan goes bad for more information.

One of the solutions is the use of hints. I only use hints only in extreme cases when the data skew is such that the optimiser doesn’t have a chance or you can’t rely on stats being up to date.

Whats new with SQL Server 2008 R2 SP1 (http://support.microsoft.com/kb/2527041) (list of bug fixes here http://support.microsoft.com/kb/2528583) is an addition to the forceseek hint which allows you to force the index you wish to seek on, and the columns to use. I can see that to be of use if you have two access paths to the table and the stats and estimates reckon that you should use one path when you should really use the other path.

I’m not sure I’ve come across a scenario where I need this but clearly someone has.

In addition a FORCESCAN has been added, I wonder if thats been added to help support fasttrack type work configurations.

Here is the indepth section from BOL (http://msdn.microsoft.com/en-us/library/ms181714.aspx) about FORCESEEK.

 FORCESEEK [ (index_value(index_column_name [ ,... n ] )) ]

Specifies that the query optimizer use only an index seek operation as the access path to the data in the table or view. Starting with SQL Server 2008 R2 SP1, index parameters can also be specified. Specifying FORCESEEK with index parameters is similar to using FORCESEEK with an INDEX hint. However, you can achieve greater control over the access path used by the query optimizer by specifying both the index to seek on and the index columns to consider in the seek operation.

index_value

Is the index name or index ID value. The index ID 0 (heap) cannot be specified. To return the index name or ID, query the sys.indexes catalog view.

index_column_name

Is the name of the index column to include in the seek operation. The query optimizer considers only index seek operations through the specified index using at least the specified index columns. The optimizer may consider additional columns if needed. For example, if a nonclustered index is specified, the optimizer may choose to use clustered index key columns in addition to the specified columns.

The FORCESEEK hint can be specified in the following ways.

Syntax

Example

Description

Without an index or INDEX hint

FROM dbo.MyTable WITH (FORCESEEK)

The query optimizer considers only index seek operations to access the table or view through any relevant index.

Combined with an INDEX hint

FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex))

The query optimizer considers only index seek operations to access the table or view through the specified index.

Parameterized by specifying an index and index columns

FROM dbo.MyTable WITH (FORCESEEK (MyIndex (col1, col2, col3)))

The query optimizer considers only index seek operations to access the table or view through the specified index using at least the specified index columns.

When using the FORCESEEK hint (with or without index parameters), consider the following guidelines.

  • The hint can be specified as a table hint or as a query hint. For more information about query hints, see Query Hints (Transact-SQL).

  • To apply FORCESEEK to an indexed view, the NOEXPAND hint must also be specified.

  • The hint can be applied at most once per table or view.

  • The hint cannot be specified for a remote data source. Error 7377 is returned when FORCESEEK is specified with an index hint and error 8180 is returned when FORCESEEK is used without an index hint.

  • If FORCESEEK causes no plan to be found, error 8622 is returned.

When FORCESEEK is specified with index parameters, the following guidelines and restrictions apply.

  • The hint cannot be specified in combination with either an INDEX hint or another FORCESEEK hint.

  • At least one column must be specified and it must be the leading key column.

  • Additional index columns can be specified, however, key columns cannot be skipped. For example, if the specified index contains the key columns a, b, and c, valid syntax includes FORCESEEK (MyIndex (a)) and FORCESEEK (MyIndex (a, b). Invalid syntax includes FORCESEEK (MyIndex (c)) and FORCESEEK (MyIndex (a, c).

  • The order of column names specified in the hint must match the order of the columns in the referenced index.

  • Columns that are not in the index key definition cannot be specified. For example, in a nonclustered index, only the defined index key columns can be specified. Clustered key columns that are automatically included in the index cannot be specified, but may be used by the optimizer.

  • Modifying the index definition (for example, by adding or removing columns) may require modifications to the queries that reference that index.

  • The hint prevents the optimizer from considering any spatial or XML indexes on the table.

  • The hint cannot be specified in combination with the FORCESCAN hint.

  • For partitioned indexes, the partitioning column implicitly added by SQL Server cannot be specified in the FORCESEEK hint. For more information, see Special Guidelines for Partitioned Indexes.

Caution noteCaution

Specifying FORCESEEK with index parameters limits the number of plans that can be considered by the optimizer more than when specifying FORCESEEK without parameters. This may cause a "Plan cannot be generated" error to occur in more cases. In a future release, internal modifications to the optimizer may allow more plans to be considered. For more information, see Using the FORCESEEK Table Hint.

Published Tuesday, July 12, 2011 10:07 PM by simonsabin

Comments

Tuesday, July 12, 2011 10:44 PM by SimonS Blog on SQL Server Stuff

# SQL Server 2008 R2 SP1–new table hint options for FORCESEEK and FORECSCAN

 Other than bad code plan stability is one of the main reasons for performance problems I see in

Wednesday, July 13, 2011 2:50 PM by Wednesday Weekly #sqlserver Links for 2011-28 | sqlmashup

# Wednesday Weekly #sqlserver Links for 2011-28 | sqlmashup

Pingback from  Wednesday Weekly #sqlserver Links for 2011-28 | sqlmashup

# SQL Server 2008 R2 SP1???new table hint options... | Microsoft | Syngu

Pingback from  SQL Server 2008 R2 SP1???new table hint options... | Microsoft | Syngu