Limit CPU at SqlBulkCopy

Question

Is it possible to limit CPU usage at SqlBulkCopy?

When I execute my code (see below), CPU usage on SQL server jumps to 100%. I'd like to limit this to, let's say, 50%. Is this possible?

Code:

string connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ToString();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
    bulkCopy.DestinationTableName = "dbo.CSVFile";
    bulkCopy.BulkCopyTimeout = 600;
    bulkCopy.BatchSize = 1000;
    bulkCopy.WriteToServer(tableCSV);
}

EDIT: This code is running on seperate machine from the db server.

Why I need to limit CPU usage: this SQL server has multiple databases, one of them is acceseed constantly from two computers, each of them does a couple of queries per second. This two computers operate two big machines, so every time one of this computer waits for his queries to execute, he stops his machine, waits for queries to execute and then runs his machine again. So every time I run something on SQL server that consumes a lot of CPU, that stops some machines in production.

Answer 1

Why? You are asking the server to do work, it's doing the work. Making it twice as slow doesn't make sense. Leave it up to SQL Server to schedule its jobs, you can't do it yourself since you don't know what kind of jobs are active.

Answer 2

You can use MAXDOP to temporarily use only one processor : http://msdn.microsoft.com/en-us/library/ms181007.aspx

If SQL CPU usage is a problem, you may move SqlServer to a dedicated server.

Answer 3

Is the data you're updating affecting the results of the other machine's queries? If it is then it should block them, nothing to do with the CPU but because the records are locked for update. You want the operation to run as quickly as possible so max CPU is good.

If the two datasets are different then there is no reason why a well configured SQL server couldn't serve both, look into tuning options, how much memory is available etc

Answer 4

You said :

They are completely seperated databases.

You can install a second instance of sql server with different CPU affinity/priority. You can also buy a second server.