January 8, 2010 at 10:35 am
I need your help
In a production system in sql server 2000 there is a DTS that has 5 procedures using parrallelism in order to load five main tables i. e from table1 to table 5
Ecah process makes some select statements to some oher tables using joins and after that makes an insert statement to a main table i.e table X
The whole process sometimes lasts about 7 hours to finish if all 5 procedures are running in PARALLEL, but the problem is than it barely gets those 5 procedures in PARALLEL.
The process usually has 4 procedures in PARALLEL (Running) and the other process remain sleeping until the first procedure has finished (After 5 hours), consecuently it delays the whole process finishing from 14 or 15 hours delaying the estimated time.
id like to know the reason why some times get 4 procs. running ant the other one sleeping or why sometimes it gets the five running?
using SQL SErver 2000 is there any way to ensure the five procedures start running in PARALLEL in order to not to affect the times, or what can i do?
I'd really appreciate yoor help with your experience and your knowkledge
January 8, 2010 at 7:01 pm
How many cores does the server have? I would imagine that is going to limit how many concurrent threads can run at once.
Joie Andrew
"Since 1982"
January 12, 2010 at 8:18 am
The server has 32 cores (Processors) and 100 GB. The configuration is good for the database server and its client.
Even though i restart the instance, but it doesn change.
What i noticed yesterday i kill 2 procs, let's say procedure1 (running) and process2 (Sleeping), and manually i started to run proc 2 again and inmediately started running in PARALLEL.
Id appreciate you give a light why it happens or how sqlserver (2000) handles its 5 processes.
April 15, 2010 at 11:33 am
Hi! I'm new here, and this is an old thread already, but there's a very simple answer!
DTS packages have a parallelism setting which defaults to 4!
You could set it to 5 - but frankly, I usually have better luck setting it to 1.
I leave it as an exercise for the reader to *find* the setting, but it's on a properties tag for the package somewhere not too obscure, IIRC.
Josh
April 16, 2010 at 1:17 am
Hi,
Except for setting the number of processes that can run in parallel (properties tab for the package) remember to have every process you want to run on a separate connection.
So if You have 5 procedures , set the parallelism to "5" , define 5 different connections to Your DB and run each procedure using a different connection.
This way they won't "wait" for others to end.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply