FILESTREAM Best Practices
This topic provides recommended best practices for using FILESTREAM.
Physical Configuration and Maintenance
When you set up FILESTREAM storage volumes, consider the following guidelines:
Turn off short file names on FILESTREAM computer systems. Short file names take significantly longer to create. To disable short file names, use the Windows fsutil utility.
Regularly defragment FILESTREAM computer systems.
Use 64-KB NTFS clusters. Compressed volumes must be set to 4-KB NTFS clusters.
Disable indexing on FILESTREAM volumes and set disablelastaccess To set disablelastaccess, use the Windows fsutil utility.
Disable antivirus scanning of FILESTREAM volumes when it is not unnecessary. If antivirus scanning is necessary, avoid setting policies that will automatically delete offending files.
Set up and tune the RAID level for fault tolerance and the performance that is required by an application.
RAID level |
Write performance |
Read performance |
Fault tolerance |
Remarks |
RAID 5 |
Normal |
Normal |
Excellent |
Performance is better than one disk or JBOD; and less than RAID 0 or RAID 5 with striping. |
RAID 0 |
Excellent |
Excellent |
None |
|
RAID 5 + stripping |
Excellent |
Excellent |
Excellent |
Most expensive option. |
Physical Database Design
When you design a FILESTREAM database, consider the following guidelines:
FILESTREAM columns must be accompanied by a corresponding uniqueidentifier ROWGUID column. These kinds of tables must also be accompanied by a unique index. Typically this index is not a clustered index. If the databases business logic requires a clustered index, you have to make sure that the values stored in the index are not random. Random values will cause the index to be reordered every time that a row is added or removed from the table.
For performance reasons, FILESTREAM filegroups and containers should reside on volumes other than the operating system, SQL Server database, SQL Server log, tempdb, or paging file.
Space management and policies are not directly supported by FILESTREAM. However, you can manage space and apply policies indirectly by assigning each FILESTREAM filegroup to a separate volume and using the volume's management features.
Application Design and Implementation
When you are designing and implementing applications that use FILESTREAM, consider the following guidelines:
Use NULL instead of 0x to represent a non-initialized FILESTREAM column. The 0x value causes a file to be created, and NULL does not.
Avoid insert and delete operations in tables that contain nonnull FILESTREAM columns. Insert and delete operations can modify the FILESTREAM tables that are used for garbage collection. This can cause an application's performance to decrease over time.
In applications that use replication, use NEWSEQUENTIALID() instead of NEWID(). NEWSEQUENTIALID() performs better than NEWID() for GUID generation in these applications.
The FILESTREAM API is designed for Win32 streaming access to data. Avoid using Transact-SQL to read or write FILESTREAM binary large objects (BLOBs) that are larger than 2 MB. If you must read or write BLOB data from Transact-SQL, make sure that all BLOB data is consumed before you try to open the FILESTREAM BLOB from Win32. Failure to consume all the Transact-SQL data might cause any successive FILESTREAM open or close operations to fail.
Avoid Transact-SQL statements that update, append or prepend data to the FILESTREAM BLOB. This causes the BLOB data to be spooled into the tempdb database and then back into a new physical file.
Avoid appending small BLOB updates to a FILESTREAM BLOB. Each append causes the underlying FILESTREAM files to be copied. If an application has to append small BLOBs, write the BLOBs into a varbinary(max) column, and then perform a single write operation to the FILESTREAM BLOB when the number of BLOBs reaches a predetermined limit.
Avoid retrieving the data length of lots of BLOB files in an application. This is a time-consuming operation because the size is not stored in the SQL Server Database Engine. If you must determine the length of a BLOB file, use the Transact-SQL DATALENGTH() function to determine the size of the BLOB if it is closed. DATALENGTH() does not open the BLOB file to determine its size.
If an application uses Message Block1 (SMB1) protocol, FILESTREAM BLOB data should be read in 60-KB multiples to optimize performance.