| Author |
Topic |
|
kittyhundal
Starting Member
25 Posts |
 Posted - 2004-05-28 : 12:34:28
|
| Hi,I'm running two DTS packages. One transfers data from BBX MKeyed files to SQL Server. The other updates SQL Server tables with the transferred data.These packages must execute consecutively. That is, the first transfer must complete, then the second package must execute. If I schedule these packages to run every minute separately, they run quickly (usually taking under a minute for both) using up to 100% of the CPU Processor, but only for a short period of time. However, since I don't know how long it will take for the first package to complete (depends on how much data is being transferred and could potentially take longer than a minute) it seems to be a bit risky to handle it this way. If I create another package which contains both of these packages and then schedule that package, for some reason, it takes a very long time for the package to complete execution. Up to 3 minutes on average.I'm now trying a third option. I've scheduled Package 1, then added Package 2 as step 2 in the schedule for Package 1. When step 1 completes, step 2 is executed, when step 2 completes, step 1 is executed.On my development box which has 1 CPU, this method hogs all of the resources. On our production box which has 2 CPUs, I'm hoping that only 50% of the resources will be used at any given time. I will be testing that this weekend, but need to have another option in the event that isn't the case. I'd really like to get some advice from you guys on this. Do you think my third option is reasonable and workable, if not, why, and what other options are available to me. I have training in database management, etc., but not specifically in SQL Server, so I've been figuring things out by trial and error.One suggestion made by the guys here was to create a step which was just a 60 second pause to free the CPU resources periodically. However, no-one knows how to do this. Any suggestions?Hopefully someone can give me an answer asap since I need to have this running by the end of the weekend.I've posted this question to the SQL Team as well in the hopes that I can get an answer more quickly. Apologize to all if this violates the sites' protocols.Terrific site, by the way. Tons of very helpful information for rookies like me :)Kitty |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-28 : 13:34:21
|
| Your piece about the CPUs is incorrect. It might only use 50%, but it could also use 100%. To wait a certain amount of time, create your step and put this in it:WAITFOR DELAY '000:00:60'MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2004-05-28 : 15:00:15
|
| Any reason why these two steps are seperate packages? And how often do you want them to run?- Eric |
|
|
|
kittyhundal
Starting Member
25 Posts |
Posted - 2004-05-28 : 15:13:11
|
| Hi EricI've made these two steps separate packages because one is a OLE DB SQL Server connection to an ODBC BBX driver (the ODBC BBX driver can't handle multithreading) and the other is an ODBC SQL Server connection to another ODBC SQL Server Connection.Kitty |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2004-05-28 : 17:35:01
|
| Maybe in the second package you chould check if the first package is still running, and if so just wait until it stops.- Eric |
|
|
|
kittyhundal
Starting Member
25 Posts |
Posted - 2004-05-28 : 17:39:45
|
| Combined everything into a single package and ended up with the same problem. The logs indicate that everything is taking 15 seconds (altogether) to run. However, unscheduled, it's taking 2 minutes, and scheduled it takes 4 minutes (even though the DTS run status indicates the job took less than a minute to run)??Am I missing something here? |
|
|
|
kittyhundal
Starting Member
25 Posts |
Posted - 2004-05-28 : 17:41:09
|
| Shouldn't this be happening when I run the 2 packages as two steps?I scheduled one package, then added the other package as step 2.Still had the same problem. |
|
|
|
kittyhundal
Starting Member
25 Posts |
Posted - 2004-05-28 : 17:45:52
|
| To answer Eric's question about how often. I need them to run at the very least every minute. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-06-04 : 03:05:07
|
Firstly, I don't see why you want to "free the CPU resources periodically". SQL Server will handle thread / cpu allocation better than you can. quote:
The logs indicate that everything is taking 15 seconds (altogether) to run. However, unscheduled, it's taking 2 minutes, and scheduled it takes 4 minutes (even though the DTS run status indicates the job took less than a minute to run)??
This makes no sense. If everything takes 15 seconds, how can it take 2 minutes?You need to investigate what is slowing the process. If it is CPU limits, increase the CPU power, if it is network, do something about that. Try running some profile and performance tests.-------Moo. :) |
|
|
|
kittyhundal
Starting Member
25 Posts |
Posted - 2004-06-24 : 14:11:18
|
| Thanks for the info. Loaded the packages onto the Production Server and everything is working fine. Used the method of running each package as a step but the last step exits. The scheduler restarts the package every minute.Kitty |
|
|
|
|
sql server and cpu processing