Talking points around SQL Server 2005 scalability
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