October 6, 2015 at 3:47 am
I have a SQL Server agent job that runs in 2 minutes on average. This job is scheduled to run from 8.30am - 6pm everyday, every 3 minutes. I've noticed that the very first run each day (8.30am) takes a bit longer - the norm being around 30 seconds longer, but often over a minute longer, and on occasion up to 5 minutes. It's the only time of the day we have this problem. I've been assured that the only other task that runs on the server around this time is a scheduled task to delete old backups, which happens at 8am.
The job runs the following steps:
1. Run SSIS package (this is the step taking too long)
2. If step 1 exceeds 3 minutes, send an e-mail alert (temporary step but doesn't seem to slow anything down)
I don't believe it to be caused by the ETL task itself (step 1) as performance is only ever poor at 8.30am. I've been monitoring how many records are being inserted/updated/deleted at this time but often it is only a single record., or none at all.
It doesn't have a huge impact on our work, but I am curious as to why this happens.
Has anyone experienced similar/have any thoughts please?
October 6, 2015 at 5:18 am
Its very hard to say without actually seeing what is happening at the specific time on the server.
However it sounds potentially like you have blocking occurring which is holding up your process.
Running something like sp_whoisactive at the time will give you an idea if anything is blocking, or you just get a bad execution plan from a query etc:
See here for details http://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/
MCITP SQL 2005, MCSA SQL 2012
October 6, 2015 at 5:23 am
Perhaps the data which the SSIS package interacts with is not in cache the first time it's run.
I'm surprised it takes so long for so few rows. What exactly does it do? Are there simple queries involved anywhere in it's operation?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 6, 2015 at 5:26 am
RTaylor2208 (10/6/2015)
Its very hard to say without actually seeing what is happening at the specific time on the server.However it sounds potentially like you have blocking occurring which is holding up your process.
Running something like sp_whoisactive at the time will give you an idea if anything is blocking, or you just get a bad execution plan from a query etc:
See here for details http://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/
Thankyou, that looks really helpful. I'll try this out and let you know if I have any success.
October 6, 2015 at 5:34 am
ChrisM@Work (10/6/2015)
Perhaps the data which the SSIS package interacts with is not in cache the first time it's run.I'm surprised it takes so long for so few rows. What exactly does it do? Are there simple queries involved anywhere in it's operation?
That is possible, thankyou.
In theory it should be a relatively straightforward collection of upsert/delete tasks, unfortunately it's full of poorly written and inefficient procedures (I believe the individual who created the process wasn't too familiar with SQL and was working under pressure), if I was able to share it with you I don't think you'd be surprised at it taking so long!
October 6, 2015 at 5:42 am
tindog (10/6/2015)
ChrisM@Work (10/6/2015)
Perhaps the data which the SSIS package interacts with is not in cache the first time it's run.I'm surprised it takes so long for so few rows. What exactly does it do? Are there simple queries involved anywhere in it's operation?
That is possible, thankyou.
In theory it should be a relatively straightforward collection of upsert/delete tasks, unfortunately it's full of poorly written and inefficient procedures (I believe the individual who created the process wasn't too familiar with SQL and was working under pressure), if I was able to share it with you I don't think you'd be surprised at it taking so long!
I guess you've just identified your own coalface. If you're lucky you might find a quick win or two. A query I was thrown this morning which took nearly 15 minutes to run, executes in 1.5 seconds with the index hints removed...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 6, 2015 at 5:53 am
ChrisM@Work (10/6/2015)
tindog (10/6/2015)
ChrisM@Work (10/6/2015)
Perhaps the data which the SSIS package interacts with is not in cache the first time it's run.I'm surprised it takes so long for so few rows. What exactly does it do? Are there simple queries involved anywhere in it's operation?
That is possible, thankyou.
In theory it should be a relatively straightforward collection of upsert/delete tasks, unfortunately it's full of poorly written and inefficient procedures (I believe the individual who created the process wasn't too familiar with SQL and was working under pressure), if I was able to share it with you I don't think you'd be surprised at it taking so long!
I guess you've just identified your own coalface. If you're lucky you might find a quick win or two. A query I was thrown this morning which took nearly 15 minutes to run, executes in 1.5 seconds with the index hints removed...
Possibly. On the test server I've managed to get the process down from 1.5mins to 15 seconds so far, mostly from changing the upsert from slowly changing dimension to use a T-SQL MERGE. However the issue in the original post has never happened on the test server (although the usage on this server is significantly less so that could explain why?)
That's pretty good!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply