Skip to content

Scaling SQL Server

Scalability is "a desirable property of a system, a network, or a process, which indicates its ability to either handle growing amounts of work in a graceful manner or to be readily enlarged." (A. Bondi, 'Characteristics of scalability and their impact on performance').  There are a number of different approaches that allow SQL Server to easily handle ever growing transaction volumes.

Performance Tuning

The simplest approach to increase the capacity of a database server is to review the query performance.  This often has a dramatic effect on performance.  We use ClearTrace to identify queries that use the highest aggregate resources.  In one engagement we eliminated the need for a new server by aggresively tuning key queries and adding needed indexes.

Vertical Scaling (Scale Up)

Adding additional hardware to an existing server can be a relatively painless way to improve performance.  This ranges from simple enhancements like adding memory to moving the database files to faster storage.  A more complex solution is to migrate a server to a new server that has improved performance.

Hybrid Scaling

The first step between vertical scaling and horizontal scaling is to migrate key activies off the server.  The most common example is moving reporting to another server where data is nearly real-time.  This can be accomplished through a data warehouse, SQL Server replication or a database snapshot on a mirrored database.  This can remove a significant load from the transaction server and improve throughput and reduce locking. 

Another approach is caching commonly run queries.  These may be simple lookup queries that are executed repeatedly.  Individually they are each fast but in aggregate they can consume significant resources.  This can be accomplished by caching on the client (or web server) using .NET cache objects or using a dedicated caching service such as memcached or Project Velocity.

A key characteristic of both hybrid and vertical scaling is that your main transaction database lives on a single server.

Horizontal Scaling (Scale Out)

Scaling out a database means that your main transaction database is spread across multiple servers.  This is commonly called "sharding" a database.  For example, your user account table might be split across multiple servers.  Each user account is placed into a bucket and these buckets are distributed across number database servers.  The key requirement is that the client application be able to determine which server to use without having to query each server.  This can involve significant design and coding challenges and is rarely used except in the largest web sites.

 

 

Latest News