Control execution plans

May include but is not limited to: table hints, query hints

As you may know, SQL Server tries to use the best possible way to execute a query. It has very powerful query optimization techniques, with which you’ll be happy most of the time. But sometimes (very very rarely) you need to provide hints on how to execute a given statement (in a real world, you’d never do that, because it’s the responsibility of the DBA).

Fortunately, there are a bunch of built-in query hints. Even more fortunately, there isn’t a single goddamn word on these hints (to be funny, there isn’t any hint of them) in the Training Kit, so let’s query our old friend, MSDN.

Now there are two kind of these hints, query and table. Let’s start with the former. You can set a query hint on any query (the sole exception is when you use UNIONs), by adding the OPTION keyword, along with the desired hint to it. Here’s a brief list of what you may encounter:

  • GROUP: specifies that what method should aggregations use. The two values are HASH and ORDER.
  • UNION: specifies what method should the union operators use: MERGE, HASH or CONCAT.
  • JOIN: specifies what method should JOINs use: LOOP, MERGE or HASH.
  • FAST(number): takes a nonnegative number, and optimizes the query to retrieve those top number of rows fast.
  • FORCE ORDER: specifies the order of joins, and forces it.
  • MAXDOP(number): takes a nonnegative number, and overrides the max degree of parallelism configuration option.
  • OPTIMIZE FOR(@variable name UNKNOWN/constant…): takes a list of variables, and uses them to optimize the query (not for execution).
  • PARAMETERIZTAION: specifies whether to use forced or simple parameterization. This hint can be used only inside a plan guide, not in an actual query.
  • RECOMPILE: forces the database engine to recompile the query plan every time it runs. Without recompile, the engine catches the query plans and reuses them.
  • ROBUST PLAN: forces the database engine to use a plan that works on the maximal potential number of rows, possibly hurting performance.
  • KEEP PLAN: forces the engine to keep the query plan, as long as multiple updates don’t occur.
  • MAXRECURSION(number): specifies a number of maximal recursions. Mostly used in common table expressions.
  • USE PLAN(N’xml_plan’): forces the query optimizer to use the specified plan.

These were the query hints. You use them like this:


At the end of the query. Now let’s consider the usage of table hints. You shouldn’t use them, because SQL Server probably knows much better how to execute a query than you (or anyone else out there). The most interesting one is the INDEX table hint, which is written as follows:

WITH (INDEX(myIndex))

Note that you can enter any number of table hints, separated by commas. The INDEX table hint forces the query optimizer to use the specified index when working with your table. You can only specify one index hint per table.

Further Readings

Table hints

Query hints

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s