Interesting one today:
Recently, in a post, we covered about covered some ideas optimizing transactional log file by reducing or removing too many VLFs. This is an important step in optimizing Sql Server performance, especially for VLDBs.
Important Artifact 1:
There are some interesting nuances to Transactional log file architecture and its fascinating operational subtleties. As Microsoft documented it extensively, there are something called VLFs in a LDF file (transactional log file). The way each VLF is utilized in a circular -linked-list fashion in an important artifact to finding optimization approaches.
Important Artifact 2:
Adding to that, Paul Randall’s post here uncovers some nuances to Sql Server internals algorithm in which it extends LDF files. This is key to the way we configure LDF size for each database usage levels.
After reviewing the above topics and supporting artifacts, the optimal approach to configuring transactional log files (LDF) for VLDBs to achieve elimination of 1 MB AutoGrowth frequently are two ways:
- Versions before Sql Server 2014
- Sql Server 2014 and newer
Versions before Sql Server 2014
Pre-cofigure larger transactional log file size in 8 GB increments (after initial size); This results in 16 VLFs in each 8 GB growth increment, with 512 MB for each VLF.
Example:
- If you need LDF file size less than or equal to 8 GB, start with 8 GB size.
- From 8 to 16 GB, use 16 GB LDF file size.
- 16 – 24 GB, use 24 GB as initial size.
- …
- …
- …
- 72 – 80 GB, use 80 GB as initial size.
Important: It is important to start with 8 GB and keep increasing by 8 GB to larger file size, rather than just going straight up to 80 GB. It is important to perform actions in this sequence, because of the algorithm that assigns VLFs to each size increment.
See the script below for detailed understanding:
--
-- Perform increments in 8 GB (to create 512MB VLFs)
--
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 8 GB);
GO
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 16 GB);
GO
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 24 GB);
GO
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 32 GB);
GO
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 40 GB);
GO
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 48 GB);
GO
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 56 GB);
GO
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 64 GB);
GO
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 72 GB);
GO
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 80 GB);
GO
| Initial or increment |
Allocation size |
NoF VLFs |
VLF size |
Total Log size |
Total VLFs |
| Initial |
500 KB |
2 |
250 KB |
500 KB |
2 |
| Increemnt |
8 GB |
16 |
512 MB |
8 GB |
18 |
| Increment |
8 GB |
16 |
512 MB |
16 GB |
34 |
| Increment |
8 GB |
16 |
512 MB |
24 GB |
50 |
| Increment |
8 GB |
16 |
512 MB |
32 GB |
66 |
| Increment |
8 GB |
16 |
512 MB |
40 GB |
82 |
| Increment |
8 GB |
16 |
512 MB |
48 GB |
98 |
| Increment |
8 GB |
16 |
512 MB |
56 GB |
114 |
| Increment |
8 GB |
16 |
512 MB |
64 GB |
130 |
| Increment |
8 GB |
16 |
512 MB |
72 GB |
146 |
| Increment |
8 GB |
16 |
512 MB |
80 GB |
162 |
Sql Server 2014 and Newer
Starting Sql Server 2014 , the algorithm that assigns VLF for each new LDF size increment has undergone significant changes. Keeping then in mind, we need a different approach to configuring LDF size.
Note: We start with initial 8 GB; Add another 8 GB; From them on, add by 1 GB increment up to required size.
--
-- Perform increments in 8 GB (to create 512MB VLFs), then increase it by 1 GB
--
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 8 GB);
GO
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 16 GB);
GO
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 17 GB);
GO
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 18 GB);
GO
..
..
..
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 100 GB);
GO
| Initial or increment |
Allocation size |
NoF VLFs |
VLF size |
Total Log size |
Total VLFs |
| Initial |
8 GB |
16 |
512 MB |
8 GB |
16 |
| Increment |
8 GB |
16 |
512 MB |
16 GB |
32 |
| Increment |
1 GB |
1 |
1 GB |
17 GB |
33 |
| Increment |
1 GB |
1 |
1 GB |
18 GB |
34 |
| Increment |
1 GB |
1 |
1 GB |
19 GB |
35 |
| Increment |
1 GB |
1 |
1 GB |
20 GB |
36 |
| … |
… |
… |
… |
… |
… |
| … |
… |
… |
… |
… |
… |
| Increment |
1 GB |
1 |
1 GB |
100 GB |
116 |
Other better practices:
- No benefit to having multiple LDF files
- Better to have larger size VLFs than too small size (that could result in frequent small increments – not good)
- Since instant file initialization does not work for LDF files, it might take a few seconds to set up LDF to desired final size.
Read Full Post »
Playing with database servers... 