# Most suitable DB Architecture for real-time high-volume processing API

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!

• Can you give us some more information on your existing architecture? Table definition of 'heavy' table, with indexes. Definition of database? (if you defaulted your db to grow by 1MB, it could be just the file usage on the disk which is causing your degradation...) – Andrew Bickerton Apr 5 '11 at 13:04

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.

• This was very very helpful. Thank you - I will investigate your ideas. – roman Apr 6 '11 at 14:52

If you are not automatically updating the stats, run sp_updatestats within your DB context. If you are having a 'heavy' table, as you mentioned, try moving that table to a different filegroup. The filegroup should have its data files on a different physical disks than those of your other tables.

SQL Server can handle very high volumes of data. Based on what you have shared so far, it seems the data in the table grew and your performance went down and that seems to be a classic case of not having the right indexes in place. If you can share the table schema + indexes + sql query, some of us can advice you on the best course of action here. GL.

Also, it helps to know the SELECT @@VERSION information.

EDIT:

The following will also help to know.

    WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold


And the amount of memory available to SQL Server.