Moved from: bobsql.com
As hardware continues to expand and evolve SQL Server testing and customer reports have highlighted the need to partition activities for optimal scaling. Partitioning based designs are common ways to localize activities and improve performance and scalability. An example of how SQL Server leverages partitioning is the CMemThread object.
For thread safety various synchronization mechanisms are utilized (spinlock, latch, mutex, semaphore, …) For discussion purposes let’s focus on a spinlock with imperial, computer industry testing results. A highly contended spinlock does not scale well beyond 8 CPUs. The CPU is utilized and N-1 spinners are unable to acquire the lock, only one owner is