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!
Best Answer
Are you doing a round-trip to the DB for every request? If so, the classic cause of performance deteriorating as a table grows is stale statistics, as in, a query plan that made sense when a table was small is suboptimal once it grows. Check the query plans. Also check that you have auto stats creation, update, and async update enabled. I am assuming that you are already using a clustered index.
There is no point moving your hot table to a different database unless you a) understand what it is that you are waiting on and b) the different database relieves that. E.g. if you are waiting on I/O perhaps moving to a different database stored on different disks might help - but if its running through the same storage controller, it might not. If you are waiting on CPU, then this will not help, you would need to add CPUs or move it to another host. If you are waiting on lock contention, then this will not help, you will need to use a more suitable isolation level. So first, perform the necessary analysis to see where your SQL Server is spending its time.
But also consider if a round-trip to the DB is necessary for every query, in C# for example there are data structures offering O(1) access. This may be a better approach.