Can bulk inserts on one DB affect the performance of the whole server?

Question

I have a process that uses BULK INSERT to push two CSV files into SQL tables, so I can join their data with another table I have. The data coming from the CSV files generates tables about 100,000 rows each. When the process is done these tables are truncated. This whole process is running on a DB that was created just for that (no other activity).

After running this for a while in one minute cycles, the whole server becomes VERY slow. It looks like this activity is taking some resource and not releasing it. Even though the bulk insert and the join together take just about 5 seconds each time.

Answer 1

Every action you perform on the instance will consume resources and affect performance to some extent. Some things are more noticeable than others.

What recovery model is your database in? Is the table you're adding the data to in another database?

It's hard to say what the underlying problem could be with just the information you've provided. You'll have to do some monitoring on your server to see exactly what the cause could be. Your server could be queueing up IO requests, you might be growing your trans log file and running into waits there, you might be pushing memory constraints, indexes might be rebuilding, all kinds of things.

Answer 2

using BULK INSERT by itself doesn't mean you will get minimal logging. You should be careful and read up on the pre-requisites for getting minimal logging.

Ref: http://msdn.microsoft.com/en-us/library/ms190422.aspx

Ref: http://sankarreddy.com/2011/03/interrogating-prerequisites-for-minimal-logging-in-bulk-import-part-1/

Also, check the memory settings on the box and make sure you have max memory is set appropriately. Have you looked at the page file usage also? If there is too much page file usage then you may also want to reduce the max memory setting. What else is running on the box?

The right way to handle this is to use WAIT STATISTICS information [http://technet.microsoft.com/en-us/library/ms179984.aspx] and also trace some info when the server is running slow. When server is running slow, you need to check the cpu usage, IO usage etc... and @SQLSoldier has a cool script to identify the root cause of high transient cpu spikes.

Ref: http://www.sqlsoldier.com/wp/sqlserver/catchingtransientcpuspikesusingsqltrace

This is a starting point and there are other things you could do but need more info.

Answer 3

It depends a lot on the server's hardware and configuration, but yes, heavy activity on one single database can create performance problems on the whole server. The most plausible scenario is that the heavy activity consumes almost all of the server's memory and pushes everything else out of the system's (or SQL Server's) cache; so, even after it's done, everything has to be re-read from disk again.