Control resource usage 1

[matthieu99]

Hi,

When running jobs (large updates,inserts,etc...) on our SQL Server all our applications are affected because these jobs take a lot of resources. Is there a way to flag a query or DTS so that it takes less resources?

Thanks.

 

[joebickley]

Dont think u can speciafy a particular job to run in a lower prioity but in a proc you could put " WAITFOR DELAY '00:00:02' " To occur everynow and then, say every 100 inserts, just to Cut the process into bursts on activity rather than a total balck out?

By no means a perfect answer but it might help?

 

[tlbroadbent]

Which resources are most affected by the processs? Are tables being locked? Is Processor time used by the jobs excessive? There could be any number of possible problems and a number of ways to correct the problems.

A common problem when performing mass updates is trying to Insert, Update or Delete hundreds of thousands or millions of rows in one batch. This causes excessive transaction log growth. Which can slow all processes using a particular database. faq183-3141 explains "How to Use Batch Size to Speed Mass Updates, Inserts and Deletes."

DTS bulk insert tasks can also be setup to commit smaller batches of inserts thus limiting log growth. See the following topic in SQL BOL.

How to configure the fast load batch options

http://msdn.microsoft.com/library/en-us/howtosql/ht_dts_task_9p4j.asp

Please let us know if there are other issues to consider.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[matthieu99]

Thanks Terry!

The response time of our web applications gets affected. The query on those pages are nothing fancy, just SELECTs. The response time can sometimes go from fractions of a second to 10 seconds.

The mass updates comes from DTSs that retrieve data from text files and then consolidates it in new tables and indexes the tables. The amount of records can go up to about 300,000.

Matthieu