Throttling database processes?

Question

Post reply

Throttling database processes?

mxhxr

Mr or Mrs. 500

Points: 551
More actions
June 26, 2008 at 8:20 am

#124475

We have a transactional database that encounters data loads almost daily. These data loads are initiated by end users, and cannot really be scheduled for off-hours.
The problem is, the data load monopolizes server resources, and other "regular" users of the OLTP system experience timeouts and very slow performance at best.
Is there any way to throttle back (in SQL 2005) the resources utilized by a single process or user?
thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 1

There's a resource govenor new in SQL Server 2008. Don't think there is anything avaialble in 2005

Gethyn Elliswww.gethynellis.com

MannySingh SSChampion Points: 12465 More actions · Answer 2

Create a Database SNAPSHOT.That should let the other users Query the Database for SELECTS only.

Maninder
www.dbanation.com

Matt Cunningham Old Hand Points: 378 More actions · Answer 3

How are the data loads being done? Java front end app with a JDBC connector?

We had issues with a datastore that is also used for reporting that had some horrible code used to do inserts ( looping through millions of records to find duplicates for each insert ) that was taking huge amounts of time to process and the reporting was, as you say, experiencing timeouts and connectivity problems.

Find out what is beating up your server, CPU or I/O.

If it's cpu chances are that there is some code that needs to be tweeked or some indexes to be added.

If it's I/O...might want to think about adding a staging table on a separate set of disks and inserting the data into the real tables off hours.

A combination of code rewrites and additional indexes brought the load times from 24+ hrs to 4hrs. Be warry that more indexes can slow down updates..but this should get you on the right track

_______________________________________________________________________
Work smarter not harder.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

The real problem is that you're using the same table for OLTP and Batch processing. Load the data into a separate table (staging table), process it, the transfer the final results to your OLTP table.

We had the same problem where I work... code ran for 30 minutes, 4 times a day, and cause 10 minute long server wide "blackouts" each time. Using the method I've identified above, the code ran in 3.91 seconds...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ben Moorhouse SSCrazy Points: 2385 More actions · Answer 5

It's also worth making sure that you're using transactions properly when loading the data - we have to deal with data loads every half hour, so need to keep physical table writes (and therefore table locks) to a minimum.