October 10, 2017 at 12:00 am
Comments posted to this topic are about the item WorkPlace Encounters: Grappling With ASYNC_NETWORK_IO Waits
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
October 10, 2017 at 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
October 10, 2017 at 8:59 am
thierry.vandurme - Tuesday, October 10, 2017 2:41 AMHi 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
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
October 10, 2017 at 9:35 am
Br. Kenneth Igiri - Tuesday, October 10, 2017 8:59 AMthierry.vandurme - Tuesday, October 10, 2017 2:41 AMHi 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
ThierryHigh 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!
October 11, 2017 at 12:00 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply