Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique

solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these

questions will not appear in the review screen.

You support an application that stores data in a Microsoft SQL Server database. You have a query that returns data for a report that users run frequently.

The query optimizer sometimes generates a poorly-p

erforming plan for the query when certain parameters are used. You observe that this is due to the distribution of data within a specific table that the query uses.

You need to ensure that the query optimizer always uses the query plan that you prefer.

Solution: You force the desired plan.

Does the solution meet the goal?

A. Yes

B. No

Explanation:

KEEPFIXED PLAN should be used as it forces the query optimizer not to recompile a query due to changes in statistics.

When FORCEPLAN is set to O

N, the SQL Server query optimizer processes a join in the same order as the tables appear in the FROM clause of a query. In addition, setting FORCEPLAN to ON forces the use of a nested loop join unless other types of joins are required to construct a plan

for the query, or they are requested with join hints or query hints.

References: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017