Index Tuning Wizard

Step 2: The Tuning Wizard.

After we close the SQL Profiler we are back in the Enterprise Manager and here we are going to run a Wizard as shown in Figure 8.

From the multitude of Wizards, some call this screen the Wizards Wizard, expand the Management group and select Index Tuning Wizard and click OK.

As with all wizards, a description pops up and we click Next after reading it.

First up we need to tell the Wizard which Server instance and which database to tune, I'm tuning my FIS_Portal database, and we tell it to Keep all existing indexes (since I had none) and offcourse I select Add indexed views and I set the tuning mode to Thorough, which takes the longest but has the best results.

After we click Next we need to specify our workload, this is the table created in Step 1, so we setup our database and tell what table to use and we click on OK.

Next we can finetune the nr of queries to run and since I know that 200 queries out of my workload will cover every possible table in my Database I will limit it to 200 and I will use the other default settings as well.

In Figure 14 we are asked what tables should be analysed and since I do not care how long it takes I select ALL tables, I rather spend 5 minutes longer waiting for the tuning process to finish then find out later that some tables are still slow with returning data, so ALL tables it is.

Upon pressing Next the Index Tuning Wizard is going to do it's job, it's going to re-execute the Workload data and examine all execution plans that accompany these queries and based on this info the Tuning Wizard returns Figure 16.

Figure 16 shows a complete list with recommended indexes, as you can see in Figure 16 for some tables it recommends a NON-Clustered index, this is most likely because there's no data in those tables yet and therefor it suggests the cheaper index.

When we click on Analysis we get more detailed information on how indexes were used, you can even save this report for future benefit.

When we close this report and click next we again get the last screen in the Wizard, Figure 18, which gives you a last opportunity to CANCEL or go back to make changes.

Now when we press complete all our indexes will be created for us and that concludes this tutorial about the Index Tuning Wizard.

Goto Part 1

Read more about Clustered and NON-Clustered indexes

Read more about the Index Tuning Wizard