October 7, 2015 at 3:37 pm
I have heard that high numbers of VLF's aren't good. It can impact performance and can delay recovery time, so I wanted to test that.
I created 2 DBs with 100MB datafile and 50MB logfile.
TestDB log file had 100MB autogrowth
TestDB2 log file had 1% growth.
I inserted 1048576 records, took the backup
Ran DBCC loginfo and
TestDB had 40 VLFs and
TestDB2 had 165 VLFs
but when I restored both DBs, this is what I got.
TestDB:
RESTORE DATABASE successfully processed 42258 pages in 4.420 seconds (74.691 MB/sec).
SQL Server Execution times:
CPU Time = 125ms, elapsed time = 8323 ms.
TestDB2:
RESTORE DATABASE successfully processed 42257 pages in 3.943 seconds (83.724 MB/sec).
SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 8314 ms.
Question is: Where is the difference? How TestDB which has 40 VLFs are better than TestDB22 which has 165 VLFs.
October 8, 2015 at 3:44 am
Chitown (10/7/2015)
I have heard that high numbers of VLF's aren't good. It can impact performance and can delay recovery time, so I wanted to test that.I created 2 DBs with 100MB datafile and 50MB logfile.
TestDB log file had 100MB autogrowth
TestDB2 log file had 1% growth.
I inserted 1048576 records, took the backup
Ran DBCC loginfo and
TestDB had 40 VLFs and
TestDB2 had 165 VLFs
but when I restored both DBs, this is what I got.
TestDB:
RESTORE DATABASE successfully processed 42258 pages in 4.420 seconds (74.691 MB/sec).
SQL Server Execution times:
CPU Time = 125ms, elapsed time = 8323 ms.
TestDB2:
RESTORE DATABASE successfully processed 42257 pages in 3.943 seconds (83.724 MB/sec).
SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 8314 ms.
Question is: Where is the difference? How TestDB which has 40 VLFs are better than TestDB22 which has 165 VLFs.
Quite a few factors here, firstly the throughput for the second restore was higher so the restore would be quicker.
Secondly, with not much to process in the transaction log the recovery is likely unhindered.
Logs with a high number of VLFs and a high number of committed and uncommitted transactions can see longer recovery times due to the number of VLFs that have to be scanned\processed.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 8, 2015 at 3:55 am
You're not going to see much of a difference with a hundred or so VLFs. Try getting up to tens of thousands.
You see the effect mostly on recovery, when starting the database up, also on log backups and other processes which read the log. Restores read the backup file and write to the data file, so they're not going to be impacted by VLFs. They don't write the log backup to the log file itself and then process it from there.
Tony and I tested this out: http://www.sqlservercentral.com/articles/Stairway+Series/98295/
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
October 8, 2015 at 11:44 am
New Testing:
TestDBSize 1.048GB
Rows Inserted 2097152(00:00:09)
Rows Inserted 4194304(00:00:23)
Rows Inserted 8388608(00:00:66)
Rows Inserted 16777216 (00:05:15)VLFs 152
Recovery Time = 00:08:43
TestDB2Size 1.048GB
Rows Inserted 2097152(00:00:17)
Rows Inserted 4194304(00:00:34)
Rows Inserted 8388608(00:01:20)
Rows Inserted 16777216 (00:06:19)VLFs 554
Recovery Time = 00:06:45
October 8, 2015 at 11:55 am
You're not going to see a difference in inserts or restores. See the article I referenced where Tony and I tested the difference between about 20 VLFs and 16000 VLFs for recovery after startup and taking log backups.
500 VLFs is also not a lot and not likely to cause problems. Try testing with tens of thousands.
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
October 9, 2015 at 7:31 am
are you saying with a restore you wont see enough recovery to cause any impact?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 9, 2015 at 7:47 am
Perry Whittle (10/9/2015)
are you saying with a restore you wont see enough recovery to cause any impact?
No, I'm not saying that.
I'm saying that the recovery process after a restore (log, full or diff) reads the log backup from the backup file. It's not first written into the transaction log and then recovered from there. Now I don't know offhand how the backed up log is architected, whether it has the same VLF structure as the log itself or not. If it doesn't, if the log backup is a single chunk of log records, then recovering after a restore won't be subject to the same overhead of opening every single VLF in the transaction log file.
That said, every article, post, discussion which talks about the impact from lots of VLFs, talks about the impact on operations which read from the transaction log (backups, crash recovery, etc), not restore operations.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply