January 24, 2011 at 5:40 pm
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
January 24, 2011 at 10:19 pm
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/
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
January 26, 2012 at 7:28 am
: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/[/url]
January 26, 2012 at 8:41 am
This doc is old but should give you some background.
January 27, 2012 at 8:09 am
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 on googles mail service
May 10, 2017 at 11:25 am
This was removed by the editor as SPAM
May 10, 2017 at 11:26 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply