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

Article
03/05/2012

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.)

The SQL customer advisory team (SQL CAT) blog post Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications1 discusses input/output (I/O) in relation to Microsoft SQL Server database applications.
The SQL CAT blog post Data Compression: Strategy, Capacity Planning and Best Practices2 explains how the data compression feature in the SQL Server 2008 database software can help reduce the size of the database and improve the performance of I/O intensive workloads. Note that I/O is one of the most common bottleneck in current SQL Server systems.
The white paper The Data Performance Guide 20083 describes techniques for bulk loading large data sets into SQL Server.
The SQL CAT blog posts Tuning the Performance of Backup Compression in SQL Server 20084 and Tuning Backup Compression Part 25 provide guidance related to tuning options for backup performance, and discuss how backup compression interacts with other features of SQL Server 2008.
The white paper Troubleshooting Performance Problems in SQL Server 20086 provides step-by-step guidelines for diagnosing and troubleshooting common performance problems using publicly available tools such as SQL Server Profiler, Microsoft Performance Monitor, dynamic management views, and SQL Server Extended Events and the data collector.
The SQL CAT blog post Resolving PAGELATCH Contention on Highly Concurrent INSERT Workloads7 describes the process of diagnosing a problem and then using table partitioning to work around it.
Managing SQL Server Workloads with Resource Governor 8 is a good starting point for understanding how Resource Governor can help in scaling up. Additionally, review the Support Engineers blog Resource Governor and MAXDOP, Parallel plan generation9 for practical pointers.
The SQL CAT post Scripts and Tools for Performance Tuning and Troubleshooting SQL Server 200510 is a toolbox full of information to make performance tuning and troubleshooting easier.
The SQL CAT blog post Top SQL Server 2005 Performance Issues for OLTP Applications11 discusses some of the top performance bottlenecks for OLTP applications.
See DBCC Checks and Terabyte-Scale Databases12 for best practices on Database Console Command (DBCC) checks and terabyte-scale databases. Note that it is very important to regularly run DBCC in very large databases (VLDBs).
The white paper Service Broker: Performance and Scalability Techniques13 describes a large scale customer scenario and the techniques employed in scaling SQL Server Service Broker to process tens of thousands of messages per second on one server.
The white paper SQL Server 2005 Waits and Queues14 approaches SQL Server performance tuning through a methodology called "Waits and Queues."
The white paper Plan Caching in SQL Server 200815 explains how the SQL Server plan cache operates and how to avoid excessive recompilation.
The article Instant Initialization – What, Why and How?16 explains that instant file initialization (IFI) is very important for large databases. Note that implementing IFI requires consideration of the security implications.
Online indexing operations can help keep the database available while undergoing maintenance operations, as discussed in Online Indexing Operations in SQL Server 2005.17
Windows System Resource Manager 18 is a component that some customers use to manage the CPU resources for various instances of SQL Server executing under a Windows Server instance. Note that we recommend that you do not use Windows System Resource Manager to manage SQL Server memory usage. The SQL Server memory setting configuration should be used to manage the memory usage.

Case Studies and References

Examples of successful architectures are described in the following case studies and white papers:

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