Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

High Traffic Website Design Expand / Collapse
Posted Monday, January 24, 2011 5:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 31, 2013 4:02 PM
Points: 69, Visits: 539
Hi All,

I have a website which has db in the back end. The db currently is only on one instance. I am expecting high traffic in near future, so what steps should I take to support this.

The users on the website insert records into tables , which have identity column as primary key.

What should I change? Will clustering help? How do I handle identity columns?

Post #1052791
Posted Monday, January 24, 2011 10:19 PM



Group: General Forum Members
Last Login: Saturday, December 3, 2016 5:18 AM
Points: 45,619, Visits: 44,147
Don't change anything unless you know you have a problem.,-part-1/,-part-2/

First thing you need to do is test the DB and the website under the expected load and see how it behaves. You can but load-testing tools for websites, I don't know names offhand.

Clustering is not for performance. Clustering is a High Availability technique (and is reasonably expensive)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1052856
Posted Thursday, January 26, 2012 7:28 AM


Group: General Forum Members
Last Login: Tuesday, February 7, 2012 1:19 AM
Points: 100, Visits: 97

Keep transactions as short as possible. The shorter transactions are, the shorter that locks have to be held, which results in both greater performance and scalability. Here are some ways to help keep transactions as short as possible:

It is especially important that transactions aren’t kept open while waiting for user input. User input should be collected as a whole, and then submitted at one time. In other words, don’t collect user input in the middle of a transaction.
Use stored procedures for your code.
Only return the exact data needed, no extra columns or rows should be returned.
Try to reduce the number of round trips between the client and SQL Server.
In large units of work, consider breaking it down into smaller units, so that there are many, very quick transactions rather than a single, large transaction. A large transaction hold locks for longer periods, contributing to performance problems.
From another perspective, if there are many, many transactions going on, contributing to excess round trips and network traffic, consider batching them together so that there are fewer round trips between the client and SQL Server.
Do not use a transaction to view and browse data.
Consider using a lower transaction isolation level, if appropriate.
Always manage your transaction by beginning and ending your transaction explicitly. Do not use implicit transactions.

org link===>
Post #1242166
Posted Thursday, January 26, 2012 8:41 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:17 PM
Points: 660, Visits: 4,533
This doc is old but should give you some background.
Post #1242250
Posted Friday, January 27, 2012 8:09 AM



Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 5,659, Visits: 8,180
If you truly expect high traffic volumes then you had best test for that BEFORE you go live!!! I can't tell you the number of times I have seen clients try to scale up with a crappy database application and have it fail spectacularly. Two of those past clients of mine actually went out of business because they didn't get me on board to help before it was too late.

Speaking of which, asking the question like you did I can tell you with almost absolute certainty that you have ZERO chance of success here unless you get good professional help to review your application and assist with testing and improvements. SQL Server is AMAZINGLY GOOD at working well with bad design, bad schemas, bad maintenance, bad applications, poor hardware, etc. But high volume activity and concurrency requires that you do a LOT of things right. The good news is that (in my 15 years of consulting experience anyway) there are LOTS of things that are done wrong by companies that can provide incredible performance gains when fixed.


Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1242892
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse