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 12»»

Parallel Processing Using Jobs Expand / Collapse
Author
Message
Posted Sunday, April 28, 2013 9:24 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, November 17, 2014 2:50 PM
Points: 100, Visits: 576
Comments posted to this topic are about the item Parallel Processing Using Jobs
Post #1447357
Posted Monday, April 29, 2013 2:01 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 769, Visits: 855
Hey all,

Great idea - and perfect timing for me.

I am currently looking to rewrite some of our TL (E is done prior) to a better system then it currently runs on.

o Can be used for different systems (we have multiple systems that will use it)
o Has precedence, but can fail elements and continue others.
o Has parallelism where possible.
o Is easy to maintain
o With detailed error logging, and simple monitoring

Currently i have considered SSIS (although not easy to maintain in my view) and using service broker to queue (a little complex).

What do people think of this article as an idea? So effectively i could have a set of tables to run this with - and procs to do the TL.

Thoughts?

Dan
Post #1447395
Posted Monday, April 29, 2013 3:42 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, July 4, 2014 1:25 PM
Points: 165, Visits: 292
I'm already doing something very similar on one of our systems. The process requires me to load data from a standby database into one which the users can access. The load is a complete refresh due to the nature of the system.

As part of this load, I have 4 threads of data copy running. These are actually managed from metadata in a table, but in an SSIS package which loops through four dynamically generated queues. This could be converted very easily to the T-SQL create / execute / delete job approach.

I then use the create / execute / delete job approach for building the indexes on these tables. The idea is that as soon as the procedure has completed that copies the data across for a table, it spawns a job to rebuild all of the indexes for that table. This means that the data copy over gets done quickly, and the indexes follow, off the critical path.
I was worried about the overhead on the server for doing this, but the copy over doesn't appear to be badly affected by the index rebuild.

Cheers

Ben



Post #1447423
Posted Monday, April 29, 2013 4:18 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:40 AM
Points: 652, Visits: 1,894
Interesting idea...but the article has a somewhat incomplete feel to it.

I notice that you never get beyond the 'Introduction' heading.

A little more explanation - perhaps a working example...and more importantly a conclusion (does it work? is it a good idea? is there a risk?) would have been welcome.

Perhaps Part II is on the way? If so, I take it all back!

Thanks.
Post #1447438
Posted Monday, April 29, 2013 8:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:27 PM
Points: 3, Visits: 12
This method does have limited usefulness, but should be viewed as a last resort after making every attempt to streamline and simplify processes. It can easily be over-used (and abused) as an easy cure-all for long running processes that need to be fixed.

I am not just speaking hypothetically. We added an almost identical process to our system years ago and we are now spending a lot of time getting rid of it because it is a constant headache. The job queue table backs up regularly on a busy system, and even when it is not backed up it introduces load that competes with user interactive processes. The idea of "do it later" doesn't work when the system is so busy processing interactive requests from users that there is no "later" during working hours.


Post #1447547
Posted Monday, April 29, 2013 8:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 10, 2014 6:19 AM
Points: 262, Visits: 919
David McKinney (4/29/2013)
Interesting idea...but the article has a somewhat incomplete feel to it.

I notice that you never get beyond the 'Introduction' heading.

A little more explanation - perhaps a working example...and more importantly a conclusion (does it work? is it a good idea? is there a risk?) would have been welcome.


I was wondering how you make it known to others how these jobs are getting into the job list. If everyone "just knows" about the metadata table that creates jobs, then perhaps I'm being pedantic. However, if/when a new DBA sees self-deleting jobs appearing and disappearing (before doing the exhaustive task of identifying the metadata table and the SP that consumes it) there may be some confusion. Also, is this cause for a security concern? Can a jr.dev add schedule entries to the metadata table via an insert right and bypass any security on scheduled job creation? Whose credentials are these jobs running under?

fwiw - i was expecting to read an article about problems/solutions involving parallel execution and scheduled jobs. Something along the line of "T-SQL with a singleton usage expectation got scheduled twice... it took {too long} to find/fix the deadlocks that were killing performance."
Post #1447551
Posted Monday, April 29, 2013 8:41 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 769, Visits: 855
gfish (4/29/2013)

I am not just speaking hypothetically. We added an almost identical process to our system years ago and we are now spending a lot of time getting rid of it because it is a constant headache. The job queue table backs up regularly on a busy system, and even when it is not backed up it introduces load that competes with user interactive processes. The idea of "do it later" doesn't work when the system is so busy processing interactive requests from users that there is no "later" during working hours.


What have you replaced it with?

I am looking to use it to run a load of stored procs first thing in the morning running parallel (the way i would code it also means that some rely on others). Currently they run concurrently but this isnt needed for much process.

Dan
Post #1447571
Posted Monday, April 29, 2013 8:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:27 PM
Points: 3, Visits: 12
danielfountain (4/29/2013)
gfish (4/29/2013)

I am not just speaking hypothetically. We added an almost identical process to our system years ago and we are now spending a lot of time getting rid of it because it is a constant headache. The job queue table backs up regularly on a busy system, and even when it is not backed up it introduces load that competes with user interactive processes. The idea of "do it later" doesn't work when the system is so busy processing interactive requests from users that there is no "later" during working hours.


What have you replaced it with?

I am looking to use it to run a load of stored procs first thing in the morning running parallel (the way i would code it also means that some rely on others). Currently they run concurrently but this isnt needed for much process.

Dan


We replaced it by simply looking at the real need for what was being done in the background jobs and deciding that it probably didn't need to be done at all. Without boring you with a lot of details, let's just say that we were spending a lot of time generating extremely accurate information when a simple approximation would have been sufficient. I know that this is not always possible and you may have looked at this already. I was just cautioning against over-use of this method as a way to avoid fixing bad processes.

For what you are doing it's probably fine, as long as "first thing in the morning" means before working hours. What we did (and what I was cautioning against) caused queued jobs to be added throughout the day, primarily by triggers. Users performing common tasks were unknowingly adding to the pile of queued jobs even though the system seemed to respond quickly to their request.

I would say that as long as the process is initiated by some sort of scheduled or manual administrative action and there is a reasonable certainty that all of the jobs will be completed before they start competing with users for server time then you are probably OK.
Post #1447576
Posted Monday, April 29, 2013 9:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:27 PM
Points: 3, Visits: 12
There are a couple of other things to be aware of when using this method.

The size of your SQLAGENT.OUT file will continue to grow at a much faster rate than it currently does. SQL Agent logs the deletion of every job, even if it was created with the auto-destruct parameter. You may need to purge the SQLAGENT.OUT file regularly. It also makes it much harder to look for actual useful information in the Agent log because you have to filter out all of the delete messages.

We write commercial software, not an in-house proprietary system. In one instance this caused a problem with third-party monitoring software that a customer had installed on their server. One thing that was monitored was long-running Agent jobs. We created the auto generated jobs with a GUID in the name, so the monitoring software added a new entry to its own tables every time one of them exceeded the monitoring software's run-time threshold. There were eventually hundreds of thousands of rows in this table, and because you would only expect to find a few rows in this table it was not carefully indexed. This caused the monitoring software to gradually consume more and more server resources. This lead to even more long-running jobs, which just made the problem grow faster.
Post #1447593
Posted Monday, April 29, 2013 9:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, June 5, 2013 7:45 PM
Points: 380, Visits: 91
i wrote a CLR Procedure to do a similar work:

stpParallel_Processing:
Parameters:
SqlString Ds_Database, (Database Name)
SqlString Ds_Procedure, (Procedure Name)
SqlString Ds_Table, (Table Name)
SqlInt32 Qt_Threads, (Numbers of Threads)
SqlInt32 Fl_Return (Return status)

the idea:
1 - Create a custom procedure with parameters, which will process a unit of calc.
2 - Create a table with columns (same name of procedures parameters)
3 - Execute "stpParallel_Processing"

the procedure will pick each line of "parameters table" and call the custom procedure in parallel (Using the number of threads passed in parameter and parameters suplied in table).

it's working fine :), i'm using it to process variety of data instead of simple while loop.
in some cases, the process time is twenty times faster
Post #1447597
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse