Post reply

VLFs sizes

  • May 30, 2013 at 4:42 pm

    #274380

    Hi All,

    I want to see someone's explanation if there is a performance loss (and why in case of) when some VLFs are small and some are big, in a transaction log.

    For example a database had had small growths of 128MB, and each VLF with 16MB; and then a change of 4608MB is set for the growth, and each new VLF is now 288MB.

    I know how to make the VLFs all equal.

    It will be good posting some links where this issue is discussed, too.

    Thank you,

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • May 30, 2013 at 4:57 pm

    #1619933

    IgorMi (5/30/2013)

    Hi All,

    I want to see someone's explanation if there is a performance loss (and why in case of) when some VLFs are small and some are big, in a transaction log.

    For example a database had had small growths of 128MB, and each VLF with 16MB; and then a change of 4608MB is set for the growth, and each new VLF is now 288MB.

    I know how to make the VLFs all equal.

    It will be good posting some links where this issue is discussed, too.

    Thank you,

    IgorMi

    Hi Igor,

    I enjoy reading his blogs so I thought I would reference you these, I'm sure you've read the more popular ones on SQLSkills about it.

    http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx

    http://sqlblog.com/blogs/linchi_shea/archive/2009/02/12/performance-impact-a-large-number-of-virtual-log-files-part-ii.aspx

    From his tests it's based on affect transactions ( I read that article a few years ago..so going from memory and assuming he only ran transactions )

    .... However backup/restore operations.. for restore it may make an impact. (I rcecall restoring a DB with 8,000+ VLFs 250GB+ DB)

    --------------------------------------------------
    ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

  • May 30, 2013 at 11:46 pm

    #1619956

    Shouldn't be any noticeable effects from different sizes of VLFs, if there are even any effects at all. It's large numbers of VLFs that cause a problem, and that is mostly around database recovery and restore and to a lesser extent backups and replication.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 31, 2013 at 1:54 am

    #1619977

    Hi,

    @sqlsurfing: Thank you for your links. I knew and read those articles/posts. I was looking to find something concretely for the VLFs' different size, and probably like Gail said it should not be any issues except the high number of VLFs during recovery, restore, ...

    Thanks to both!

    Regards,

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • May 31, 2013 at 5:54 am

    #1620024

    IgorMi (5/31/2013)

    Hi,

    @sqlsurfing: Thank you for your links. I knew and read those articles/posts. I was looking to find something concretely for the VLFs' different size, and probably like Gail said it should not be any issues except the high number of VLFs during recovery, restore, ...

    Thanks to both!

    Regards,

    IgorMi

    Beyond what has already been spoken,With a large number of VLF's files (DBCC LOGINFO) you can have performance problems using replication.

    Regards,

    André CR

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

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