The SQL Server Query Analyzer is a feature rich Query Analyzer. The tool can provide a graphical representation of a query and helps the user mould queries into more efficient shapes. It also helps identify faulty queries. +To Tune the index created the user will have to open the SQL query analyzer and open a query or enter one.

On the Query menu click on Index Tuning Wizard and follow the steps of the wizard. On the Specify workload screen select SQL Query Analyzer Selection and click Next.

Select the tables and their indexes

View the recommendations made by the Wizard.

On click Analysis button the users can see the various reports on Index usage, Table Analysis etc.

Once the indexing of the dimension tables is done and optimized the cube processing becomes faster. The cubes can be further optimized using the Usage Analysis Wizard and Usage based Analysis Wizard to fine tune aggregations.

Aggregation selection is an art that has to be refined over time with usage pattern analysis and data analysis.

Next Page: Tutorial 47: MSAS: Optimization Tuning Part 2 - Page 2