SQL Server 2008 File-related Optimizations

While attending training today, I learned some file-related optimizations for SQL Server 2008. Some of those will also apply to previous versions of SQL Server.

1) No performance gain in multiple log files

There is a common misconception that having multiple log files per database would help performance. That is not true. Even if you add another log file, SQL will still have to write sequentially to only one of the log files. Adding another log file makes sense if your first log file is on a disk that is full.

2) Auto-Shrink - Keep it off!!!

Auto shrink for a database is off by default and should be kept that way. Shrinking is known to cause high fragmentation, which will affect your performance. Unless you have no other option (low disk space), you should not use shrink and you should definitely not enable auto-shrink.

Details at:
https://www.sqlskills.com/blogs/paul/post/Auto-shrink-e28093-turn-it-OFF!.aspx

3) Instant Initialization - From minutes to seconds

By default, creating (and restoring) a database requires initializing the database files, which can take a while. For instance, creating a 20GB can easily take minutes. The initialization is done by SQL (not by NTFS).

However, you have an option to skip this initialization step called "instant initialization", which will bring that time down from minutes to seconds. It requires granting the correct rights to SQL (it needs the SE_MANAGE_VOLUME_NAME permission, also known as Perform Volume Maintenance Tasks). This is not done by default. After changing this setting, you need to restart SQL Server to make it effective.

The log files, regardless of this setting, are always initialized (when you create or grow) by writing zeros to it, since it's required by the way the logging mechanism works.

Details at:
https://www.sqlskills.com/blogs/Kimberly/post/Instant-Initialization-What-Why-and-How.aspx
https://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-instant-file-initialization.aspx

4) Keep your virtual log files at an appropriate size

SQL Server log files are logically broken into sections called "Virtual Log Files" or VLFs. The size of your VLFs will depend on how big a log file your create (or by how much you grow it). You can see how many VLFs you have and how big they are using the DBCC LOGINFO command.

Having too many VLFs is not good (more processing required to manage data about each VLF). Having very large VLF is also not desirable (older VLFs less likely to clear and growing the log takes more time, since it needs to initialize the new VLF with zeros).

Details at:
https://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
https://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx