title: Manage big data in sql server
body: <p>Our team is facing a problem of querying a large amount of data on sql server blocks inserting into the table. </p>
  
  <p>We are working on a .NET project which involves saving and querying a large amount of data. The project contains two databases, one is a Realtime database and the other one is a Historical database, both are SQL Server databases, and they live on different machine. The two databases have the exactly same table structure, and there are only four tables.</p>
  
  <p>The Realtime DB contains a small amount of real-time data, and the data inside is constantly being updated. The historical DB contains all the updates history. When the project runs, it will shoot update queries to the Realtime DB, and insert queries into the Historical DB. Each day about 2 million inserts will be executed on the Historical DB. The queries are executed Asynchronously.</p>
  
  <p>The Historical DB will also be used for data retrieval and reporting purposes. People will run queries on the server, what queries they will run are out of our control. The problem we are facing right now is that when a query that returns a lot of rows is running, the connection pool will be full within a short time, as a result connection timeout would occur and data will be lost. </p>
  
  <p>We have tried tweaking indexes on the table, increasing the max size of connection pool and increasing the timeout time, but none of them will solve the problem primarily. In production the project will run for 5 years, at that time there will be 4 billion rows in the database.</p>
  
  <p>I guess my real question is how people usually deal with big data in SQL server, how to manage insert and select at the same time on a table that has millions or billions of rows.</p>