August 10, 2006 at 5:43 pm
Hi,
We have SQL server Standard edn SP3 dual processor (soon to become SQL server Enterprise edn and sp4, Quad processor), and have only one huge database on it.
The problem is that one job (a reconcilliation job) takes up most of the server resources, and anyone trying to read the server gets a very slow response. I want to limit the amount of CPU (memory/ Processor/Disk I/O etc) used by this job to a certain amount so that the other jobs/processes can still run
Thanks heaps in advance,
August 14, 2006 at 8:00 am
This was removed by the editor as SPAM
August 14, 2006 at 10:35 am
You can do this with DTS tasks. Certain DTS functions can be dialed back to smaller batch sizes, and the whole DTS package can be set to a limited amount of memory. If you need to do this to limit a stored proc, just wrap it in a DTS task, and you should be able to get the same result. The only other way to do it is to limit the batch sizes in code, and use the wait command to force a wait between batches.
Good luck!
Jasmine
August 15, 2006 at 10:40 am
you need to establish the root cause of the slow performance and resolve that. I suspect you're probably i/o bound. I suggest you analyse the query plan for your reconcilliation and look to optimise it - and as suggested batching does often work much better.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 15, 2006 at 12:51 pm
It's not slow performance that causes this problem. When I've seen this before, it's been caused by a process with extremely good performance, but with a lot of work to do. That process has the ability, because it is running so fast, to take over the server completely, and it won't allow other processes to get any CPU time. The only way to fix it is to deliberately degrade the performance of the offending process, which will allow other processes to get some CPU time.
August 15, 2006 at 1:57 pm
fine if that's the case then you have a perfect process! My experience tells me this isn't the case and you can modify it to work otherwise. Obviously upgrading to more procs and to enterprise should give a vast performance boost. You might want to consider adding extra memory ( with enterprise ) , I've dropped cpu by 75% by adding memory.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 17, 2006 at 8:28 am
I'll agree with Colin, I've never heard/seen a perfect sql statement taking over the processor on a machine. If it's that perfect it wouldn't be a problem in the first place. Many times in my experience, it has been a lack of proper indexes that has degraded system performance. We don't know table sizes, nor processes involved in the reconciliation process. Are you using cursor's to do your processing? Too many unknowns to try to guess at a problem. Is there a blocking problem with the query.
just my 2 pennies.
Tom
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply