The Database Engine Tuning Advisor (DTA) is the greatly enhanced replacement to the Index Tuning Wizard tool that shipped with previous versions of SQL Server. DTA plays an important role in an overall performance solution, letting you leverage the query optimizer to receive recommendations on indexes, indexed views, or partitions that could improve performance.
Open new session (ctrl + N)
Then you will get a new pane with two options.
1. General 2. Tuning options
In the first tab you need to enter values for few things like session name, workload (file/table), database name, and tables. You can save this tuning log to a particular file.
In the tuning tab, you will get several options such as Limit tuning time (stop at), Physical design structures to use in database, Partitioning strategy to employ, Physical design structures to keep in database.
PDS to use in database - Indexed views, Indexes, nonclustered indexes, and indexes and indexed views. (Objects to be used in the tuning strategy)
Partitioning strategy to employ – No partitioning, full partitioning, and Aligned partitioning
PDS to keep in database – do not keep any existing PDS, Keep all existing PDS, keep align partitioning, Keep indexes only, and Keep clustered indexes only. (Objects to be kept intact)
Once you start analysis you will another 3 options in the pane.
Progress
Recommendations – estimated improvement 0%, Partition recommendations, Index recommendations (Database name, object name, Recommendation (create/alter/drop), target of Recommendation, Details, size, definition (query to make changes))
Reports - Tuning summary (Date, time, server, database(s), workload file, max tuning time, time taken, expected percentage of improvement, max space, used space, number of events tuned), Tuning reports
No comments:
Post a Comment