Voice of the DBA

Tag: mcm

  • MCM Prep – VLF Allocations

    I had heard that you could have too many VLFs in your transaction log, and it could cause performance issues. Kimberly Tripp has a great blog on how to get better performance from your log, but until I was studying for the MCM, I didn’t realize you could have too few.

    The issue seems to be that if you allocate too much transaction log at once, you could end up with a few, very, very large (GB in size) VLFs, and despite regular log backups, you could not be clearing any VLFs inside the log until there are no active transactions in that VLF. And that could cause a performance slowdown as it clears that large VLF.

    How are VLFs allocated? Here’s the formula:

    • chunks less than 64MB = 4 VLFs
    • chunks of 64MB and less than 1GB = 8 VLFs
    • chunks of 1GB and larger = 16 VLFs

    This is from : http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx#ixzz19KysnYcK

    So if you need 50GB of log space, do as Kim recommends and get 512MB chunks by allocating 8GB at a time. You can do this contiguously, as in all the same day, but allocate 8GB, let it finish, then ALTER to add 8GB more, repeat until you reach the correct size.

  • The MCM Journey

    I’m not really planning on getting the MCM. Perhaps I’ll change my mind, but it’s not the time for me to do it. I’m not consulting, and not planning on doing it anytime soon. If I studied enough to pass the lab, likely my skills would atrophy without some real work. At this point in my life, I don’t have the time for getting real work.

    Actually, I don’t have the time for real studying, either. However MVPs have the chance to take the written exam for free, instead of $500, and I can’t really justify passing up this opportunity. I scheduled it in January, and last week started studying by downloading some of the MCM Readiness videos from SQL Skills.

    I’ve been slowly starting to watch them, and I realize how much I don’t know about SQL Server as I go through them. It’s not all bad news as I also realize how much I do know in terms of general concepts and understanding of how the pieces and parts work.

    I don’t expect to pass, especially after seeing that someone like Jason Strate didn’t on his first try, but this is a good chance for me to focus on a task and also to get an idea of where I stand.