Query Plans & Plan guides

Introduction:

Most developer DBAs have tough time tuning TSQL part of the database and application. And most people leave in middle of it either frustrated or cant go beyond that. Here is a simple look on how to use the query hints and plan guides to tune the TSQL part of your database and application.

Whats a Query Hints?

SQL Server uses Cost-based-Optimizer to generate query plans for executing the query. Sometimes the plan that SQL Servers uses can be overridden to force SQL Server to use a plan that we give to the database engine. SQSL Server generates query plans using CBO depending on system resources like processor, memory, virtual memory, etc., that is available at the time of execution. And these plans are stored in the buffer cache to be used in future until these are either manually or automatically cleared from the buffer. These plan what SQL Server generates for a query can be overridden using hints which is what we are going to look out now.

There are three types of query hints available with SQL Server 2005 ass given below.

1. Table hint
2. Join hint
3. Query hint

Table Hints:

Table hints specifies the query optimizer to pick up the option that is along with the query instead of allowing SQL Server to pick the best one for it. This includes picking up indexes to be used in a query, locking methods, scanning, etc.

NOEXPAND
This specifies that any indexed views are not expanded to access underlying tables when the query optimizer processes the query. The query optimizer treats the view like a table with clustered index.

INDEX (index_val [ ,… n ] )
This specifies the name or ID of the indexes to be used by the query optimizer when it processes the statement. Only one index hint per table can be specified.
If multiple indexes are used in the single hint list, the order of the indexes in the index hint is significant. The maximum number of indexes in the table hint is 250 non-clustered indexes.

KEEPIDENTITY
It is used to specify that identity values imported using the bulk option in the INSERT statement are used for identity only and verifies the identity criteria.

KEEPDEFAULTS
It is used to specify that default values are inserted when the column lacks a value or has a NULL value when using the bulk option in the INSERT statement.

FASTFIRSTROW
This option specifies that FIRST n numbers of rows that are specified are brought by the database engine first and then proceeds to retrieve the other rows that the query might yield resulting.

IGNORE_CONSTRAINTS
This specifies to ignore any constraints on the table while using the BULK option in the INSERT statement. But constraints like UNIQUE, PRIMARY KEY and NOT NULL are never ignored.

IGNORE_TRIGGERS
This specifies that any triggers defined on the table are ignored while using the BULK option in the INSERT statement.

HOLDLOCK
HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in. HOLDLOCK cannot be used in a SELECT statement that includes the FOR BROWSE option.
HOLDLOCK is equivalent to SERIALIZABLE isolation mode.

NOLOCK
NOLOCK holds no lock in the object and can is equivalent to READUNCOMMITTED. There is a possibility of reading DIRTY PAGES using this mode and should never be used unless necessary.

NOWAIT
This suggests that database engine not to wait for any lock grants and instructs to generate an error message as soon as a lock is encountered.

PAGLOCK
This specifies that page level locks to be used instead of row or table locks. When specified in transactions operating at the SNAPSHOT isolation level, page locks are not taken unless PAGLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

READCOMMITTED
Specifies that read operations comply with the rules for the READ COMMITTED isolation level by using either locking or row versioning. If the database option READ_COMITTED_SNAPSHOT is OFF, the Database Engine acquires shared locks as data is read and releases those locks when the read operation is completed. If the database option READ_COMMITTED_SNAPSHOT is ON, the Database Engine does not acquire locks and uses row versioning.

READCOMMITTEDLOCK
Specifies that read operations comply with the rules for the READ COMMITTED isolation level by using locking. The Database Engine acquires shared locks as data is read and releases those locks when the read operation is completed, regardless of the setting of the READ_COMMITTED_SNAPSHOT database option.

READPAST
When specified database engine does not read pages or rows that are being locked by other transaction. Database engine skips past the rows instead of blocking the transaction. READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

READUNCOMMITTED
This isolation level specifies that dirty reads are allowed. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, or present users with data that was never committed.

REPEATABLEREAD
This specifies that a scan is performed with the same locking semantics as a transaction running at REPEATABLE READ isolation level..

ROWLOCK
This hint specifies to use a ROW level lock instead of a page or table lock. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

SERIALIZABLE
This is equivalent to HOLDLOCK and the most restrictive among others. This hold the locks until the transaction is completed instead of releasing them as soon as the required page or row or table is no longer needed.

TABLOCK
This specifies that a TABLE level lock to be used instead of a row or a page level locks.

TABLOCKX
This specifies that an exclusive lock is taken on the table until the transaction completes.

UPDLOCK
This specifies that update locks are to be taken and held until the transaction completes.

XLOCK
This specifies that exclusive locks are to be taken and held until the transaction completes. If specified with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks apply to the appropriate level of granularity.

JOIN HINTS:

Join hints are used to enforce join strategy among table joined in the query. Join hints are mutually exclusive and can be enforced only in UPDATE, DELETE and SELECT statements. The hints that are available are listed below.

LOOP JOIN
This specifies that a nested loop join is used which means that for every row in the inner table is compared with the every other row in the outer table. This is the most commonly used join and can be used when joining a smaller inner table with larger outer table.

HASH JOIN
This specifies that a hash join in which one table is used as a hash table. The other table is scanned for one row a time and searching is done with hash functionality.

MERGE JOIN
This specifies that a merge join is used which means that for every row in the both table are sorted and compared with the every other row in the other table.

REMOTE JOIN
Remote join is used to join a table in a remote server. This is best used when the remote table has a relatively larger number of rows compared inner table that is in place locally.

QUERY HINTS:

Query hints are used as a directive to query and must be used with the OPTION clause and more than one hint can be used.

HASH or ORDER GROUP
This specifies that aggregations described in the GROUP BY, DISTINCT, or COMPUTE clause of the query should use hashing or ordering.

MERGE or HASH or CONCAT UNION
This specifies that all UNION operations are performed by merging, hashing, or concatenating UNION sets. If more than one UNION hint is specified, the query optimizer selects the least expensive strategy from those hints specified.

LOOP or MERGE or HASH JOIN
This specifies that all join operations are performed by LOOP JOIN, MERGE JOIN, or HASH JOIN in the whole query. Join hints are discussed above.
FAST number_rows
This specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set. This is equivalent to the FASTFIRSTROWS hint that was discussed earlier.

FORCE ORDER
This specifies that the table join order indicated by the query syntax is preserved during query optimization.

MAXDOP number
This option specifies the number of processor to be used for executing the query. This overrides the max degree of parallelism configuration option of sp_configure only for the query specifying this option.

OPTIMIZE FOR
This is to be used like
OPTIMIZE FOR(@variable_name=literalconstant[.n])
This hint optimizes the query for a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution.

PARAMETERIZATION { SIMPLE | FORCED }
This specifies the parameterization rules that the SQL Server query optimizer applies to the query when it is compiled.
FORCED PARAMETERIZATION can be used when a single query with different literal value is being used number of times with the same query plan instead of generating them each time before execution.

RECOMPILE
This specifies the database engine to discard the plan generated for the query after it executes, forcing the query optimizer to recompile a query plan the next time the same query is executed.

ROBUST PLAN
This forces the query optimizer to try a plan that works for the maximum potential row size, possibly at the expense of performance. When the query is processed, intermediate tables and operators may have to store and process rows that are wider than any one of the input rows.

KEEP PLAN
This hint forces the query optimizer to keep the plan in cache and this is only cleared on the next auto update statistics that is being executed due to excessive UPDATE, DELETE, or INSERT statements. Specifying KEEP PLAN makes sure that a query will not be recompiled as frequently when there are multiple updates to a table.

KEEPFIXED PLAN
This hint forces the query optimizer not to recompile a query due to changes in statistics. Specifying KEEPFIXED PLAN makes sure that a query will be recompiled only if the schema of the underlying tables is changed or if sp_recompile is executed against those tables.

EXPAND VIEWS
This hint specifies that the indexed views are expanded and the query optimizer will not consider any indexed view as a substitute for any part of the query. A view is expanded when the view name is replaced by the view definition in the query text.

MAXRECURSION number
This hint specifies the maximum number of recursions allowed for this query. number is a nonnegative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.

USE PLAN N’xml_plan’
This forces the query optimizer to use an existing query plan for a query that is specified by ‘xml_plan’. Query plans cannot be forced for insert, delete and update statements.

PLAN GUIDES

Plan guides are used in situation where the query comes from a third-party application. Plan guides in turn use query hints that are discussed earlier here. The plan guides feature offers users a mechanism to inject hints into the original query without having to modify it. Any of the query hints explained above can be applied to a SELECT, UPDATE, DELETE, or INSERT….SELECT statement using a plan guide.

Plan guides can be created using the system procedure sp_create_plan_guide.

sp_create_plan_guide [ @name = ] N’plan_guide_name’
, [ @stmt = ] N’statement_text’
, [ @type = ] N'{ OBJECT | SQL | TEMPLATE }’
, [ @module_or_batch = ]
{
N'[ schema_name. ] object_name’
| N’batch_text’
| NULL
}
, [ @params = ] { N’@parameter_name data_type [ ,…n ]’ | NULL }
, [ @hints = ] { N’OPTION ( query_hint [ ,…n ] )’ | NULL }

And plan can be controlled using the procedure sp_control_plan_guide.

sp_control_plan_guide [ @operation = ] N’‘ [ , [ @name = ]
N’plan_guide_name’ ]

The can be:
DROP used to drop the plan guide specified by plan_guide_name.
DROP ALL used to drop all plan guides in the current database.
DISABLE used to disable the plan guide specified by plan_guide_name.
DISABLE ALL used to disable all plan guides in the current database.
ENABLE used to enable the plan guide specified by plan_guide_name.
ENABLE ALL used to enable all plan guides in the current database.
If you are using template based plan guides, templates can be generated using the system procedure sp_query_get_template.

More about the using Plan guides with illustration will be continued in my next article using Plan guides effectively.

Leave a Reply

Your email address will not be published. Required fields are marked *

*