High volumne

Posted on 2014-01-14
Last Modified: 2014-01-18
Can anyone provide me some concerns/suggestions/tipical issues/related tips ... on High Volume Transaction ? I have basic knowledge but have not ever exposured to a high volume transaction system yet. Can anyone give me a good leading so that it would helpful for me to develop solutions for high volume transaction enterprise class systems in commercial settings.
Thanks a lot.
Question by:minglelinch
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39780588
Check this 
that's an example of a high volume application

Assisted Solution

Beartlaoi earned 100 total points
ID: 39780633
There are multiple variables to consider.
First though the main performance bottleneck in a database is the storage.  Disks can only seek so fast.  To deal with this you should have many disks and partition schemes to balance the daily load to all the disks.
Also, do not have SQL do things that the app/middle tier can do like sorting and some joining like translating IDs to names of non-changing data.  Also avoid using SQL to generate Identity values, with proper data design the IDs can be generated by the client and the DB only has to store it.
You can partition your data between servers like last names between A-M on one server and the others on the other, use as many servers as you like.  But programming for this can be tricky and may not be agile enough to change where the boundaries are to keep things properly load balanced.
If your budget is really big, you can look at MS Data Warehouse where they mainly throw hardware at it and give you an effectively load-balanced SQL/Storage solution.

Assisted Solution

smilieface earned 200 total points
ID: 39780940
For high volume you're going to need a few things. This is my personal order of priority.

1. Memory. Lots of memory dedicated to Buffer Space. Without any real metrics I would suggest at least 25% of the database size available as Buffer. Note that this must be backed by real RAM. Paging is your enemy on a database server.
2. Dedicated servers. Nothing worse than wanting the CPU and something else has it, or waiting for someone else's write to complete.
3. Storage. Faster the better, and the good news is that there is some staggeringly fast storage available through SSD now. Your targets for SSD disk are TempDB and the .LDF files. Don't waste SSD storage on the .MDF files, as they'll be fine on slower storage.
4. CPU. Enough that you are consistently below 60% busy.

There are lots of forums out there that will help (including this one), but to get a more specific answer, you'll need to ask a more specific question!
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


Author Comment

ID: 39781130
Thanks for the great helpful comments.

How about high availability strategies?
LVL 42

Assisted Solution

EugeneZ earned 100 total points
ID: 39781214
the answer is "it depends":
on your budget
technology, software
and what do you plan to use

select what one you plan (e.g. real time )

and check

Transaction processing system

Assisted Solution

smilieface earned 200 total points
ID: 39781236
And on your software edition. As this is in a SQL forum, I will make the recommendation to check the features of the various of SQL server editions

Accepted Solution

Secondstreet earned 100 total points
ID: 39790159
Certainly there are a lot of options for you and as others have noted it "depends" on a lot of things... especially the budget.


1) Is your goal a high transaction volume (writes - inserts/updates/deletes) or high capacity working with lots of data collected? If you're into transaction rate that IO latency/IOPS are going to be your bottlenecks... If high capacity processing then you'll likely need RAM and CPU power.

2) What is your ballpark budget for this?


1) Since this is posted in MS SQL I'll assume the solution you want is Microsoft SQL Server based.

2) If high transaction volume is required you'll need storage with low-latency to achieve the highest IOPS. As mentioned in other comments the FusionIO IODrive 2 is pretty wicked fast in terms of IO latency and IOPS... those are your tickets to high transaction throughput.

3) The RAID controller in your SQL Server(s) may have a battery-backed RAM cache that allows you to do write caching. This can greatly increase your ability to handle bursts of transactions... although doesn't help much in terms of sustained high transaction rates.

4) SQL 2014, soon to be released, will be interesting for transaction volume. If you can tolerate some potential transaction data loss (last few seconds) in case of a catastrophic server failure... and that's a BIG IF... then SQL 2014 has a new feature called "delayed durability" which can greatly increase transaction throughput. I'm getting much higher rates in my tests.

5) Test... test... test. If you can try different hardware and properly configure and test it.

6) If you need High Availability and extreme Transaction Rates you may want to use Mirroring instead of clustering. I use clustering in many of my installations but transaction volume is often limited by IO latency for those who need extreme transaction rates.

I'm currently on a project that requires that we attain extreme transaction/sec rates and hardware wise am spending $110K for a pair (for High Availability) of servers and lots of PCIe based storage.

Author Closing Comment

ID: 39791452
Thanks for all helpful comments.

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
First Max value 3 28
changing page verifacation 1 27
HTML <font style="color:red"> 9 31
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question