Can SQL server 2008 handle 300 transactions a second?

Question

In my current project, the DB is SQL 2005 and the load is around 35 transactions/second. The client is expecting more business and are planning for 300 transactions/second. Currently even with good infrastructure, DB is having performance issues. A typical transaction will have at least one update/insert and a couple of selects.

Have you guys worked on any systems that handled more than 300 txn/s running on SQL 2005 or 2008, if so what kind of infrastructure did you use how complex were the transactions? Please share your experience. Someone has already suggested using Teradata and I want to know if this is really needed or not. Not my job exactly, but curious about how much SQL can handle.

Answer 1

Its impossible to tell without performance testing - it depends too much on your environment (the data in your tables, your hardware, the queries being run).

Answer 2

I agree with @Kragen, however this may be of use - http://as400bks.rochester.ibm.com/tividd/td/ITMD/SC23-4852-00/en%5FUS/HTML/mssql511rg93.htm

Answer 3

According to tcp.org its possible for SQL Server 2005 to get 1,379 transactions per second. Here is a link to a system that's done it. (There are SQL Server based systems on that site that have far more transactions... the one I linked was just the first I one I looked at).

Of course, as Kragen mentioned, whether you can achieve these results is impossible for anyone here to say.

Answer 4

Infrastructure needs for high performance SQL Servers may be very differnt than your current structure.

But if you are currently having issues, it is very possible the main part of your problem is in bad database design and bad query design. There are many way to write poorly performing queries. In a high transaction system, you can't afford any of them. No select *, no cursors, no correlated subqueries, no badly performing functions, no where clauses that aren't sargeable and on and on.

The very first thing I'd suggest is to get yourself several books on SQl Server peroformance tuning and read them. Then you will know where your system problems are likely to be and how to actually determine that.

An interesting article: http://sqlblog.com/blogs/paul%5Fnielsen/archive/2007/12/12/10-lessons-from-35k-tps.aspx