How to Improve Partition Processing Performance

Most of the times I found question on the forums “how to improve partition processing performance ?”. The answer to this question is a bit lengthy but we can improve processing performance by doing some simple techniques. So let’s discuss some techniques which really plays a vital tole in improving Processing Performance.

• If the size of your each partition is very large then split it into smaller partitions (you can try to create partitions on year, Quarter or month basis but that depends upon the size of your data). If you create multiple partitions,so while processing a corresponding measure group analysis services process all partitions under that measure group in parallel which helps in improving processing performance. 

•  If you want to update your partition only with newly added rows then its best approach to create a partition with new data only and perform ProcessFull on that partition. So this helps you to add new rows without impacting existing partitions. Even after processing, you can merge newly added partition with existing one. 

•  If your underlying fact table is not properly optimized for good performance then that degrades processing performance as well. So create proper indexes on underlying tables, so the data get fetched quickly while processing which helps in improving processing performance. 

• Instate of ProcessFull, it is always recommended to use ProcessData followed by ProcessIndex, it helps in improving processing performance. 

• If you are using 32-bit of windows then the maximum amount of memory that 32-bit Analysis Services can address is 3 GB regardless of the amount of actual memory that is installed on the computer.Now Suppose the actual memory that is installed on the computer is 4 GB but if you are using 32-bit of windows,analysis services can address only 2GB of memory but you can maximize it upto 3GB by using /3GB switch in the boot.ini file. To enable Analysis Services to address more than 2 GB of physical memory, use the /3GB switch in the boot.ini file. If your ssas database is very large or complex and your analysis services needs more than 3 GB memory then you can use SQL Server (64-bit) which allows the analysis services to access more than 3 GB of memory. 

• If you are using 64-bit versions of Windows Server 2003, then you can preallocate the memory for analysis services by using "PreAllocate" setting in msmdsrv.ini file. PreAllocate allocates a certain percentage of memory to analysis services when the service starts.You can refer this article for more details; http://sqlcat.com/technicalnotes/archive/2008/07/16/running-microsoft-sql-server-2008-analysis-services-on-windows-server-2008-vs-windows-server-2003-and-memory-preallocation-lessons-learned.aspx

• Even check SSAS2008 Performance guide; http://www.microsoft.com/downloads/en/details.aspx?FamilyID=3be0488d-e7aa-4078-a050-ae39912d2e43&displaylang=en