You can get recommendations from the Workload Statistics
Advisor, Workload Index Advisor, and Workload Query Advisor.
- Workload Statistics Advisor
- The Workload Statistics Advisor considers a set of SQL statements
in a query workload and looks for missing, incomplete, obsolete, and
conflicting statistics that might lead to suboptimal performance for
the SQL statements in the workload. The Workload Statistics Advisor
provides a consolidated set of statistics recommendations that apply
to the entire workload, and provides RUNSTATS jobs that you can run
to improve the performance of the workload as a whole.
- After collecting accurate and up-to-date statistics for the workload,
you can use the Workload Index Advisor to analyze the indexes for
the workload.
- Workload Index Advisor
- The Workload Index Advisor considers a set of SQL statements in
a query workload, and looks for ways to improve the performance of
the workload, or reduce the amount of disk space used by indexes on
the data server. The Workload Index Advisor recommendations include
DDL scripts for creating indexes to improve the performance of the
workload as a whole, or for dropping indexes to recover disk space,
depending on the scenario that you specify.
- Workload Query Advisor
- The Workload Query Advisor compares SQL statements with a set
of best practices and recommends ways that you might rewrite SQL statements
in the workload to improve the performance of the workload as a whole.