The SQL Server Query Optimizer cannot always find the best possible execution plans. In some cases, you can order a better plan by using the optimizer hints. In order to use the hints, you need to change the query. In addition, you have another option to influence the query execution—the plan guides. You can use the plan guides when you don’t want to or can’t change the query text—for example, when you need to optimize the queries created by an application of a third-party provider. SQL Server uses the plan guides to attach query hints or a fixed query plan to queries.

Optimizer Hints

Optimizer hints have a somewhat unfortunate name. They are not just hints; they are actually directives for the query execution ...
Read More

Plan Guides

In the plan guide, you can specify either the OPTION clause or a specific query plan for the statement you ...
Read More

This lesson is a part of Understanding Further Optimization Aspects from 70-461 Querying Microsoft SQL Server 2012 Prep course. More lessons in this chapter are

Understanding Plan Iterators

As you already know from Chapter, SQL Server executes a query by using a set of physical  operators. Because these ...
Read More

Using Parameterized Queries and Batch Operations

As you learned in Chapter 14, the SQL Server Query Optimizer has a lot of work to do to determine ...
Read More

Using Optimizer Hints and Plan Guides

The SQL Server Query Optimizer cannot always find the best possible execution plans. In some cases, you can order a ...
Read More

The Practice tests included in this course are:

Exam 70-461 Querying Microsoft SQL Server 2012 Practice Test 5

Instructions for Exam 70-461 Querying Microsoft SQL Server 2012 Practice Test 5 Number of Questions: The test has approximately 20-50 ...
Read More

Exam 70-461 Querying Microsoft SQL Server 2012 Practice Test 4

Instructions for Exam 70-461 Querying Microsoft SQL Server 2012 Practice Test 4 Number of Questions: The test has approximately 20-50 ...
Read More

Exam 70-461 Querying Microsoft SQL Server 2012 Practice Test 3

Instructions for Exam 70-461 Querying Microsoft SQL Server 2012 Practice Test 3 Number of Questions: The test has approximately 20-50 ...
Read More

Exam 70-461 Querying Microsoft SQL Server 2012 Practice Test 2

Instructions for Exam 70-461 Querying Microsoft SQL Server 2012 Practice Test 2 Number of Questions: The test has approximately 20-50 ...
Read More

Exam 70-461 Querying Microsoft SQL Server 2012 Practice Test 1

Instructions for Exam 70-461 Querying Microsoft SQL Server 2012 Practice Test 1 Number of Questions: The test has approximately 20-50 ...
Read More