question

lok_taurian avatar image
lok_taurian asked

Performance issues with SQL Job

Team, I am facing a strange problem. We have a End of Day job in our system. in about 5-7 days in a month, This job fails in about 70 locations indicating Timeout expired error. When the job runs the next day after failure, it picks and process the data for two days (The previous day - where the job failed) and the current day. and surprisingly, the job completes with in three minutes !! Experts advice required on the root cause for this issue and the resolution for the same. Thanks
performance-tuning
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
We need a little more information about what the job is doing. You mention 70 locations, does the job run on one server or on 70 servers? What does the job do? SSIS, T-SQL, Batch file? Does the job fail at the same time on the same day each month? Is anything else running at that time that could be contending for resources? What version and edition of SQL Server is this running on?
1 Like 1 ·
lok_taurian avatar image lok_taurian commented ·
The job runs on multiple servers (nearly 1200 of them). every month, there is a failure on 70 locations(average) out of 1200 locations. The job purpose is simple - load data from transaction tables into reporting tables. There are about 15 tables and we have separate SPs to load each table. We have a wrapper SP which calls all of these 15 SPs. The job calls the wrapper SP. The job fails randomly. There is no specific pattern for failure. failure happens on both SQL 2000 and SQL 2005. There are no other jobs running when this job executes. Thanks
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
They are loading transaction tables into reporting tables - are these reporting tables on each server, or a central reporting system? Do you get information on what exactly is timing out?
0 Likes 0 ·
lok_taurian avatar image lok_taurian commented ·
Data is loaded into respective servers. Cetnral reporting system is not involved here. Time out generally occurs after 30 minutes. My doubt is how can it take 30 minutes, when it can complete processing double the data (The next day) with in Three minutes ?
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
You will need to run a trace to see what is happening on the systems at the time of the timeout. Are you sure you are not running anything else on the system at the same time? Backups, copy jobs. It may not be in SQL Server, but some other external process - check with the systems/network/san admins to make sure they aren't doing something at that time.
0 Likes 0 ·
lok_taurian avatar image lok_taurian commented ·
Thank you very much for your suggestions William. I will speak to the network team and try to collect information on this.
0 Likes 0 ·
Blackhawk-17 avatar image Blackhawk-17 commented ·
Are these physically separate systems or multiple instances on the same hardware, or even multiple VMs? You might be getting hardware contention.
0 Likes 0 ·

1 Answer

·
Fatherjack avatar image
Fatherjack answered
Are you able to alter the schedule that this job runs on? I would suggest maybe moving it forward or back by 30 minutes or so and see if it resolves the issue. If so then its a conflict - maybe locks/blocks - with another job running at the same time. Its only a stab in the dark but might get you a resolution quickly...
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.