You are a database administrator for AIOTestKing.com. Your company uses a different company’s application that is based on SQL Server 2005 Standard Edition. The application executes a query that uses an index query hint. The index query hint is not suitable for your environment, but you cannot modify the query. You need to force the application to use a different query execution plan. What should you do?
A.
Create a plan guide for the query.
B.
Clear the procedure cache.
C.
Create a new covering index on the columns that the query uses.
D.
Update the statistics for all of the indexes that the query uses.
Explanation:
Over the past few years,Microsoft SQL Server has increased its presence in the industry and has reduced its TCO. This reduced TCO is a direct result, primarily, of the numerous self-tuning mechanisms built into Microsoft SQL Server.These mechanisms automatically perform tasks that would otherwise have to be performed by experienced database administrators. One such mechanism is the cost-based optimizer (CBO) that is used to dynamically generate query execution plans. The CBO probes several system-wide resource states and employs many complex, heuristical algorithms to generate the best possible plan for a given query and the underlying table and index structures. This mechanism works well for the vast majority of user queries, but there are times when experienced users need to force a particular query plan, based on some prior knowledge or insights into future uses.