Analysis Services Thread Pool Changes in SQL Server 2012

As part of the SQL Server 2012 release several changes were made to Analysis Services that should alleviate some issues previously seen on large NUMA node machines.

Separation of IO and Processing Jobs

To better understand how the changes that were made work, it is helpful to have some background information about what types of issues were seen in earlier releases.

During testing, it was found that a 2 NUMA node 24 core server was handling roughly 100 queries per second during stress testing. The same tests were then run on a 4 NUMA node server with 32 cores and the queries answered per second actually decreased. Investigation indicated the cause as cross NUMA node access to the system file cache. For example if the first job that read a file was scheduled on a CPU assigned to NUMA node 1 and a subsequent job, which needed to read the same pages from the file, was scheduled on a CPU assigned to NUMA node 3, a significant performance penalty was seen reading the cached file page. Deeper investigation determined that the bottleneck appeared to be in accessing the standby list of file cache pages. The performance impact appeared to become more pronounced the higher the number of CPUs and NUMA nodes on the machine. For machines with less than 4 nodes the impact was not found to be significant. However, on machines with 4 or more nodes the impact was such that performance could begin to regress.