MSAS: Optimization Tuning Part 1 Page - 2

Cube storage Modes influence the degree to which cube query is impacted for the end user. Since in MOLAP storage, the data is copied onto the server and not stored in the relational database, the latter has no impact on the performance of the cube query. The query is faster as it draws its data from the multidimensional structures created and stored in the server independent of the relational database. In ROLAP and HOLAP storage the impact of the relational database performance is felt because, the data is drawn from the relational database itself. Therefore, the relational database performance tuning becomes important if ROLAP or HOLAP storage modes have been selected. Cold caches do not impact on MOLAP stored cubes but definitely impact upon ROLAP and HOLAP cubes. The latter exploit warm caches better even though their performance even with heavy caching is poor.

Dimensional Modeling impacts on performance of queries. It is essential to incorporate the principles of dimensional modeling so that dimensions and fact tables so that the data becomes meaningful to the end user. The star and snowflake schemas generally used, improve cube design and reduce the need for multiple table joins. Design of fact tables should be optimized by deleting duplicate data and by reducing the length of rows.

Any optimization technique that improves the speed of reading data, improves the speed of processing cubes. One important technique that is frequently used is Indexing. Indexes are built on the fact and the dimension tables to facilitate performance of the joins and queries. Every dimension table is indexed on the primary key. Indexes on other columns for identifying levels in the hierarchical structure are also useful while performing specialized queries. The Fact table is indexed on the composite primary key made up of the foreign keys of the dimension tables. Clustered tables are tables which have clustered indexes. The data pages are doubly linked lists and the index is implemented as a B-tree index structure that enables fast retrieval of rows based on the clustered index keys. Heaps are tables with non clustered indexes.

The Index Tuning Wizard allows the user create an optimal set of indexes for a Microsoft SQL Server 2000 database without requiring extraordinary expertise and understanding of the structure of indexes. The primary requirement of the Index Tuning Wizard is the existence of a workload. The workload is a SQL script or a SQL profiler saved to a file or table containing SQL Batch or remote procedure call(RPC) even classes along with the Event Class and Text data columns.

1. The Index tuning wizard recommends the best mix of indexes for a database, given a particular workload. For this purpose it uses the Query optimizer to analyze the queries in the workload.

2. The Wizard analyzes the effects of the proposed changes on the performance of queries.

3. It recommends ways to tune the database for a set of problems

4. It allows the user customize the recommendations by specifying advanced options such as disk space constraints.

The recommendations are SQL statements that can be executed to create new and more effective indexes or drop existing indexes.

However the Index Tuning Wizard does not give any recommendations on tables referenced by cross database queries that are not present in the current database; system tables and primary key constrains and unique indexes. The maximum number of tunable queries in a work load that are considered is only 32,767 queries. Any additional queries will be ignored. Any additional queries with quoted identifiers are also ignored. Since the Wizard gathers data by sampling method, successive executions of the same workload may result in variations in the indexes recommended and also improvements on previous recommendations. When saving the SQL script if the Index Tuning Wizard encounters an error such as lack of disk space, it does not give a message. The index tuning wizard consumes a lot of CPU and memory resources during analysis. Finally if the data in the tables being sampled are insufficient or there is no improvement that can be made to the index, the Wizard does not return any report.

Working with the Index Tuning Wizard

1. On the Enterprise Manager console window expand a server group,

2. Expand the server in which to create the index.

3. On the Tools menu, click Wizards.

4. Double-click Index Tuning Wizard.