December 4, 2003 at 3:59 am
I have created several jobs on a specific SQL instance on a server running multiple instances.
For unknown reasons the execution time of these jobs have doubled in the last few weeks.
Jobs taking 30 minutes are now taking 1 hour and are taking more time every day.
Agent jobs running on the other instances seem to have no performance decrease.
The instance has a weekly maintenance plan.
I inherited these scripts from our remote office since we recently moved their ERP to our central server.
They had noticed the same and solved the problem by using windows task manager to run a batch file.
When I run the scripts via osql everything works out fine. Execution time is as expected.
So problem solved!?
Not to me. Can anyone tell me what is causing this and how to prevent it?
Thanks in advance for any reply.
Leon
Regards,
Leon Bakkers
December 4, 2003 at 7:04 am
This is something we noticed empirically, I don't have docs saying this is actually the case. Take it with a grain of salt. Jobs run through SQL Agent seem to run at a lower priority than commands you'd execute through isql, osql, or isqlw. For small jobs it's not very noticeable but for larger ones, obviously it is.
A workaround is to use a CmdExec job step and execute the same T-SQL command using isql or osql. We found that when SQL Server Agent turned over control to the OS, the priority was normal and the commands executed in the speed we were accustomed.
As to why you suddenly saw a huge time increase, I can't answer unless the statistics are out of date or something of that sort which is performance related.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
December 5, 2003 at 10:15 am
Brian,
I am experiencing the same issue with some jobs in one of our servers.
Would you please elaborate a little bit further and, if possible, place some example code to do that?
Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply