Behind any data-driven web application is a database in some form or another, making database performance critical for any growing application.
Queued processing can substantially increase the performance and scalability of databases that drive a web application. This article provides an overview of the queuing technologies in SQL Server 2005 & 2008 and offers real-world examples for a clearer understanding of how they can be used.
Overview
It’s common that a web application was designed with a certain amount of traffic and users in mind. Often the infrastructure starts off pretty simple- a single web server and one centralized database server. This clearly has limitations in the amount of traffic and the number of user requests that can be handled at any given time but may support the current amount of traffic.
But what’s also common is that an application grows beyond the initial requirements and is now straining under a much heavier load than originally estimated. So the application needs to be scaled out to continue being fast and reliable. Unfortunately, scaling out existing applications is generally a long-term strategy and often it requires a major redesign to be done right. You might need to buy yourself some time with a short-term strategy first.
Short-Term Scaling Strategy
A short-term strategy might include some or all of the following:
- Scaling out your web server
It is quite easy and quick to scale out your web server to be able to handle more traffic with little impact on the existing application. This mainly entails building one or more additional web servers that point to your database server, and employing load balancing.
- Caching
By caching configurations and other static information on your web servers, you can reduce the backend traffic and database load.
- Scaling up the database server
Hardware is generally cheap, and adding resources to your database server can make a significant difference in terms of the number of requests your database server can handle at any given time.
- Fine-tuning the database
Indexes, locking, queries and other aspects of the database should be reviewed and analyzed to obtain optimal performance.
The items above are well known and can provide your application with a performance boost depending on its architecture and the nature of its usage. However, the biggest bottleneck in this type of architecture is generally the database. Scaling it up or fine-tuning its performance can only do so much.
Queued Processing: Service Broker
Fortunately, there is another way to increase the number of requests that your database can handle.First introduced in SQL Server 2005 and then enhanced in SQL Server 2008, Service Broker is a queuing infrastructure for queuing database requests. It allows database servers to process more requests than they would otherwise be able to.
A full description of the Service Broker’s architecture and capabilities is beyond the scope of this article, and you can find quite a few articles that describe these on Microsoft’s website and on many blogs. Instead, this article focuses on the scenario described above and the potential approach you can employ to solve or at least mitigate your load issues.
Simply put, Service Broker can be used to capture various database requests and rather than processing them in real time they would be queued and processed at a later time. This enables you to monitor the number of requests that the database needs to handle at any given time, and therefore, would increase the web application’s performance and scalability.
Assessing Possible Queued Transactions
As queued processing is not appropriate for just any transaction, you should start by analyzing the application and first determine how much the application can actually benefit from it, and what impact queued processing might have on usability.
To do this, try to identify those database transactions that do not necessarily require real-time processing. Determine those that are currently taking a big toll on database resources, whether because a transaction requires a relatively large amount of resources to execute or if it is being executed by a large number of users at the same time.
The following are some transactions that might be good candidates for queued processing:
- Reporting
Rather than showing the user the requested report immediately, you might be able to send the report via email at a later time.
- Filling Out Forms
If a user doesn’t need to see the results of the data he enters, the application can store the form’s data in Service Broker, and apply it to the database at a later time.
- Back-End Processing
Many applications require back-end processing once a particular user action takes place. This might involve recalculating certain data, updating related records, etc. This can often be queued and executed in sequentially later on.
- Emails & Alerts
If your application sends many emails, such as informing users of actions taken against their data for example, you can easily delay and limit the number of emails your database needs to process at any given time.
- Batch Processing
Rather than processing full batch operations at any given time, you can queue each item in the batch and process these in a controlled manner over a much longer period of time.
Real-World Use Case: Scopings
Scopings, our homegrown recruiting platform, was designed to scale out its databases from day one. Nevertheless, one of the goals we set for ourselves was to also use our database servers to their fullest potential. This would help reduce both the number of database servers the platform requires and the hardware requirements for each server.
A full description of Scopings would be long, but for the purpose of this article we can describe Scopings as a platform that extensively uses matching and workflow technologies to allow employers and candidates find one another simply and with little effort.
As both technologies require very little real-time processing, we chose to perform a great deal of Scopings’ functionality using Service Broker’s queues. As a result, we were able to substantially lower the total cost of ownership on the platform by handling more users’ requests with less hardware and fewer software licenses.
The best examples for using technology are real-world use cases. Below are two examples of queued processing usage within Scopings.
- Matching – Match records against a secondary database
Upon publishing a new position, or updating candidates’ information, Scopings updates the relevant information in real time but does not attempt to match the position criteria with potential candidates, or candidates’ information with potential positions. Instead, Scopings uses a combination of replication and Service Broker messages to perform the actual matching operation on a predestinated database that may or may not exist on the same database server. This gives Scopings complete flexibility in the way databases are distributed across database servers. It also minimizes the load on each database that is designated to store users’ information, allowing it to support many more concurrent users.
- Workflow – Delay the evaluation of the data submitted
Scopings has a very sophisticated workflow mechanism. In essence, candidates need to successfully pass a certain phase in their job applications before they can move on to the next phase. Once candidates submit their information for any specific job application phase, Scopings logs the data in real time, but uses queues to process and evaluate the data that was submitted and determine whether or not they should indeed move on to the next phase.
Conclusion
Although we haven’t delved into the technical details (in this article anyway), hopefully this has provided some insight into how queued transactions can potentially improve your application’s performance- whether Service Broker might satisfy all of your future requirements or how you can buy some valuable time until you can fully scale your application.