Transactional (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.

Online transaction processing (OLTP) systems, often simply referred to as transactional systems, are designed to process small, quick, interactive workloads for which users expect a fast response time. For such workloads, it is generally expected that atomicity, consistency, isolation, and durability (ACID) properties will be maintained and response time will be less than one second. These are often business-critical workloads that require high levels of performance, security, availability, and, in case of a disaster, near-zero data loss. Often these systems are at the very core of business operations, and any downtime, data loss, or security breach can impact business operations and result in unpleasant publicity. The demanding requirements for these business-critical systems drive system design and development, and the tasks become more challenging with large volumes of data, users, and transactions rates. For such systems, it is expected that stress testing or benchmarking will be done to ensure that the system can scale to the desired projected volumes.

To achieve these demanding goals and still operate within constrained budgets drives designers to look for creative solutions, which are adopted based on the project needs. Such solutions include scaling-out the transactional workload, assigning servers for read-only workloads, dropping ACID requirements for some workloads (for example, shopping cart data), asynchronous processing of some work, and relaxing disaster recovery requirements for some data domains.

Best Practices

The following resources provide some best practices and some pitfalls to avoid when using transactional systems. (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 Top SQL Server 2005 Performance Issues for OLTP Applications1 also applies to Microsoft SQL Server 2008.
The SQL CAT blog post Scripts and Tools for Performance Tuning and Troubleshooting SQL Server 2005 ,2 which provides scripts and stored procedures for managing and working with SQL Server 2005, also applies to SQL Server 2008.
The article Diagnosing Transaction Log Performance Issues and Limits of the Log Manager3 is helpful because log volume and commit frequency can often have a negative impact on the performance of transaction systems.
If you are using the Microsoft Distributed Transaction Coordinator (DTC), review the article Resolving DTC Related Waits and Tuning Scalability of DTC.4 Note that DTC transactions are slower, so you should use them only if necessary.
The article Initializing a Transactional Replication Subscriber from an Array-Based Snapshot5 describes how to initialize a transactional replication Subscriber from an array-based snapshot rather than by using the native Microsoft SQL Server snapshot mechanism. Note that initializing the Subscriber using a storage area network (SAN)-based restore solution is particularly beneficial for very large databases.
For tips on troubleshooting deadlock situation, see Analyzing Deadlocks with SQL Server Profiler6 and Deadlock Troubleshooting, Part 1.7

Case Studies and References

SQL Server has been deployed for many tier-one OLTP and data warehousing customers. Examples include the following:

Questions and Considerations

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

Fully understand user requirements and use scenarios, including the following:
- The number of concurrent users
- Transactions
- Performance and scalability
- High availability/disaster recovery (HA/DR) requirements, including windows for completing maintenance tasks
Also review the Technical Reference Guide for Operational (OLTP) systems, because some customers use the terms "transactional” and “operational” interchangeably. Note that the Considerations and Best Practices sections in the guide for operational systems also apply to transactional systems.
Understand the HA/DR requirements for the system. For tier-one applications, there can be very stringent data availability requirements, such as no data loss, necessitating design considerations dictated as much by HA/DR as by the performance requirements. Refer to the Technical Reference Guides on HA/DR for additional information.
Determine whether the transactional system will run concurrent with reporting. Some degree of concurrent reporting is common because reporting replicas might have an unacceptable latency for some reports. Consider using Read Committed Snapshot Isolation (RCSI), but note that using RSCI does bring associated trade-offs. RSCI might help with the potential blocking issues, but it will not help with the resources required for the combined transactional processing and reporting. It might be necessary to offload the reporting workload to another server if lack of resources becomes an issue. Some approaches for creating a second copy for reporting include:
- Database mirroring
- Log shipping
- SAN-based replication
- Transactional or peer-to-peer replication
Choosing between scale-up and scale-out is a critical architectural decision. Understand whether the customer intends to interoperate with, or migrate to, cloud-based systems. Refer to the scale-up and scale-out Technical Reference Guides for additional information.
Determine how the current deployment compares with previously deployed versions. Understand hardware requirements and operational considerations. If using an independent software vendor (ISV)-provided system, it is more likely that other installations have used the application before, and based on those experiences, capacity planning information can be more realistic and dependable.
Blocking and deadlocks can occur with increasing volumes of concurrent transactions and on the data access pattern. As discussed above, RSCI can help with blocking issues. Generally, reducing large numbers of deadlocks requires application code access path patterns.