Scenarios for Choosing Tuning Options
You can combine various tuning options that are available with Database Engine Tuning Advisor to get the recommendations that you need for your SQL Server implementation. The following sections discuss scenarios of different tuning option combinations and the type of tuning recommendations each combination produces.
Choosing the Right Combination of Options
The following table lists examples that illustrate how Database Engine Tuning Advisor interprets various combinations of tuning options users can choose by means of either the dta command prompt utility or the Database Engine Tuning Advisor graphical user interface (GUI):
Arguments specified with dta |
Selections from Tuning Options tab in GUI |
How Database Engine Tuning Advisor interprets this combination of options |
|---|---|---|
-fa IDX -fp NONE -fk ALL |
Indexes No partitioning Keep all existing PDS |
Retains all existing physical design structures in the recommendation. Database Engine Tuning Advisor may also suggest unpartitioned indexes as part of the recommendation. |
-fa IDX_IV -fp NONE -fk NONE |
Indexes and indexed views No partitioning Do not keep any existing PDS |
Includes only unpartitioned indexes and indexed views in the recommendation. If the resulting recommendation is implemented, all existing partitioned objects (if any) will be dropped. |
-fa IDX_IV -fp ALIGNED -fk ALIGNED |
Indexes and indexed views Aligned partitioning Keep aligned partitioning |
Retains all existing physical design structures that are "aligned" (partitioned in the same way as base tables and views). Database Engine Tuning Advisor may recommend other indexes and indexed views that are also aligned. Implementing the resulting recommendation takes the database into an "aligned" state. |
Usage Scenarios for Tuning Option Combinations
The following table lists common scenarios that most users encounter in practice, and the tuning combinations that enable these scenarios. The factors that govern these scenarios are:
Performance
Feature familiarity, for example, comfort level using indexed views
Incremental changes to the database
Manageability
Backward compatibility
Ease of migration (from one version of Microsoft SQL Server to another)
Performance is a factor that is common to all scenarios; therefore, it is not explicitly listed as one of the factors in the following table.
#. |
Scenario description |
Factors |
Objects specified |
Partitioning specified |
Keep option specified |
|---|---|---|---|---|---|
1 |
User wants best performance (new or existing databases), but user not comfortable with partitioning feature. Users migrating from SQL Server 2005 to SQL Server 2008. |
Feature familiarity, backward compatibility |
Indexes and indexed views |
None |
None |
2 |
User tuning small set of new or ad hoc queries, but user not comfortable with partitioning feature. Users migrating from SQL Server 2005 to SQL Server 2008. |
Incremental change, feature familiarity, backward compatibility |
Indexes and indexed views |
None |
All |
3 |
User does not want to change clustering of base tables. Not comfortable with partitioning. Users migrating from SQL Server 2005 to SQL Server 2008. |
Feature familiarity, incremental change |
Indexes and indexed views |
None |
Clustered indexes |
4 |
User wants well tuned indexes and only wants incremental changes for indexes for small set of new queries. Not comfortable with partitioning. Users migrating from SQL Server version 7.0 to SQL Server 2000, and SQL Server 2000 users. |
Incremental change |
Indexes and indexed views |
None |
Indexes |
5 |
User wants best performance for entire workload (new or existing databases). User configures or deploys new SQL Server system. Users migrating from SQL Server 2005 to SQL Server 2008 who want to exploit partitioning for performance. |
- |
Indexes and indexed views |
Full |
None |
6 |
User tuning small set of new or ad hoc queries. User deploys new SQL Server system |
Incremental change |
Indexes and indexed views |
Full |
All |
7 |
User does not want to change clustering of base tables but wants best performance otherwise. |
Incremental change |
Indexes and indexed views |
Full |
Clustered indexes |
8 |
User wants to keep all existing indexes but not necessarily indexed views, while improving performance as much as possible. May be useful when workload changes (indexed views are less robust than indexes with regard to workload changes). |
Incremental change |
Indexes and indexed views |
Full |
Indexes |
9 |
User wants best performance (new or existing databases), but partitioning is primarily for manageability. User configures or deploys new system. |
Manageability |
Indexes and indexed views |
Aligned |
None |
10 |
User wants partitioning primarily for manageability. Tuning a small set of new or ad hoc queries. |
Incremental change, manageability |
Indexes and indexed views |
Aligned |
Aligned |
11 |
User does not want to change clustering of base tables. Partitioning is primarily for manageability. |
Feature familiarity, Incremental change, manageability |
Indexes and indexed views |
Aligned |
Clustered indexes |
12 |
User comfortable with indexes but not with indexed views or partitioning. Best performance for entire workload. User upgrades from SQL Server version 7.0 to SQL Server 2005, or from SQL Server version 7.0 to SQL Server 2008. |
Feature familiarity, backward compatibility |
Indexes |
None |
None |
13 |
User comfortable with indexes but not with indexed views or partitioning. Tune small set of new or ad hoc queries. User upgrades from SQL Server version 7.0 to SQL Server 2005, or from SQL Server version 7.0 to SQL Server 2008. |
Incremental change, feature familiarity, backward compatibility |
Indexes |
None |
All |
14 |
User does not want to change clustering of base tables. Not comfortable with partitioning or indexed views. User upgrades from SQL Server version 7.0 to SQL Server 2005, or from SQL Server version 7.0 to SQL Server 2008. |
Feature familiarity, incremental change |
Indexes |
None |
Clustered indexes |
15 |
User wants to eliminate existing indexed views from current database without eliminating existing indexes. |
Feature familiarity, incremental change |
Indexes |
None |
Indexes |
16 |
User not comfortable with indexed views. Best performance for entire workload. User upgrades from SQL Server version 7.0 to SQL Server 2008. |
Feature familiarity |
Indexes |
Full |
None |
17 |
User not comfortable with indexed views. Tune small set of new or ad hoc queries. User upgrades from SQL Server version 7.0 to SQL Server 2008. |
Feature familiarity, incremental change |
Indexes |
Full |
All |
18 |
User not comfortable with indexed views. User does not want to change clustering of base tables. Tuning small set of new or ad hoc queries. User upgrades from SQL Server version 7.0 to SQL Server 2008. |
Feature familiarity, incremental change |
Indexes |
Full |
Clustered indexes |
19 |
User wants to eliminate existing indexed views from current database without eliminating existing indexes. |
Feature familiarity, incremental change |
Indexes |
Full |
Indexes |
20 |
User comfortable with indexes, but not with indexed views. Partitioning is primarily for manageability. Wants best performance for entire workload. User upgrades from SQL Server version 7.0 to SQL Server 2005. |
Feature familiarity, Manageability |
Indexes |
Aligned |
None |
21 |
User comfortable with indexes but not indexed views. Partitioning is primarily for manageability. Wants best performance for entire workload. User upgrades from SQL Server version 7.0 to SQL Server 2008. |
Feature familiarity, manageability |
Indexes |
Aligned |
Aligned |
22 |
User does not want to change clustering of base tables. Not comfortable with indexed views. Partitioning is primarily for manageability. User upgrades from SQL Server version 7.0 to SQL Server 2005. |
Feature familiarity, incremental change, manageability |
Indexes |
Aligned |
Clustered indexes |
23 |
User is tuning small set of queries. Does not want any new clustered indexes. Not comfortable with indexed views or partitioning. |
Feature familiarity, incremental change |
Non-clustered indexes |
None |
All |
24 |
User does not want any new clustered indexes. Not comfortable with indexed views or partitioning. |
Feature familiarity, incremental change |
Non-clustered indexes |
None |
Clustered indexes |
25 |
User is tuning small set of queries. Does not want any new clustered indexes. Not comfortable with indexed views. |
Feature familiarity, incremental change |
Non-clustered indexes |
Full |
All |
26 |
User does not want any new clustered indexes. Not comfortable with indexed views. Willing to consider full redesign of non-clustered indexes. |
Feature familiarity, incremental change |
Non-clustered indexes |
Full |
Clustered indexes |
27 |
User does not want any new clustered indexes. Not comfortable with indexed views. Wants to keep final physical design aligned. |
Feature familiarity, incremental change, manageability |
Non-clustered indexes |
Aligned |
Aligned |
28 |
User is tuning small set of queries. Does not want any new clustered indexes. Not comfortable with indexed views. Willing to consider redesign of non-clustered indexes as long as aligned. |
Feature familiarity, incremental change |
Non-clustered indexes |
Aligned |
Clustered indexes |
29 |
User is tuning a set of queries that select from a subset of table data. Wants best performance for indexes. |
Performance |
Indexes with filtered index option |
None |
None |
30 |
User wants well-tuned indexes and only wants incremental changes for indexes for small set of new queries. Users migrating from SQL Server 2005 to SQL Server 2008. |
Incremental change |
Indexes and indexed views, with filtered index option |
None |
Indexes |