Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers 3

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.