This article peaked our interest.
Highlights:
1. SharePoint doesn’t like big iron.
After looking at our test results as well as collecting their own data, Microsoft SharePoint® Support indicated that SharePoint® was apparently unable to make use of such large hardware (8 processors with 16G of RAM). In an effort to validate that the problem was indeed caused by the large hardware, they recommended that we reduce the number of processors to 4, and then later suggested reducing it to 2. In each case, this resulted in a surprising performance improvement but the stalling behavior remained.
2. Tempdb database contention in SQL Server.
After additional testing and data gathering, Microsoft Support engineers found that contention on tempdb allocations within SQL Server was causing delays processing queries from SharePoint®. This problem is described in the Microsoft Knowledge Base (#328551).
The fix required creating additional tempdb databases within SQL Server (one for each processor) and enabling a startup parameter (-T1118) that instructed SQL Server to use a round-robin tempdb allocation strategy. This change reduced resource allocation contention in the tempdb database, improving performance on complex queries.
3. Single-threaded cache access in a multi-processor system.
…problems with the size of the TokenAndPermUserStore cache in SQL Server. When the server has a large amount of physical memory (in this case 32G) and the rate of random dynamic queries is high, the number of entries in this cache grows rapidly. As the cache grows, the time required to traverse and cleanup the cache can be substantial. Because access to this cache is single-threaded, queries can pile up behind each other waiting for the cleanup to complete. This queuing slows performance and prevents a multi-processor system from scaling as expected. The remedy was to start SQL Server with a “-T4618” parameter, which limits the TokenAndPermUserStore cache size. (This was not one of the solutions listed in the Microsoft Knowledge Base for this issue – it was provided by a Microsoft Support Engineer).
