Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


High Traffic Website Design


High Traffic Website Design

Author
Message
suri.yalamanchili
suri.yalamanchili
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 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?

Thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55199 Visits: 44696
Don't change anything unless you know you have a problem.

http://sqlinthewild.co.za/index.php/2010/03/11/the-root-of-all-evil/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-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


johnitech.itech
johnitech.itech
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 97
Hehe
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===>
http://www.sql-server-performance.com/2007/oltp-performance/
Chrissy321
Chrissy321
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

Group: General Forum Members
Points: 800 Visits: 4610
This doc is old but should give you some background.

http://www.dell.com/downloads/global/power/4q03-mis.pdf
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7002 Visits: 8390
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.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search