Throttling SQL Server

[RFEULINGatRITE]

Throttling SQL Server

Hello,

We created some triggers that occur during updates. Problem is that when some larger updates occur the trigger consumes the entire hard drive throughput and freezes everything else that’s using the database.

Is there anyway that I can slow the trigger down so that other programs can continue to use the database? Is there a way to tell SQL Server to throttle the operations?

Thanks!

-Rick

 

[dbomrrsm]

how do you mean slow it down ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[mrdenny]

The only way you can throttle down the SQL Server would be to change the parallisism option for either the query or for the database server over all.

Why do you have a trigger doing so much work. Perhaps there is a better way to approch the problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[RFEULINGatRITE]

Thanks for your responses.

The trigger is doing so much work because its part of an integration we done with another commercial product so we do not have access to source code or you'd be right, there would be a better way to do it.

What I mean when I say slow it down is - I want to make it so its not pushing the servers hard disk I/O so much because right now, when someone updates a lot of rows (1000+) it has to write a lot of data to the drive making it so other programs cannot access data without waiting for it to complete.

I would like it to slow down because the user who’s doing a mass update is expecting to wait whereas other people might be doing some other smaller jobs where instant retrieval of small amounts of data is critical.

An easy way to say it is, I need to prioritize jobs.

Thanks again for the help so far!

 

[mrdenny]

There isn't much you are going to be able to do to do that.

The best I can sugest is move the object that the trigger is using to another drive by moving it to another filegroup.

With the object on another drive, the object users won't have to wait for the drives to finish the big command, as that command will be running against another drive.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)