Some of you may remember what's been going on for the last few months. I appreciate the help received from these boards and feel that a final solution is looming.
Overview: Datamart server which runs a 3rd party ETL process. Experiencing erradict results in performance. Advised to play detective and learned that server is being used to run other reports. These reports are not utilising indices, nor are they properly written. The result is blocking. The blocking throws serious delays in the ETL process causing schedules to overlap, etc, etc. Bad news.
I ended up using sp_blockerpss80. The procedure collects a view of system table info. The procedure shows a series of PAGEIOLATCH_SH before the actual blocking shows up. I've tried to no avail to figure out what causes this. Is this a hardware issue? Something to do with disk writes/sec?
Anyway, the main question now is: How can I single out the ETL process to not be affected by ad hoc reporting? The system is not designed very well, meaning they both use the same user name. Is there a way to Kill all processes when the ETL process kicks off? It just needs about 30 minutes every 4 hours to run properly.