Every query gets compiled by the database engine into an execution plan.
The execution plan is the strategy that SQL Server will use to access
the data. The access strategy (execution plan) considers many things including
the query, indexes and data distributions. Optimization is performed each
time SQL Server is required to create an execution plan. The Query Optimizer
determines the best way to execute a query based on such things as:
(statistical data on the distribution of values)
Performance Tuning and Optimization of SQL Server oftens focuses on the
execution plan for critical data used to optimize the query.
Another way to view a query plan selected by the Query Optimizer, without
also returning a result set from executing a query, is by enabling the
SET SHOWPLAN_TEXT or SET SHOWPLAN_ALL commands.
Using the workload and the execution plans built by the Query Optimizer,
the Index Tuning wizard:
- Recommends the best mix
of indexes for a database and ways to tune the database for a set of
- Analyzes the effects of
the proposed changes, including index usage, distribution of queries
among tables, and performance of queries in the workload.
You can customize the recommendations
by specifying advanced options such as how much disk space can be used
by new indexes. A recommendation consists of SQL statements that, when
executed, create more effective indexes and optionally drop existing indexes
that are inefficient or unnecessary.
Index Tuning wizard recommendations are applied immediately, scheduled
for later application, or saved to a script for manual execution.
Index Tuning wizard recommendations not include index creation for tables
referenced by cross-database queries and system tables.
Tables created with PRIMARY KEY constraints and unique keys automatically
generate indexes for these keys. Therefore, the Index Tuning wizard does
not recommend indexes for tables containing these attributes, but it can
drop or replace a clustered index that is not unique or is not created
on a PRIMARY KEY constraint. There are other limits associated with the
Index Tuning wizard. For more information, see SQL Server Books Online.
the Index Tuning Wizard
You can start the Index Tuning wizard from Enterprise Manager, Query Analyzer,
or SQL Profiler.
- In Enterprise Manager, the
Index Tuning wizard is a listed wizard in the Select wizard window.
- In Query Analyzer, the Index
Tuning wizard is an option in the Query men.
- in SQL Profiler it's an
option in the Tools menu.
connecting to the server, the Index Tuning wizard requires that you select
a database and specify whether you want to keep the existing indexes,
whether you want to create indexed views, and how thorough of an analysis
should be performed. The wizard does not recommend that any indexes be
dropped if the Keep All Existing Indexes checkbox is selected. Recommendations
will include only new indexes.
If you are running SQL Server 2000 Enterprise Edition or Developer Edition,
the Index Tuning wizard can create indexes on views if the Add Indexed
Views checkbox is selected. The more thorough the analysis, the more
significant will be the CPU consumption while analysis is being performed.
After you select the Index Tuning wizard configuration, you must select
a workload. Workload data comes from a trace file or trace table or a
selection in the Query Analyzer. The Query Analyzer selection option is
available only if you start the Index Tuning wizard from the
After you select the workload, you can change the default index tuning
parameters, select the tables for the wizard to analyze, and then run
the analysis. Following the analysis, the wizard might not make index
suggestions if there isn't enough data in the tables being sampled or
if recommending indexes do not offer enough projected improvement in query
performance over existing indexes.