Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Throttling database processes? Expand / Collapse
Author
Message
Posted Thursday, June 26, 2008 8:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 15, 2014 9:33 AM
Points: 33, Visits: 255
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
Posted Thursday, June 26, 2008 8:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:04 AM
Points: 1,030, Visits: 2,795
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
Posted Thursday, June 26, 2008 8:43 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:42 AM
Points: 646, Visits: 732
Create a Database SNAPSHOT.That should let the other users Query the Database for SELECTS only.

Maninder
www.dbanation.com
Post #524254
Posted Thursday, June 26, 2008 11:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 8, 2013 11:39 AM
Points: 16, Visits: 325
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
Posted Thursday, June 26, 2008 8:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 36,941, Visits: 31,443
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #524701
Posted Wednesday, February 24, 2010 2:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:11 AM
Points: 171, Visits: 444
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 »

Add to briefcase

Permissions Expand / Collapse