Internally SQL Server breaks down a transaction log file into a
number of sub-files called virtual log files (VLFs). SQL
Server determines the number and size of VLFs to allocate to a log file, upon
creation, and then will add a predetermined number of VLFs each time the log
grows, based on the size of the auto-growth increment, as follows (though, for
very small growth increments, it will sometimes add fewer than four VLFs):
·
<64 MB – each
auto-growth event will create 4 new VLFs.
·
64MB to 1 GB = 8 VLFs.
·
>1 GB = 16 VLFs.
For example, if we create a 64 MB log file and set it to auto-grow
in 16 MB increments, then the log file will initially have 8 VLFs, each 8 MB in
size, and SQL Server will add 4 VLFs, each 4 MB in size, every time the log
grows. If the database attracts many more users than anticipated, but the file
settings are left untouched, by the time the log reaches 10 GB in size, it will
have grown about 640 times, and will have over 2,500 VLFs.
Towards the other end of the scale, if we grow a log in 16 GB
chunks, then each growth will add 16 VLFs, each 1 GB in size. With large VLFs,
we risk tying up large portions of the log that SQL Server cannot truncate, and
if some factor further delays truncation, meaning the log has to grow, the
growth will be rapid.
The trick is to obtain the right balance. The maximum
recommended auto-growth size is about 8 GB (advice offered by Paul Randal in
his Log File Internals and Maintenance video). Conversely, the growth
increments must be large enough to avoid an unreasonably large number of VLFs.
There are two main reasons to avoid frequent small auto-grow
events. One is that log files cannot take advantage of instant file
initialization, so each log growth event is relatively expensive, compared to
data file growth, in terms of resources. A second is that a fragmented log can
impede the performance of operations that read the log.
Many operations will need to read the transaction log,
including:
·
Full,
differential and log backups – though only the latter will need to read substantial portions
of the log.
·
Crash
recovery process – to reconcile the
contents of data and log files, undoing the effects of any uncommitted
transactions, and redoing the effects of any that were committed and hardened
to the log, but never made it to the data files.
·
Transactional
replication – the transactional
replication log reader reads the log when moving changes from the publisher to
the distributor.
·
Database
mirroring – on the mirror
database, the log gets read when transferring latest changes from the primary
to the mirror.
·
Creating
a database snapshot –
which requires the crash recovery process to run.
·
DBBC CHECKDB – which creates a database snapshot when it
runs.
·
Change
Data Capture – which uses the
transactional replication log reader to track data changes.
Ultimately, the question of a "reasonable" number of
VLFs in a log file will depend on the size of the log. In general, Microsoft
regards more than about 200 VLFs as a possible cause for concern, but in a very
big log file (say 500 GB) having only 200 VLFs could also be a problem, with
the VLFs being too large and limiting space reuse.
Transaction Log VLFs – too many or too few?
Disclaimer
The tests that follow in no way reflect the
reality of busy multi-user databases running on server-grade hardware, with
specific RAID configurations and so on. We ran them on an isolated SQL Server
2008 instance, installed on a virtual machine. Your figures are likely to vary,
and the observed impact will obviously be lower for faster disks. The idea is
simply to offer some insight into potential log fragmentation issues, and some
pointers on how to investigate their potential impact.
No comments:
Post a Comment