TFS reports might be slow or inaccessible during cube processing on a busy TFS 2010 server

  • Article

In Dev10, we did load runs on these 4 configs:

Load Test Config TFS config Model CPU Memory Disk Active TPC Vusers # of agent
1 Single server Dell PowerEdge 860 1P 2.1 3 Ghz 2GB 1 x 7.2K rpm (500 GB) 1 3 1
2 Single server Dell PowerEdge 860 2P 2.13 Ghz 4GB 1 x 7.2k rpm (500 GB) 10 6 2
3 Dual server AT: Dell PowerEdge 860DT: HP ProLiant DL380 G5 AT: 2P 2.13 GhzDT: 4P 2.33 Ghz AT: 4 GBDT: 8 GB AT: 1 x 7.2k rpm (500 GB)DT: 2 x 10K rpm SAS (292 GB) and 17 x 10k SAS (2.4 TB) 30 30 6
4 Dual server AT: Dell PowerEdge 860DT: HP ProLiant DL380 G5 AT: 4P 2.13 GhzDT: 8P 2.33 Ghz AT: 8 GBDT: 16 GB AT: 1 x 7.2k rpm (500 GB)DT: 2 x 10K rpm SAS (292 GB) and 17 x 10k SAS (2.4 TB) 60 60 6

One issue we found is that on config #3 and config #4, a lot of tests that access TFS reports timed out. The issue is that AS has four thread pools. Cube processing uses threads from the Processing thread pool. But queries also need to use threads from this thread pool. So if AS is starved for threads, you can get very poor query response time during cube processing. Increasing the max size of this thread pool prevents this starvation, and allows queries to continue running without much degradation in performance during cube processing. There is a document on MSDN called Analysis Services Performance Guide that includes the follow suggestion on setting this value:

Situation

Action

Processing pool job queue length

Increase Threadpool\Process\MaxThreads and retest.

The best way to see this is to use Performance Monitor to watch these two performance counters and increase the number of threads so it's just a little bit higher than what's being used. We tried this on two machines. Pioneer had MaxThreads set to 120. Setting it to 150 is high enough to allow queries to run and not get blocked while processing the cube. On another server, the default was 80, and setting it to 100 was enough of this machine.

You can follow these simple steps to configure your AS properly:

  • First measure how many threads are being used during cube processing:
    • Open the Performance (Server 2003) or Performance Monitor (Server 2008) application
    • Remove all counters
    • Add a performance counter
      • Click on the Add button
      • Open the MSAS 2008:Threads section
      • Add the Processing pool busy threads performance count
    • During cube processing, see how high this goes.
  • Check/change the thread limit:
    • Open SQL Server Management Studio
    • Connect to your Analysis Services machine
    • Right click on the AS instance (not the database) and click Properties. This displays the Analysis Services Properties dialog box
    • Check the box Show Advanced (All) Properties
    • Near the very bottom is a property called ThreadPool \ Process \ MaxThreads. If this value is close to the maximum number of threads used during cube processing, increase the value in the Value column and try again. You want this number to be high enough so cube processing doesn't or only very briefly reaches this limit. You might start by increasing it 10%.

Have a good day.

 Jiange