[Last Call] Learn how to a build a cloud-first strategyRegister Now


High volumne

Posted on 2014-01-14
Medium Priority
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 https://secure.logmein.com/welcome/visualization/fullscreen/ 
that's an example of a high volume application

Assisted Solution

Beartlaoi earned 400 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 800 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!
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments


Author Comment

ID: 39781130
Thanks for the great helpful comments.

How about high availability strategies?
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 400 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 800 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 http://www.microsoft.com/en-us/sqlserver/editions.aspx

Accepted Solution

Secondstreet earned 400 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

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!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

830 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