Can anyone explain this.
A CRM package holds all its data in one filegroup. We use a view based on 3 tables. The view appears to hang an a regular basis. We recreated the problem in a test environment with no active users so it doesn't appear to be a direct (b)locking problem. We looked in sp_lock. The code looks clean too.
Getting desperate, we simply moved the underlying tables to a new file group (on the same array) and this solved the problem.
However I can't understand why. Any suggestions?