Recommendations and guidelines for improving SQL Server FileStream performance

Article translations Article translations
Article ID: 2160002 - View products that this article applies to.
Expand all | Collapse all

On This Page

SUMMARY

The SQL Server Filestream feature allow you to store varbinary(max) binary large object data as files in the file system. When you have a large number of rows in filestream enabled tables, you can end up with a file system volume that contains large number of files. To achive best performance when processing the integrated data from the database as well as the file system, it is important to ensure the file system is tuned optimally. The following are some of the tuning options that are available from a file system perspective:
  • Altitude check for the SQL Server filestream filter driver [e.g. rsfx0100.sys]. Evaluate all the filter drivers loaded for the storage stack associated with a volume where the filestream feature stores files and make sure that rsfx driver is located at the bottom of the stack. You can use the FLTMC.EXE control program to enumerate the filter drivers for a specific volume. Here is a sample output from the FLTMC utility: C:\Windows\System32>fltMC.exe filters
    Collapse this tableExpand this table
    Filter NameNum InstancesAltitudeFrame
    Sftredir14060000
    MpFilter93280000
    luafv11350000
    FileInfo9450000
    RsFx0103141001.030
  • Check that the server has the "last access time" property disabled for the files. This file system attribute is maintained in the registry:
    Key Name: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
    Name: NtfsDisableLastAccessUpdate
    Type: REG_DWORD
    Value: 1
  • Check that the server has 8.3 naming disabled. This file system attribute is maintained in the registry:
    Key Name: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
    Name: NtfsDisable8dot3NameCreation
    Type: REG_DWORD
    Value: 1
  • Check if filestream directory containers do not have more than 300,000 files. You can use the information from sys.database_files catalog view to find out which directories in the file system store filestream related files.
  • Check that the filestream directory containers do not have file system Encryption or file system Compression enabled as these can introduce a level of overhead when accessing these files.

MORE INFORMATION

The filestream feature overview
Information about fltmc.exe control program
NtfsDisableLastAccessUpdate
NtfsDisable8dot3NameCreation

Recommendations and guidelines for improving SQL Server FileStream performance

The following table provides more information about the products or tools that automatically check for this condition in your instance of SQL Server and in the versions of SQL Server against which the rule is evaluated.

Collapse this tableExpand this table
Rule SoftwareRule TitleRule DescriptionProduct versions against which the rule is evaluated
System Center AdvisorSQL Server filestream feature can benefit from filesystem settings changeSystem Center Advisor determines whether the SQL Server instance is using the FileStream feature and whether the two related registry key settings are set up as described in this article. Review the information that is provided in the “Information Collected” section of the advisor alert, and make the required changes to the registry keys.SQL Server 2008
SQL Server 2008 R2
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)

File System needs tuning for better FileStream performance


TheSQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect situations in which the file system is not configured optimally for better Filestream performance. If you run the BPA tool, and you encounter a warning that is titled "Engine - File System needs tuning for better FileStream performance," you must follow the recommendations that are discussed in this article to improve the file system performance.SQL Server 2008
SQL Server 2008 R2
SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)

File System needs tuning for better FileStream performance


The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect situations in which the file system is not configured optimally for better Filestream performance. If you run the BPA tool, and you encounter a warning that is titled "Engine - File System needs tuning for better FileStream performance," you must follow the recommendations that are discussed in this article to improve the file system performance.SQL Server 2012





Properties

Article ID: 2160002 - Last Review: April 2, 2012 - Revision: 5.0
APPLIES TO
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Enterprise
Keywords: 
KB2160002

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com