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
Comments
Anonymous
August 08, 2006
what about partitioning?
IMHO partitioning is what allows a DB to scale above certain levels .... Sybase in it's latest edition has some very clever partitioning you should have a look at ....Anonymous
August 21, 2006
You are exactly right - partitioning is one of the ways of getting rid of global state.Anonymous
August 21, 2006
Hi Slava,
Can you give some examples of how to remove global state in an application?
Does that mean something like not using Cache object on ASP.NET, for instance?
And, in SQL Server, can it be done? I think that lock table, data pages cache, plan cache, etc, need to be global. Am I wrong?
What king of global state can you remove in SQL Server? Can you exemplify?Anonymous
September 09, 2006
Slava,
Thanks for this article on SQL Server Scalability. So when I buy the next SQL Server version who knows it might be full of ANTS :)
Thanks
Yogesh Bhalerao
Scalability Experts, Inc.Anonymous
September 28, 2006
The comment has been removedAnonymous
October 04, 2006
The comment has been removedAnonymous
October 09, 2006
The comment has been removedAnonymous
July 27, 2008
PingBack from http://www.sqlserverfeeds.com/534/talking-points-around-sql-server-2005-scalability/Anonymous
June 01, 2009
PingBack from http://uniformstores.info/story.php?id=15744Anonymous
June 01, 2009
PingBack from http://paidsurveyshub.info/story.php?id=73124