Analysis Services Processing Performance

Analysis Services has a very efficient processing architecture and server is capable of processing rows as fast as the data source can provide. You should see processing rate in the ballpark 40-50K rows/sec or even better. One of my customers just bought a new shiny HP ProLiant BL680c server only to find out that processing time went three times higher than the old server. I did a simple test where I asked to execute the processing on both the old server and the new server. The query on the old server would return all rows within 2 minutes, while the same query would execute for 20 minutes which averages to about 4K rows processed/sec. This test ruled out Analysis Services right off the bat. It was clear that the network is the bottleneck. Luckily, the server had a lot of processing power, so processing wasn’t ten times slower.

As it turned out, the company has a policy to cap the network traffic at the switch for all non-production subnets or security and performance reasons. Since the new server was still considered a non-production server, it was on plugged in to a restricted network segment. The moral of this story is that often basic steps could help you isolate and troubleshoot “huge” issues.