As the number of engines and tasks running on an SMP system increases, contention for the spinlock on the data cache can also increase. Any time a task needs to access the cache to find a page in cache or to relink a page on the LRU/MRU chain, it holds the cache spinlock to prevent other tasks from modifying the cache at the same time.
With multiple engines and users, tasks wind up waiting for access to the cache. Adding cache partitions separates the cache into partitions that are each protected by its own spinlock. When a page needs to be read into cache or located, a hash function is applied to the database ID and page ID to identify which partition holds the page.
The number of cache partitions is always a power of 2. Each time you increase the number of partitions, you reduce the spinlock contention by approximately 1/2. If spinlock contention is greater than 10 to 15%, consider increasing the number of partitions for the cache. This example creates 4 partitions in the default data cache:
sp_cacheconfig "default data cache", "cache_partition=4"
You must reboot the server for changes in cache partitioning to take effect.
For more information on configuring cache partitions, see the System Administration Guide.
For information on monitoring cache spinlock contention with sp_sysmon, see “Cache spinlock contention” on page 89 in the book Performance and Tuning: Monitoring and Analyzing for Performance.
Each pool in the cache is partitioned into a separate LRU/MRU chain of pages, with its own wash marker.
