Post reply

Moving tables to a new file group unblocks a view

  • August 2, 2005 at 2:59 am

    #110161

    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?

  • August 3, 2005 at 1:54 am

    #579037

    Moving the tables will have physically defragmented them. Is there a clustered index? Have you tried a reindex? It looks like some kind of i/o contention.

  • August 3, 2005 at 4:41 am

    #579082

    SQL Server will, with a fragmented index (clustered on nonclustered) , lock more records then it needs and therby create big blocking problems.

    Ex: If you UPDATE a record in a clustered index you expect just that record to be locked but if the index is fragmented SQL Server can instead put a range lock or even a table lock.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply