March 27, 2009 at 3:02 pm
Hello,
I'm sure there is a thread on this already, but I was having trouble coming up with a search term to find it. I apologize if it repeats an existing thread - and if you know the thread URL, I'd be grateful if you could post it.
Also, I realize that I could test this with a sample job, but I want to know theoretically what SQL Server is designed to do, so I can know what is expected versus what actually happens.
My question is this:
Suppose a SQL Server Agent job is scheduled to run every 15 minutes. A run of that job starts at 1:00 pm. But suppose that this particular run of that job takes 20 minutes to complete. Which of the following will happen?
1. The next scheduled run (1:15 pm) starts and executes while the 1:00 pm job is running.
2. The next scheduled run (1:15 pm) starts but is blocked by the 1:00 pm job, so it does not start executing until 1:20 pm - but it runs to completion from then.
3. The next schedule run (1:15 pm) fails because the 1:00 pm job is already running.
4. Something else (please fill in the answer).
Thanks for any help.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 27, 2009 at 3:13 pm
If the job is already running, it doesn't even try to start it, and waits till the next scheduled time after it finishes the current run.
In your example, it wouldn't start at 1:15, or 1:20, but would start again at 1:30.
Make sense?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 27, 2009 at 3:31 pm
GSquared (3/27/2009)
If the job is already running, it doesn't even try to start it, and waits till the next scheduled time after it finishes the current run.In your example, it wouldn't start at 1:15, or 1:20, but would start again at 1:30.
Make sense?
Yes, that makes perfect sense. Thanks for the explanation. I guess the best thing to do is to estimate as well as possible how long the job will usually take, and then space the jobs accordingly. Although, it seems like having the job run exceed the scheduled interval too often defeats the purpose of having that job schedule interval in the first place.
It also just occurred to me that if the requirement is to have the jobs have to run at a certain interval - say, hourly - then the challenge would be to make sure the job completes in much less than one hour. And if the job takes anywhere close to an hour to run - say, 45 or 50 minutes - then basically that means the job is running 75% to 85% of the time. I wonder whether there is some cost-benefit analysis that can be weighed against user requirements that can space jobs so that they are not 80% "on" and 20% "off." Or maybe this is a moot point because the database server has to meet what the users demand of it?
Thanks again!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 28, 2009 at 6:09 am
Whatever this job is, I hope it's not too intrusive to the system since it sounds like it's going to be running 24/7. If you're inserting or updating data all the time, how will the users read the data out of the database?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 29, 2009 at 10:06 am
Grant Fritchey (3/28/2009)
Whatever this job is, I hope it's not too intrusive to the system since it sounds like it's going to be running 24/7. If you're inserting or updating data all the time, how will the users read the data out of the database?
Thanks for your reply. That is what we're trying to figure out - we need to feed data into a set of intermediate tables and then into the production tables.
Is there a standard way to do this on an hourly basis without locking tables for too long? Also, what is the longest acceptable locking time for such a job? A couple of seconds? I'm afraid that's the answer, but given the number of table we need to feed to, the current time is about 15 seconds for each set of records (that's the time for inserting the necessary records to the related tables for one person's overall "record" in the system). Way too long, especially if we have to process more than 2 person's records per hour, which will likely be the case.
So we have competing priorities - the need to maintain the integrity of the data against the need to complete the inserts and updates quickly. The only way I can see to break up the transaction into smaller pieces is to insert or update only some tables at a time, which risks the integrity - for example, if records in 10 tables constitute a complete "record" for a person, then insert/update 2 tables per transaction and have 5 small transactions instead of 1 big one.
Thanks for any help - I realize this is a tall order.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 29, 2009 at 11:02 am
You might look at SQL Sentry's scheduling solution for help with the jobs.
As far as making this faster, you would need to get execution plan for each insert/update and see if there is something you can do to make it faster by tuning.
If you get to that point, then you might have underpowered IO. Perhaps you need another array to move some tables to or a faster subsystem.
March 29, 2009 at 2:00 pm
Depending on how the data is distributed, what kind of data, etc., you might want to look into partitioning. This would allow most of the data to be online and available and you would only be inserting into a single area.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply