SQL SERVER – Tips working with Files inside SQL Server

When two SQL enthusiasts meet, often the conversation drifts to questioning each others understanding and implementation. Now, when two DBA’s meet it is always a great to see how the conversation goes because each carry a ton of baggage which can sometimes contradict too. One of the common rules of thumb has been to placing log and data files of different drives.

Recently, I had a friend say there is nothing much to do apart from placement of the drives. In one such conversation, I said – “Hey, it is not that simple. Isn’t there more to it as a DBA you take care, when working with your database files (data / log)?”. I got a blank look and was asked, Pinal I don’t see you have ever written anything like this over the blog. Though they are spread across over multiple blog posts, I thought this blog will aggregate some of the points I shared in the conversation.

#1 Tip

Physically isolate Data files from Log files. Combining heterogeneous workloads (workloads with very different I/O and latency requirements) can have negative effects on overall performance. Here is a KB article that talks about it in detail: Separate database and transaction log files on different drives for optimal performance and disaster recovery

#2 Tip

Secondly, it is highly recommended to place the database files and backups on separate physical devices. Otherwise, if the device that contains the database fails, your backups will be unavailable as well. Furthermore, placing the data (meaning Data and Log files) and backups on isolated devices optimizes the I/O performance for both production use of the database and writing backups.

#3 Tip

Similarly, it is strongly recommended to place the database files and FILESTREAM data on separate devices. Placing the data (meaning Data and Log files) and FILESTREAM data on isolated devices optimizes the I/O performance and provides better management of FILESTREAM data, namely regularly defragmenting such volumes. Just incase you want to learn more about FILESTREAMs, I recommend you read these older posts.

#4 Tip

Data files should be of equal size within each Filegroup, as SQL Server uses a proportional fill algorithm that favors allocations in files with more free space. You may recognize this recommendation as directed at the TempDB, but it applies to all user databases also. Read more about this from MSDN under File and Filegroup Fill Strategy section.

#5 Tip

Do not change the default file extensions (mdf, ldf and ndf) for data and log files. When talking about this, I would like you to take a look at the post: SQL SERVER – Antivirus Exclusions Best Practices With SQL Server because if any of these are changed, we need to make these exclusions accordingly. I am generally of the opinion not to disturb defaults unless we have a valid reason to do the same.

#6 Tip

Use the Instant File Initialization feature as this will allow a boost in performance, because when extending or creating a data file (does not apply to log files), it will not be zeroed right away, saving time and I/O cycles. This can be accomplished by granting the “Perform volume maintenance tasks” permission to the SQL Server account, which is set in the security policy console (secpol.msc). In this context I am reminded of a post by my good friend Tim Radney here which I attach for reference.

This permission does come with a small security risk, because by not zeroing out the existing space, when deleting data for example, there is a possibility that data could still be read, even though it has been “deleted”, until some other data writes on that specific area of the data file. However, the performance benefits outweighs the security risk and hence the reason for this recommendation.

As I wrap up, I think I have nicely tabulated some of the conversation points in this blog. I would love to hear from you on some of the recommendations you have done in your environments that could be a great addition to this blog via your comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)