Why would bulk Inserts cause an ASP.net application to become Unresponsive?

Question

Setup: ASP.net 3.5, Linq-to-Sql. Separate Web and DB servers (each 8-core, 8GB RAM). 4 databases. I am running an insert operation with a few million records into DB4 (using Linq-to-Sql for now, though I might switch to SqlBulkCopy). Logging shows that records are being put in consistently at a rate of 600-700 per second (I am running DataContext.SubmitChanges() every 1000 records to keep the transaction size down). The insert is run during one Http Request (timeout is set pretty high).

The problem is that while this insert operation is running, the web application becomes completely unresponsive (both within different browser windows on my machine, and on other browsers in remote locations).

This insert operation is touching one table in DB4. Most pages will only touch DB1 (so I don't think that it is a locking issue - I also checked in through Management Studio, and no objects are being locked unnecessarily). I have checked out performance stats on both the Web and DB servers, and while they may spike from time to time, throughout the inserts they stay well within the "green".

Any idea about what can be causing the app to become unresponsive or suggestions about things that I should do in order to narrow down the issue?

Responses to suggestions:

• Suggestion that inserts are using all DB connections: the inserts are being done off of a different connection string (and DB) than what other pages in the app use. Also, I checked in SSMS, and there is just one connection open for DB4, and one open for DB1 (so it doesn't look like it is running out of connections).
• Suggestion that inserts are maxing out CPU on web server: this is the only application on the server (and less than 5 users at any one time). Performance monitor shows CPU staying in between 12%-20%. Memory is hardly being touched.

Answer 1

My first guess would be that you are using up available connections to the database with the insert operations that you are doing and the web applications are waiting to get a connection to the database.

You have a few options.

1. Look in SSMS and see what you have for open and active connections under regular load and when doing the inserts see if that is a problem.
2. Use a profiling tool such as ANTS profiler to see what is going on with the web application at the time of the slow down, it might help pinpoint the issue.
3. You could also try manually executing the queries that the web application is using, on the SQL Server and see if you notice a similar behavior.

The other option, a bit less likely, but it could be that the web application doing the bulk insert, it taking all of the CPU time from the other web applications on the server, preventing use. If you haven't done so already, split out the application to its own pool so you can monitor its load.

Answer 2

I don't know about Linq-to-Sql, but NHibernate specifically states that using it for bulk inserts is a bad idea. I have found Array Binding in ADO.NET to be very fast, Here is an article explaining how to do it with Oracle, but it should work with other providers too.

Answer 3

Seems like it is bad idea to do long operations in web app (for example, your IIS server can restart your application for next to no reason). Split your long application into Web App and Service App. Do long operations in Service App. Communicate between them via WCF & pipes.

Answer 4

Eventual Solution: I changed the data insertions from LinqToSql to use SqlBulkCopy via DataTable. The first time I did this, I got an OutOfMemory exception when trying to build a DataTable with 2 million rows in memory. So I am adding 50,000 rows at a time, and loading them into the DB with SqlBulkCopy (Batch Rate: 10,000) and then clearing the DataTable Rows collection. I am now getting in 2.1 million rows in 108 seconds (About 20,000 per second; Rate rate last night was average of 200 per second with L2S). With the increased data insertion performance, the app-wide unresponsiveness has gone away.

Answer 5

It possible what you have a lock statement some where in you web application what blocking some important resurse during the whole time you loading you data into DB.