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.