Planning for Peak Usage

People often ask questions such as, "Can SQL Server handle our system? We do a million transactions a day." To answer this question, you have to know exactly what transactions they have in mind. You also need to know the system's peak usage. If a million transactions a day are nicely spread out over 24 hours, that's less than 12 transactions per second. In general, a 12-TPS (transactions-per-second) system would be adequate. But if 90 percent of the million transactions come between 2 p.m. and 3 p.m., it's a very different situation. You'll have rates of about 275 TPS during that hour and probably peaks of more than 350 TPS. You can use benchmarks, such as Debit-Credit, in which SQL Server performs over 1500 TPS, but all transactions are different. It is meaningless to refer to transactions per second in SQL Server or any other system without also talking about the types of transactions. This is precisely the reason that standardized tests are available from the Transaction Processing Council for comparing database performance.

Regardless of the specific transactions, though, you must design and build your system to handle peak usage. In the case above, the important consideration is peak usage, which determines whether you should target your system to a volume of 350 TPS for unevenly distributed usage or to only 12 TPS with usage spread out evenly. Most systems experience peaks, and daily usage is not so nicely spread out.