Throttling database processes?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Throttling database processes?

Throttling database processes?

Rate Topic

Display Mode

Topic Options

Author

Message

mxhxr

Posted Thursday, June 26, 2008 8:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 10, 2013 10:19 AM
Points: 31, Visits: 189

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!

Post #524216

GRE (Gethyn Ellis)

Posted Thursday, June 26, 2008 8:36 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 9:55 AM
Points: 1,024, Visits: 2,768

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

Gethyn Ellis

gethynellis.com

Post #524239

MannySingh

Posted Thursday, June 26, 2008 8:43 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:10 PM
Points: 646, Visits: 729

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

Maninder
www.dbanation.com

Post #524254

Matt Cunningham

Posted Thursday, June 26, 2008 11:17 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 11:49 AM
Points: 16, Visits: 311

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.

Post #524422

Jeff Moden

Posted Thursday, June 26, 2008 8:17 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:36 AM
Points: 32,890, Visits: 26,759

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #524701

Ben Moorhouse

Posted Wednesday, February 24, 2010 2:16 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 09, 2012 2:36 AM
Points: 171, Visits: 442

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.

Post #871782

« Prev Topic | Next Topic »

Permissions