Web应用程序接收数百万个请求，并导致在SQL Server 2008中每30秒生成数百万行插入(web application receiving millions of requests and leads to generating millions of row inserts per 30 seconds in SQL Server 2008)

 我目前正在解决我们的Web应用程序每30秒至少收到一百万个请求的情况。 因此，这些请求将导致在5个表之间生成3-5百万行插入。 这是非常重要的负担。 目前我们正在使用多线程来处理这种情况（速度稍快但无法获得更好的CPU吞吐量）。 然而，未来负荷肯定会增加，我们也必须考虑到这一点。 从现在起6个月后，我们正在考虑我们目前正在接收的负载大小的两倍，我目前正在寻找一种可扩展的可能新解决方案，并且应该足够容易以适应此负载的任何进一步增加。 目前使用多线程我们正在使整个调试场景变得非常复杂，有时我们遇到跟踪问题的问题。 
 
 仅供参考我们已经在使用上一篇文章中提到的SQL Builk插入/复制 
 
 Sql server 2008 - 用于插入大量数据的性能调整功能 
 
 但是我正在寻找能够解决这种情况的更有能力的解决方案（我认为应该有一个）。 
 
 注意：我不是在寻找任何代码片段或代码示例。 我只是在寻找一个我可以使用的概念的大图，我相信我可以进一步采用优雅的解决方案:) 
 
 此外，解决方案应该更好地利用线程和进程。 而且我不希望我的线程/进程因为其他资源而等待执行某些事情。 
 
 任何建议都将深表感谢。 
 
 更新 ：并非每个请求都会导致插入...但是大多数请求都会导致一些SQL操作。 应用程序执行不同类型的事务，这将导致大量的批量SQL操作。 我更关注插入和更新。 并且这些操作不需要是实时的，可能会有一点滞后......但是实时处理它们会非常有用。 

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.

原文：https://stackoverflow.com/questions/5584671