Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Best Practices When Using FILESTREAM Feature of SQL Server


By:   |   Last Updated: 2009-11-06   |   Comments (2)   |   Related Tips: More > FILESTREAM

Problem

In SQL Server 2008 one can store BLOBs (e.g. Images, video, Word, Excel, PDF, MP3, etc) in the NTFS file system rather than in a database file. This can be done by using the new FILESTREAM feature which was introduced in SQL Server 2008. In this tip we will take a look at some of the best practices which a database administrator can follow to get the best performance when using the FILESTREAM feature of SQL Server 2008.

Solution

If you are new to SQL Server 2008 and haven't used the FILESTREAM feature yet, then I would suggest you start with the following tips.

FILESTREAM Best Practices

Some of the best practices which can be leveraged by database administrators when using the FILESTREAM feature of SQL Server 2008 are listed below:

  1. Always create the FILESTREAM data container which will be used by the FILESTREAM enabled database in a separate volume.
  2. To get better performance, you should not be storing any Operating System Files, SQL Server Database Files, SQL Server Log Files, TempDB Files and Pagefiles etc on the same volume where FILESTREAM filegroups / FILESTREAM data container files are stored to avoid contention for the disk heads.
  3. Use RAID 5 + stripping for FILESTREAM data container as it provides excellent read, write and fault tolerance. Even though this is an expensive configuration.
  4. It is a good practice to periodically perform disk defragmentation of the volumes which store FILESTREAM data.
  5. Disable indexing on FILESTREAM volumes.
  6. Disable generation of 8.3 names on all NTFS volumes used for FILESTREAM data storage by running the below command at a command prompt.

    FSUTIL BEHAVIOR SET DISABLE8DOT3 1
  7. Disable last file access time tracking in NTFS volumes used for FILESTREAM data storage by running the below command at a command prompt.

    FSUTIL BEHAVIOR SET DISABLELASTACCESS 1
  8. Avoid Transact SQL statements that will update or append data to the FILESTREAM BLOBs. This action basically causes the FILESTREAM data to be loaded into the TempDB database and then loads the data back into a new physical file.
  9. TempDB database should be configured to use multiple data files and all the files should be configured to a suitable size as per your usage to improve performance.
  10. Avoid multiple smaller appends to the FILESTREAM files. As each append results in copying the underlining FILESTREAM file every time thereby slowing down performance. Instead write the BLOB into a VARBINARY(MAX) column and then perform a single write operation.
  11. If you are planning to use SQL Server Replication, then you should be using NEWSEQUENTIALID() instead of NEWID(). As NEWSEQUENTIALID() performs better than NEWID().
  12. Disable antivirus software from scanning FILESTREAM volumes if it's not mandatory within your organization. If antivirus scanning is mandatory, then set the policies that will not automatically delete offending files. If the files are deleted it will corrupt your FILESTREAM enabled database.
  13. It is advisable to store BLOB data which is greater than 1 MB as FILESTREAM data for better performance. If the BLOB data size is smaller than 1 MB, then you will see better performance when it is stored within a database.
  14. To reduce disk fragmentation, ensure disk volumes which will be used to store FILESTREAM data are formatted with a 64K allocation, the default NTFS allocation is 4K.
  15. Disable Auto Shrink and Auto Close options on FILESTREAM enabled databases to avoid disk fragmentation and poor performance.
  16. Plan before using the FILESTREAM feature as it is not support if you are planning to use Database Mirroring as a high availability option.
Next Steps


Last Updated: 2009-11-06


next webcast button


next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, November 10, 2009 - 11:13:29 PM - Ashish Kumar Mehta Back To Top

These recommendations are applicable when FILESTREAM Containers are created on SAN Drives as well as on internal drives. Having multiple files for TempDB database helps in better performance of the SQL Server overall thereby improving FileStreamDB's performance too. Its is advisable to move TempDB to a different drive and have multiple files to improve performance.


Monday, November 09, 2009 - 11:40:55 AM - DavidB Back To Top
Nice set of tips. Would some of the recommendations be different on a SAN Drive as oppose to a internal drives. An explanation of the benefits for each tip would be helpful. I have heard of the TEMPDB multiple file recommendation before but is there any additional reason for using it with FILESTREAM? I have tested the TEMPDB multiple file recommendation before on SQL Server 2005 but have not seen any performance improvement. Instead, I would get a slightly worse run times on queries. Thanks for the list, it gives me many things to consider.

Learn more about SQL Server tools