Problem solve Get help with specific problems with your technologies, process and projects.

Helping customers with peak load problems

Providing a database that works well under peak load pressure can go a long way to making VARs valuable to their customers. Expert Hilary Cotter offers advice for building and supporting databases that can stand up to the pressure.

What type of support should I build into my client's database to deal with peak work load issues?
Contention for system resources is always a problem on regional database management system (RBDMs) under high load. SQL Server 2005 has some interesting features designed to reduce contention and improve aggregate read and write performance.

Service Broker in SQL 2005 can be used to create queues for asynchronous processing. During peak load an order entry application can write to a queue which will be processed after the peak load passes. A shipping application could process the queue at a quiet time or even during the peak load with minimal impact on the throughput of the order entry application. Applications architected with service broker in mind can handle cyclical loads in a more scalable manner than if they were done synchronously.

Peer to peer replication can be used to implement a scale out topology where a group of SQL Servers will present identical data sets to the client. Instead of 1000 users connecting to a single SQL Server, they could connect to one of 10 SQL Servers in the peer-to-peer topology – each with an identical up to date data set. This scale out topology reduces contention and improves performance.

Table Partitioning can in some cases improve performance by distributing a large monolithic table into different file groups. The different file groups will reduce disk contention, and should the table partition be built around the queries offer much better performance.

Snapshot Isolation Level will present to each client a read only snapshot of the data at the time the transaction starts. This Isolation level allows greater concurrency.

Dig Deeper on Database software management

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.