January 7, 2010 at 10:30 am
Hi Everyone,
I have a complicated sql 2000 for data transformation and loading.
The problem is everytime the job runs it consumes a lot of system resources, the cpu usage peaks to 95-100 percent when the job is running and the job runs for about an hour.
So whenever the job runs the whole server slows down and we cant use it for anything else.
My question is can we manually allocate the amount of system resources that a job can use?
It doesnt matter if the job takes longer to run, it just should use the minimal system resources.
Can it be done?
January 7, 2010 at 10:48 am
In short, it can't be done in systems prior to SQL 2008. SQL 2008 has resource governor that enable the Admin to limit system resources to groups.
My suggestion to your case is, try to break the DTS Package into multiple one based on whatever mechanism you can come up with.
Run the package at times when there is no / least system usage.
Not much can be suggested since nothing about the DTS package is known.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
January 7, 2010 at 4:59 pm
I agree with running the DTS job during off-peak hours so that user impact is at its least. Also, there is the added benefit that there are not as many processes fighting for the same resources, so the DTS package in itself will probably run faster.
Does the entire DTS package have to run on that server? It is possible to have parts of the job that are the most resource intensive run on a different server depending on what you are trying to do. Can you explain what the DTS package is doing? Do you know what step/steps in the package are using up all the system resources?
Joie Andrew
"Since 1982"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply