articles >> business intelligence >> Check List for Performance Optimisation in SQL ...

Check List for Performance Optimisation in SQL Server Analysis Service 2005

By : Dinesh Asanka Jan 23, 2008

Page 3 / 3



Process Scheduled Cube Process in Command Line
Most of the time, you need to configure your cube to process at a pre-defined schedule. You have the luxury of using the SQL Server Integration Services (SSIS) package to process the cube by giving a schedule. If you are running the SSIS package at the command prompt it will be much faster. You can execute the SSIS package by following command.

DTEXEC /f <Package Name with full path>

Partitioning
SSAS uses partitions to contain cube data. Small cubes might use one part, while for medium and large scale cubes may be split into groups of data which is called partitioning.

As a practice one partition should have 2GB or 15 million rows for effective performance. Unnecessary partitioning may degrade you cube performance instead of improving it. Also too many partitions makes it difficult to manage.

A point to note about Partitioning in SSAS is that this feature is available only with enterprise and developer editions of SQL Server.

Configuring SSAS
You have the ability to install SSAS on a server which has multi-processors. Doing so allows several partitions to be updated in parallel. 

Ideally you can configure SSAS and the database services on two separate servers.  In the scenario where only one server is available there will be competition for resources which you can be eliminated by using two servers. However, you need to make sure you have good network connectivity between the two servers. Otherwise any performance gains will be reduced. Something to consider before using two servers is the possible licensing implications.

Make sure that you install the latest SQL Server 2005 Service packs and the appropriate patches. For example, SP2 of SQL Server 2005 has fix for the following bu, SQL Server Analysis Services 2005 system performance may be slow after you use the ADOMD.NET data provider to run an MDX query that returns a large result set from a cube (KB 919731).

Summary
The following is the summary of the actions and the effect of that action. You can see that what the benefits that you will achive after performing each action.

Actions	Cube Processing	Query Performance	Disk Resources
Data Length and Sizes	x	x	x
Key Attributes	x	x
Removing Unnecessary Attributes	x		x
Set Attribute Hierarchy Optimized State to FullyOptimized	x		x
Turn off Attribute Hierarchy	x		x
Define Hierarchy Relationships between attributes	x	x
Partitioning		x
Selecting Appropriate Process Type	x
Cube Process at Command Line	x
Configuring SSAS	x	x