WorkPlace Encounters: Grappling With ASYNC_NETWORK_IO Waits

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4521

    Comments posted to this topic are about the item WorkPlace Encounters: Grappling With ASYNC_NETWORK_IO Waits

    Br. Kenneth Igiri
    www.scribblingsage.com
    All nations come to my light, all kings to the brightness of my rising

  • thierry.vandurme

    Hall of Fame

    Points: 3098

    Hi Kenneth, awesome article, thanks for sharing!
    One question if I may... You mention reducing the amount of VLFs. Is that related to a specific wait? I know you could come to that conclusion if it takes a long time to restore a small database for example, but is it something you can deduce from wait stats related DMV's as well?
    Thx
    Thierry

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4521

    thierry.vandurme - Tuesday, October 10, 2017 2:41 AM

    Hi Kenneth, awesome article, thanks for sharing!
    One question if I may... You mention reducing the amount of VLFs. Is that related to a specific wait? I know you could come to that conclusion if it takes a long time to restore a small database for example, but is it something you can deduce from wait stats related DMV's as well?
    Thx
    Thierry

    High number of VLFs are mostly caused by incorrect settings in the log file AUTOGROWTH value. When the AUTOGROWTH is small of specified as a percentage, it is likely that growths will be happening often and one might experience WRITE_LOG or similar wait events. I did not deduce the need to correct the configuration from a wait event in this case however but it is a basic practice I adopt to eliminate problems in this area. An YES in fact the backup time was also quite poor when we tried to relocate the databases involved in this case. It was corrected once we changed the autogrowth setting, shrunk the log and allocated a good amount as initial size for the transaction log file. (Credits to Glen Berry)

    Br. Kenneth Igiri
    www.scribblingsage.com
    All nations come to my light, all kings to the brightness of my rising

  • thierry.vandurme

    Hall of Fame

    Points: 3098

    Br. Kenneth Igiri - Tuesday, October 10, 2017 8:59 AM

    thierry.vandurme - Tuesday, October 10, 2017 2:41 AM

    Hi Kenneth, awesome article, thanks for sharing!
    One question if I may... You mention reducing the amount of VLFs. Is that related to a specific wait? I know you could come to that conclusion if it takes a long time to restore a small database for example, but is it something you can deduce from wait stats related DMV's as well?
    Thx
    Thierry

    High number of VLFs are mostly caused by incorrect settings in the log file AUTOGROWTH value. When the AUTOGROWTH is small of specified as a percentage, it is likely that growths will be happening often and one might experience WRITE_LOG or similar wait events. I did not deduce the need to correct the configuration from a wait event in this case however but it is a basic practice I adopt to eliminate problems in this area. An YES in fact the backup time was also quite poor when we tried to relocate the databases involved in this case. It was corrected once we changed the autogrowth setting, shrunk the log and allocated a good amount as initial size for the transaction log file. (Credits to Glen Berry)

    Ok, thanks. I was aware of the impact of autogrowth settings (learned the hard way when restoring a small db that took ages to complete:)) but I thought I'd missed something in your wait stats list that would indicate high no. of VLFs being an issue.
    Thanks for replying!

  • suparSteve

    SSC Enthusiast

    Points: 186

    Very interesting story and reminder of how you have to be careful not to get tunnel vision where you only consider your own little piece of the picture.

  • bdeniz

    SSC Rookie

    Points: 34

    'be interesting to know what happened or what was wrong with the NICs / Teaming configuration. A reference to the relevant Networking blog, perhaps, or a bit of denormalisation?

    • This reply was modified 2 weeks, 1 day ago by  bdeniz.

Viewing 6 posts - 1 through 6 (of 6 total)

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