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