Couple of weeks ago I did a talk on SQL Server 2005 scalability. The actual talk was not about the enormous amount of features that you can leverage in SQL Serve 2005 to make your application scale but rather how SQL 2005 achieves its scalability. In this post I present a set of talking points of the talk. Please let me know if you have questions.

Enjoy!

 

SQL Server 2005 Scalability

“If you remove all global state your scalability will be linear”

 

SQL Server 2005 Scalability

nSQL Server 2005 is designed to scale!

nNo matter how good database engine is if your application is designed to use global state – you will hit a bottleneck sooner or latter

nTo get your application to scale – remove completely or at least partition global state your application is using

 

What is SMP

nSMP – Symmetric Multi-Processing

nFront-bus point of contention

nDifficult to scale beyond 32 CPU; Why? (Because of inherit global resource – front bus)

 

What is NUMA

nNUMA – Non-Uniform Memory Access

nMinimize/eliminate front-bus contention to surpass scalability limits of SMP architecture

nPerformance penalty for accessing foreign node memory

n Server application such as SQL Server need to be NUMA-aware to take advantage of the node-locality design

nNUMA scales; Why? (Because it partitions global resource – front bus)

 

What is Interleaved-NUMA

nEnable NUMA hardware to behave as SMP

nMemory allocated from all nodes to average out memory access penalty

nSQL Server 2000 should use interleaved-NUMA

 

What is Soft-NUMA

nActivates custom SQL Server NUMA configuration on top of any of hardware

nRegistry settings control final SoftNUMA configuration

nProvides greater performance, scalability, and manageability on SMP as well as on real NUMA hardware

 

Effect of NUMA on Buffer Pool

nBuffer pool will use remote (foreign) memory if necessary

nMin/Max memory divided per node

nE.g., 16gb max memory on 4-node NUMA, per node max is 4GB

nAffinity mask change resulting in offlining node will redistribute min/max memory on remaining nodes, need to reconfigure min/max setting

nNew performance counter objects – SQL Server: Buffer Nodes

 

Application Partitioning

nProvides resource partitioning of such resources as CPU and memory across different application for single SQL Server instance

nAchieved by leveraging either NUMA or SoftNUMA configuration along with other new SQL Server features

nEnables predicted resource distributions amongst different applications

nEnables soft application isolation with better performance characteristics than multi-instance