VLF Fragmentation and Blocking

  • Has anyone every experienced a situation where VLF fragmentation has made blocking worse? Generally, I'd expect the two to be unrelated but I'm getting blocking behavior that is making me wonder.

  • I personally have not seen that.

    How many VLF fragments do you have on the DB in question?

  • 272 VLF's. It is a vended app that has always had one or two problem querys that were good for a 2 or 3 blocks a day. Duration was typically 9 or 10 seconds.

    The last week blocking increased to a dozen blocks a day and different queries. Only one changes has gone to prod and the developers say it doesn't relate to the queries I'm seeing.

    The sudden change in behavior is what has me suspicious of VLF.

  • As I said, I have not experienced that sort of an issue and been able to directly relate the poor performance to the VLF fragmentation. However, in the past I have had MANY developers tell me "oh we changed something that is in no way related...." it generally was.

    I would start with the usual suspects like execution plans, etc. The slightest change in data volumes can change the execution plan complete.

    Are the indexes rebuilt/reorg'ed on a regular basis?

    What about statistics how often are they updated?

  • Thanks for challenging my assumptions. I haven't reviewed the basics and should have.

    I'm the only Sql guy in my shop so kicking ideas out here and getting feedback is very help for me. I really appreciate the professionals on this board.

    David

Viewing 5 posts - 1 through 4 (of 4 total)

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