Scale-Up (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

Most applications are designed for good scale-up performance because the design techniques for scale-up are reasonably well understood and are easier to develop and deploy than those for scale-out. (For example, review the bWin case study to understand the scale-up capability achieved by a customer - the link is provided below.)

However, more and more scale-out architectures are being adopted for large-scale deployments in which the expected number of concurrent users is huge and the total cost of ownership (TCO) must be kept low. Good design for scale-up is of utmost importance for both scale-up and scale-out because individual segments of a federated database or application have to perform well for the entire federation to be scalable at a reasonable cost.

The system scalability is dependent on several components, including: the database management system (DBMS) engine, the configuration settings, the database schema, and application design. Some would argue that the database schema and the application design are more important that the database engine component. Therefore, be sure that the application component gets sufficient emphasis and resources to make the system scalable.

Best Practices

The following resources provide examples of customer scenarios for scale-up solutions, in addition to general scale-up reference material. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document.)

Case Studies and References

Questions and Considerations

This section provides questions and issues to consider when working with your customers.

  • Experience shows that when new systems are tested and deployed, installations frequently encounter scalability issues in this sequence (though note that there are always exceptions):

    • Application scalability issues

    • Database schema and configuration issues

    • Internal database management system (DBMS) issues.

    As you architect new systems, be watchful for factors that impact these areas.

  • Ask and look for data access patterns that adversely impact scalability, including the following:

    • A large number of concurrent users can lead to locking contention

    • Accessing unnecessary data can lead to wasted resources

    • Concurrently updating data can lead to contention

    • Appropriate indexing can avoid long-running queries and avoid blocking issues

    Anticipate and design with these factors in mind (though note that this is not an exhaustive list). There are also issues internal to the DBMS that can prevent scalability (for example, latches, spin locks, and hash tables), and the application architect has little control over these. For information on how to anticipate and avoid the DBMS becoming a bottle neck for an application system, see the next consideration.

  • It is useful to perform an actual benchmark with some skeletal code to help ensure successful scalability; however, this may not be practical in many cases. Another good approach is to look for an existing equivalent benchmark; this is often a practical approach for an independent software vendor (ISV) application. If, for example, ISV package XYZ is used by installation A to serve 2 million users and installation B to serve 5 million users, you can conclude that with proper execution, installation C can also serve 4 million users and you can estimate the required hardware resources.

  • Do not forget the standard operations, like backup, restore, and CHECKDB, or features like database compression, backup compression, and resource governance. These can help with scalability and with meeting maintenance windows.

  • I/O subsystem performance has a major influence on scalability. For more information, see the SQL CAT blog post Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications1


Following are the full URLs for the hyperlinked text.

1 Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications

2 Data Compression: Strategy, Capacity Planning and Best Practices

3 The Data Performance Guide 2008

4 Tuning the Performance of Backup Compression in SQL Server 2008

5 Tuning Backup Compression Part 2

6 Troubleshooting Performance Problems in SQL Server 2008

7 Resolving PAGELATCH Contention on Highly Concurrent INSERT Workloads

8 Managing SQL Server Workloads with Resource Governor

9 Resource Governor and MAXDOP, Parallel plan generation

10 Scripts and Tools for Performance Tuning and Troubleshooting SQL Server 2005

11 Top SQL Server 2005 Performance Issues for OLTP Applications

12 DBCC Checks and Terabyte-Scale Databases

13 Service Broker: Performance and Scalability Techniques

14 SQL Server 2005 Waits and Queues

15 Plan Caching in SQL Server 2008

16 Instant Initialization – What, Why and How?

17 Online Indexing Operations in SQL Server 2005

18 Windows System Resource Manager

19 bwin: Global Online Gaming Company Deploying SQL Server 2008 to support 100 Terabytes

20 The Progressive Group: Progressive Prepares for Future Growth, Gains Agility with SQL Server 2005

21 A Technical Case Study: Fast and Reliable Backup and Restore of Multi-Terabytes over the Network

22 Backup More Than 1GB per Second Using SQL2008 Backup Compression