• Article

Database Engine Tuning Advisor (Tuning Options Tab)

Use the Tuning Options tab to modify default settings of general tuning options. It is not necessary to modify the settings of the Tuning Options tab before starting a tuning session.

Options

  • Limit tuning time
    Limits the time for the current tuning session. Providing more time for turning improves the quality of the recommendations. To ensure the best recommendations, do not select this option.

    Note

    Database Engine Tuning Advisor consumes system resources during analysis. Use Limit tuning time to stop tuning before periods of anticipated heavy workload on the server being tuned.

  • Advanced Options
    Click to set advanced options. For example, setting the maximum space that can be consumed by recommendations.

  • Stop at
    Provide the date and time when Database Engine Tuning Advisor should stop.

  • Indexes and indexed views
    Check this box to include recommendations for adding clustered indexes, nonclustered indexes, and indexed views.

  • Indexed views
    Only include recommendations for adding indexed views. Clustered and nonclustered indexes will not be recommended.

  • Include filtered indexes
    Include recommendations for adding filtered indexes. This option is available if you select one of these physical design structures: Indexes and indexed views, Indexes, or Nonclustered indexes.

  • Indexes
    Only include recommendations for adding clustered and nonclustered indexes. Indexed views will not be recommended.

  • Nonclustered indexes
    Include recommendations for only nonclustered indexes. Clustered indexes and indexed views will not be recommended.

  • Evaluate utilization of existing PDS only
    Evaluate the effectiveness of the current indexes but do not recommend additional indexes or indexed views.

  • No partitioning
    Do not recommend partitioning.

  • Full partitioning
    Include recommendations for partitioning.

  • Aligned partitioning
    New recommended partitions will be aligned to make partitions easy to maintain.

  • Do not keep any existing PDS
    Recommend dropping unnecessary existing indexes, views, and partitioning. If an existing physical design structure (PDS) is useful to the workload, Database Engine Tuning Advisor does not recommend dropping it.

  • Keep indexes only
    Keep all existing indexes but recommend dropping unnecessary indexed views, and partitioning.

  • Keep all existing PDS
    Keep all existing indexes, indexed views, and partitioning.

  • Keep clustered indexes only
    Keep all existing clustered indexes but recommend dropping unnecessary indexed views, partitions, and nonclustered indexes.

  • Keep aligned partitioning
    Keep partitioning structures that are currently aligned, but recommend dropping unnecessary indexed views, indexes, and non-aligned partitioning. Any additional partitioning recommended will align with the current partitioning scheme.