I've got a service that should support real-time interaction between many users (thousands real-time). I use .NET 4 with MSSQL 2008 on IIS 7.5.
The DB consists of only a few tables without high usage or data - but one table is growing REALLY fast (each use updates it each second with a new record).
On my server load testing it grew to above 400,000 records in an hour or so - my performance declined from 200ms RTT to 800ms RTT in about 2 hours.
How should I build my DB to support this? Mayvbe the problem lies in another place?
For now it works with all the tables in one DB, but I thought maybe I should move the "heavy" table to a new DB to increase performance. Should it work? is it a smart move?
I've though about moving that table to NoSQL - will it help?
Any ideas are welcome!