SQL Server Gurus,

I have a situation on one of our servers. This server's sql agent job executes an SSIS flow every hour 24/7. The SSIS flow boils down to inserting/updating/deleting multiple tables while in a transaction block.

Another remote job executes at 8 AM daily which looks at a view that displays the data written by the SSIS job.

I have the view that the remote job reads from set up with a nolock hint to avoid locking errors--since the writes are near continuous throughout the day, however the occasional consequence is the remote job will sometimes not pick up the most current data--basically because the last launch of the SSIS job is still in the midst of writing rows out. I don't usually hear about it until several days have passed, so I lose the opportunity to do a quick sp_who2, etc. to survey what else might be going on with the server that would cause especially slow writes.

2 questions--the first, is how do I set something up going forward to monitor and capture data from the sever for the time slots the remote job executes/reads from my server/view? The second, any suggestions for a better approach to managing the write/lock/read avoidance? Any advice you have is welcomed.

  • 1
    If the "last" run of the hourly job is still running at 8AM, perhaps you need to adjust your hourly job that takes more than an hour to run? Also make sure you understand what nolock does and look into Extended Events for tracing activity Commented Nov 29, 2017 at 18:38
  • @LowlyDBA, basically there's always a job in-flight when the remote job looks for it--its just usually outside of the time range it needs to pick up--except sometimes when the 7 AM run has slowed down for some reason. In this case it was pre-Thanksgiving volume... basically triple what the job usually has to process. I allow the dirty reads because most searches against the data don't require immediate reads of the last thing written. I'll look at extended events for tracing.
    – plditallo
    Commented Nov 29, 2017 at 19:06
  • 1
    One popular method is to log sp_WhoIsActive to a table. Commented Nov 29, 2017 at 19:32


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.