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.

• Different ways to enable FILESTREAM feature of SQL Server 2008
• Creating a SQL Server 2008 FILESTREAM Enabled Database and Using INSERT, UPDATE and DELETE statements to manage FILESTREAM Data
• How to Backup and Restore a SQL Server FILESTREAM Enabled Database

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:

• Check out the previous tips on FILESTREAM databases:
  • Creating a SQL Server 2008 FILESTREAM Enabled Database and Using INSERT, UPDATE and DELETE statements to manage FILESTREAM Data
  • How to Backup and Restore a SQL Server FILESTREAM Enabled Database
  • Different ways to enable FILESTREAM feature of SQL Server 2008
  • Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server 2008
• If you do not already have SQL Server 2008, download the SQL Server 2008 Enterprise 180 Day evaluation from this site.

• How to Detach and Attach a SQL Server FILESTREAM Enabled Database
• Creating a SQL Server 2008 FILESTREAM Enabled Database and Using INSERT, UPDATE and DELETE statements to manage FILESTREAM Data
• Different ways to enable FILESTREAM feature of SQL Server 2008
• Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server 2008
• More...

• Building a Data Mart with Integration Services

• Discuss this tip, post comments and feedback