| Author |
Topic |
|
shah429
Yak Posting Veteran
52 Posts |
 Posted - 2009-07-31 : 05:46:49
|
| Hi All, I understand that if the VLFs are of larger size, it can degrade performance as it might take quite a bit of time to clear the vlf when a log backup is performed. Is that correct? Secondly I want to know how too many VLFs can degrade performance or create fragmentation in the transaction log. Can anybody please advice. Thanks in advance guys. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
shah429
Yak Posting Veteran
52 Posts |
Posted - 2009-07-31 : 10:19:09
|
| Well, the question was HOW TOO MANY VLFs cause a performance bottleneck. I know how to prevent it, I just want to understand HOW it is causing fragmentation in the log file. I posted the same question on the MSDN forums and people started explaining how to avoid the problem, so I posted the question here again. Anyone please? Thanks in advance |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-07-31 : 15:02:38
|
| Post a comment on Tony's blog and see if he answers. I don't know the details offhand, but basically it comes down to physical and logical fragmentation causes more random I/O. Having fewer VLFs, and/or contiguous VLFs, reduces that and possibly permits sequential I/O. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
shah429
Yak Posting Veteran
52 Posts |
Posted - 2009-08-05 : 18:03:26
|
| Could you put it into words based on Kimberley's blog post? You probably can't because she didn't cover that. I had the same question posted on the msdn forums and nobody could reply. Not sure if anybody knows... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-08-05 : 20:43:50
|
| Let me ask some questions:1) Are you experiencing the problem on your SQL Servers and wish to correct it?2) Are you trying to prevent the problem from ever occurring?3) Are you simply curious about the causes and want to educate yourself on the internals of SQL Server log I/O?Unless you are only interested in #3, the links that have been provided should answer all of your questions. If not, you are going to have to provide a detailed example of the kind of answer you want. |
|
|
|
shah429
Yak Posting Veteran
52 Posts |
Posted - 2009-08-06 : 02:16:14
|
| Well the question is clear, how/why does having too many VLFs cause a performance degradation? I can't be more clear than this. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-08-06 : 02:35:51
|
I think it's pretty clear with this estimatequote:
* chunks less than 64MB = 4 VLFs * chunks of 64MB and less than 1GB = 8 VLFs * chunks of 1GB and larger = 16 VLFs
If your number of VLFs exceed {4, 8, 16}m then your log file probably is fragmented (you have too many VLFs) and performance will degrade.I don't know how much more clear I can be? You didn't provide ANY information about your environment so we have to give you a general answer. For example, you didn't tell us how large your current log file is, what size it was originally and neither how many time it as exanded (either manually nor with auto-growth).Then, of course, you have to do some math yourself. I hope this isn't too hard for you?If you need further help, please post some relevant information and some specifics about your environment concerning the database of choice you need help with.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
shah429
Yak Posting Veteran
52 Posts |
Posted - 2009-08-06 : 07:22:27
|
| hmmm.. I don't know why you guys are not getting it. You said:"If your number of VLFs exceed {4, 8, 16}m then your log file probably is fragmented (you have too many VLFs) and performance will degrade."How are you going to justify that. Why do you think too many VLFs cause a problem/fragmentation. Check this link:http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/43105b39-0fb3-4276-a0c3-468277c37941/#fa3baaaa-81e1-4459-a523-865671943d4d |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-08-06 : 07:29:34
|
Maybe this angle clears your mind?http://blogs.technet.com/magi/archive/2009/05/22/fragmentation-the-database-performance-killer.aspxFrom the second paragraph of Kimberley Tripps blog postquote:
If you preallocate a very large transaction log (10s to 100s of GB), SQL Server may only allocate a few VLFs - as a result, log backups will be allowed to run normally but, SQL Server only clears the inactive VLFs when you've moved into a different VLF. If your VLFs are 8GB in size, then you need to accumulate 8GB of log information before the log can be cleared...so, many of your log backups will occur normally but then one (the one that finally hits > 8GB in used size) will take quite a bit more time AND possibly cause you performance problems because it's now clearing 8GB of log information.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
Too many VLFs