SQL Server Profiler   «Prev  Next»

Lesson 8 Index Tuning Wizard
Objective Use the Index Tuning Wizard.

Index Tuning Wizard

The Index Tuning Wizard (ITW) has been officially deprecated by Microsoft starting in SQL Server 2012. It's no longer included in SQL Server 2022. Here's what replaced it:
The Database Engine Tuning Advisor (DTA)
The Database Engine Tuning Advisor is the primary tool recommended by Microsoft for index analysis and tuning in SQL Server 2022 and earlier versions. The DTA offers a more advanced and sophisticated approach:
  • Workload Analysis: The DTA analyzes a workload of SQL statements (either from a file or by directly tracing SQL Server activity) to get a comprehensive view of database usage.
  • Recommendations: Instead of just index suggestions, the DTA can recommend:
    • New indexes
    • Removing existing indexes
    • Partitioning strategies

How to Use the DTA in SQL Server Management Studio (SSMS)
  1. Right-click on the database "Select Tasks" -> "Database Engine Tuning Advisor".
  2. Workload: Choose your source (file or trace)
  3. Tuning Options: Configure what types of recommendations you want the DTA to make.
  4. Start Analysis: After analysis, the DTA will present its tuning recommendations.

Advantages of DTA over ITW
  • Deeper Analysis: The DTA takes a more holistic approach to performance optimization.
  • Beyond Indexes: Recommendations can go beyond simple index creation.
  • Modern Tool: As a more actively maintained tool, the DTA better aligns with current SQL Server best practices.

After you have found the queries that you believe are causing your performance problems, you can use SQL Server Profiler's Index Tuning Wizard to find out how you can improve performance.

Starting the Index Tuning Wizard

You can start the Index Tuning Wizard by selecting Tools-> Index Tuning Wizard from the menu. The following series of images demonstrate the Index Tuning Wizard.

Index Tuning
1) The index tuning wizard welcome screen describes what the index tuning wizard can do.

The select server and database screen allows you select what SQL Server and Database to tune.
2) The select server and database screen allows you select what SQL Server and Database to tune. Here, you can also decide on the level of analysis to perform.

The Identify Workload screen allows you to identify
3) The Identify Workload screen allows you to identify how the transactions that will be used to tune the database will be generated.

Specify Workload screen
4) The Specify Workload screen allows you to specify where the transactions are located.

The Select Tables to Tune screen allows you to specify which tables in the database you want to tune.
5) The Select Tables to Tune screen allows you to specify which tables in the database you want to tune.

The Evaluation screen appears while SQL Server is evaluated what indexes are needed.
6) The Evaluation screen appears while SQL Server is evaluated what indexes are needed.

Index Recommendations screen list the indexes recommended by the Index Tuning Wizard.
7) The Index Recommendations screen list the indexes recommended by the Index Tuning Wizard.

The Schedule Index Update Job screen allows you to schedule any changes you want to take place.
8) The Schedule Index Update Job screen allows you to schedule any changes you want to take place.


Warning: You may find that adding the recommended indexes will not always increase your performance. And, sometimes, adding more indexes can actually reduce the performance of your inserts and updates.
In the next lesson, you will learn how to use the Trace Wizard.

SEMrush Software