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 (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:

    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.

  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

    -Slava

  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"?

    Thanks

    Yogesh