web application receiving millions of requests and leads to generating millions of row inserts per 30 seconds in SQL Server 2008

Question

I am currently addressing a situation where our web application receives at least a Million requests per 30 seconds. So these requests will lead to generating 3-5 Million row inserts between 5 tables. This is pretty heavy load to handle. Currently we are using multi threading to handle this situation (which is a bit faster but unable to get a better CPU throughput). However the load will definitely increase in future and we will have to account for that too. After 6 months from now we are looking at double the load size we are currently receiving and I am currently looking at a possible new solution that is scalable and should be easy enough to accommodate any further increase to this load. Currently with multi threading we are making the whole debugging scenario quite complicated and sometimes we are having problem with tracing issues.

FYI we are already utilizing the SQL Builk Insert/Copy that is mentioned in this previous post

Sql server 2008 - performance tuning features for insert large amount of data

However I am looking for a more capable solution (which I think there should be one) that will address this situation.

Note: I am not looking for any code snippets or code examples. I am just looking for a big picture of a concept that I could possibly use and I am sure that I can take that further to an elegant solution :)

Also the solution should have a better utilization of the threads and processes. And I do not want my threads/processes to even wait to execute something because of some other resource.

Any suggestions will be deeply appreciated.

Update: Not every request will lead to an insert...however most of them will lead to some sql operation. The appliciation performs different types of transactions and these will lead to a lot of bulk sql operations. I am more concerned towards inserts and updates. and these operations need not be real time there can be a bit lag...however processing them real time will be much helpful.

Answer 1

I think your problem looks more towards getting a better CPU throughput which will lead to a better performance. So I would probably look at something like an Asynchronous Processing where in a thread will never sit idle and you will probably have to maintain a queue in the form of a linked list or any other data structure that will suit your programming model.

The way this would work is your threads will try to perform a given job immediately and if there is anything that would stop them from doing it then they will push that job into the queue and these pushed items will be processed based on how it stores the items in the container/queue.

In your case since you are already using bulk sql operations you should be good to go with this strategy.

lemme know if this helps you.

Answer 2

Since you do not need to have your inserts/updates real time you might consider having two databases; one for reads and one for writes. Similar to having a OLTP db and an OLAP db:

Read Database:

• Indexed as much as needed to maximize read performance.
• Possibly denormalized if performance requires it.
• Not always up to date.

Insert/Update database:

• No indexes at all. This will help maximize insert/update performance
• Try to normalize as much as possible.
• Always up to date.

You would basically direct all insert/update actions to the Insert/Update db. You would then create a publication process that would move data over to the read database at certain time intervals. When I have seen this in the past the data is usually moved over on a nightly bases when few people will be using the site. There are a number of options for moving the data over, but I would start by looking at SSIS.

This will depend on your ability to do a few things:

• have read data be up to one day out of date
• complete your nightly Read db update process in a reasonable amount of time.

Answer 3

Can you partition the database so that the inserts are spread around? How is this data used after insert? Is there a natural partion to the data by client or geography or some other factor?

Since you are using SQL server, I would suggest you get several of the books on high availability and high performance for SQL Server. The internals book muight help as well. Amazon has a bunch of these. This is a complex subject and requires too much depth for a simple answer on a bulletin board. But basically there are several keys to high performance design including hardware choices, partitioning, correct indexing, correct queries, etc. To do this effectively, you have to understand in depth what SQL Server does under the hood and how changes can make a big difference in performance.