Database Reference
In-Depth Information
All other options behave exactly the same way as previously outlined in this chapter. The processing time
is radically less than when the advisor processes a workload. It has only the queries in cache to process, so,
depending on the amount of memory in your system, this can be a short list. The results from processing my cache
suggested one index on the
Person
table. This is estimated to enhance performance by about 45 percent, as you can
see in Figure
10-15
.
Figure 10-15.
Recommendations from the plan cache
This gives you one more mechanism to try to tune your system in an automated fashion. But it is limited to the
queries that are currently in cache. Depending on the volatility of your cache (the speed at which plans age out or are
replaced by new plans), this may or may not prove useful.
Database Engine Tuning Advisor Limitations
The Database Engine Tuning Advisor recommendations are based on the input workload. If the input workload is not
a true representation of the actual workload, then the recommended indexes may sometimes have a
negative
effect
on some queries that are missing in the workload. But most importantly, in many cases, the Database Engine Tuning
Advisor may not recognize possible tuning opportunities. It has a sophisticated testing engine, but in some scenarios,
its capabilities are limited.
For a production server, you should ensure that the SQL trace includes a complete representation of the
database workload. For most database applications, capturing a trace for a complete day usually includes most of
the queries executed on the database, although there are exceptions to this such as weekly, monthly, or year-end
processing. Be sure you understand your load and what's needed to capture it appropriately. A few of the other
considerations/limitations with the Database Engine Tuning Advisor are as follows:
•
Trace input using the
SQL:BatchCompleted
event
: As mentioned earlier, the SQL trace input to
the Database Engine Tuning Advisor must include the
SOL:BatchCompleted
event; otherwise,
the wizard won't be able to identify the queries in the workload.
•
Query distribution in the workload
: In a workload, a query may be executed multiple times
with the same parameter value. Even a small performance improvement to the most common
query can make a bigger contribution to the performance of the overall workload, compared
to a large improvement in the performance of a query that is executed only once.
•
Index hints
: Index hints in a SQL query can prevent the Database Engine Tuning Advisor from
choosing a better execution plan. The wizard includes all index hints used in a SQL query as
part of its recommendations. Because these indexes may not be optimal for the table, remove
all index hints from queries before submitting the workload to the wizard, bearing in mind
that you need to add them back in to see whether they do actually improve performance.
