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.



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 (10)

  1. 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 ….

  2. slavao says:

    You are exactly right – partitioning is one of the ways of getting rid of global state.

  3. kirchner says:

    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?

  4. ybhalerao says:


    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 🙂


    Yogesh Bhalerao

    Scalability Experts, Inc.

  5. slavao says:

    One can define global/shared state as a set of data/resources that are referenced/updated by a number of instances in a system.  As a number of instances grow the global state becomes more and more problematic, i.e scalability bottleneck. Examples of global/shared state include: CPU front bus, shared CPU cache, global counter, global data cache. In SQL Server Application world global/shared state can include a table, index, filegroup and etc.

    One way of avoiding global state problem is to partition it. For example in SQL Server 2005 lots of our shared state is partitioned even though it might appear as global that includes parts of lock manager, memory manager, buffer pool, procedure cache and others.  In some cases you can achieve partitioning yourself even if we didn’t do it for you,  For example procedure cache has some partitioned logic but sill has global state, hash table ,you can logically partition set of your stored procedures from application end by adding a node id to a procedure name and pretty much "eliminate" global hash table. This approach will require more memory but that is usual tradeoff memory for performance/scalability

    Hope this helps


  6. Purely from a theorotical perspective, SQL Server running on a server consists of many small active processes/threads each of them co-ordinate/communicate using a data stored in a shared data space (memory). Simply put memory is a tuple space and the objects in the memory (locks; execution plans; hash tables, etc…) as tuples, a set of tuples is called a state. This is the characteristics of a data-driven co-ordination model.

    Now scalability is dependent on two things, how the tuple-space is distributed and how we search in it. Ignoring how we search in it for now, scalability depends only on how it is distributed. There are many (to be precise 4) ways of distributing the try tuple-space and one of the way is to partition it.

    Slava described the partitioning mechanism very beautifully. Thus the tuple-space(memory) is partitioned such that each partition (lock manager, memory manager, buffer pool, procedure cache, memtoleave) contains certain sets of tuples(the procedure cache contains all query execution plans).

    Now when we search for a execution plan, we are not searching in the entire tuple-space (memory) but we are only searching locally(Procedure Cache). Thus we have successfully eliminated a global state (set of tuples: execution plan+execution context). Because of which the algorithm(and hence the time) to find a tuple (say query execution plan) is not dependent on the how many different types of tuples are there in the system, but only on the number of execution plans in the Procedure Cache and number of plans required, thus making this system scalable linearly (increasing the procedure cache size will increase the no of execution plans stored by an equal proportion, assuming all execution plans are of same size)

    "If you remove all global state your scalability will be linear" interesting …

    Did I understand that correctly Slava?

    Yogesh Bhalerao

  7. Yogesh Bhalerao says:

    So is SQL Server shared data space (memory) put memory a tuple space and the objects in the memory (locks; execution plans; hash tables, etc…) as tuples? In that case "a set of tuples is called a state"?