How to avoid delays in Sql Server Agent Jobs every certain time?
Hi, I have a SQL agent with 2 steps of SSIS packages and running every 10 seconds, every certain time the agent is taking longer than unusual, I don't if it is the database that is not allowing query constantly or an issue with the job. I want to be able to get an average of 7-8 minutes which is acceptable. Here is an image with the issue highlighted: ![Image] : /storage/temp/4562-issue.png
A few things to check here. Are the times of the long running queries consistent? For example does it generally happen around 5:27AM. Are there any other jobs running at these times. What kind of workload is on the database/server at those times. Is it across multiple servers? could there be a network lag at that point in time. Lots to conside and it very much depends on your system. I have multiple jobs running and the timing can vary like yours simply because other things are happening on the server/database at that time. If other things are happening it could be competing for resources. Could be anything from an insert causing a lock into a table that your job is trying to query, to updates on the server.