Adding Cache Partitions to Reduce Spinlock

On multiengine servers, more than one task can attempt to access the cache.

By default, each cache has a single spinlock, so that only one task can change or access the cache at a time. If cache spinlock contention is more than 10 percent, increasing the number of cache partitions for a cache may reduce spinlock contention, which improves performance.

To configure the number of cache partitions for:

All data caches – use the global cache partition number configuration parameter
An individual cache – use sp_cacheconfig

The number of partitions in a cache is always a power of 2 between 1 and 64. No pool in any cache partition can be smaller than 512K. In most cases, since caches can be sized to meet requirements for storing individual objects, you should use the local setting for the particular cache where spinlock contention is an issue.

See Performance and Tuning Series: Basics > Reducing spinlock contention with cache partitions.

Setting the Number of Cache Partitions

Use sp_configure to set the number of cache partitions for all caches on a server.

For example, to set the number of cache partitions to 2, enter:

sp_configure "global cache partition number",2

You must restart the server for the change to take effect.

Setting the Number of Local Cache Partitions

Use sp_cacheconfig or the configuration file to set the number of local cache partitions.

This command sets the number of cache partitions in the default data cache to 4:

sp_cacheconfig "default data cache", "cache_partition=4"

You must restart the server for the change to take effect.

Setting Precedence

The local cache partition setting always takes precedence over the global cache partition value.

These examples set the server-wide partition number to 4, and the number of partitions for pubs_cache to 2:

sp_configure "global cache partition number", 4

sp_cacheconfig "pubs_cache", "cache_partition=2"

The local cache partition number takes precedence over the global cache partition number, so pubs_cache uses 2 partitions. All other configured caches have 4 partitions.

To remove the local setting for pubs_cache, and use the global value instead:

sp_cacheconfig "pubs_cache", "cache_partition=default"

To reset the global cache partition number to the default, use:

sp_configure "global cache partition number", 0, "default"