Recommendations and guidelines for improving SQL Server FILESTREAM performance

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 containers, which are the underlying storage for both FILESTREAM columns and FileTables, you can end up with a file system volume that contains large number of files. To achieve 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
    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 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.
  • From an elevated command prompt, run fltmc instances and make sure that no filter drivers are attached to the volume where you try to restore.
  • Check that 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. This can be prevented by having multiple containers. (See the next bullet item for more information.)
  • With only one FILESTREAM filegroup, all data files are created under the same folder. File creation of very large numbers of files may be impacted by large NTFS indices, which can also become fragmented.
    • Having multiple filegroups generally should help with this (the application uses partitioning or has multiple tables, each going to its own filegroup).
    • With SQL Server 2012 and later versions, you can have multiple containers or files under a FILESTREAM filegroup, and a round-robin allocation scheme will apply. Therefore the number of NTFS files per directory will get smaller.
  • Backup and restore can become faster with multiple FILESTREAM containers, if multiple volumes storing containers are used.
    • SQL Server 2012 supports multiple containers per filegroup and can make things much easier. No complicated partitioning schemes may be needed to manage larger number of files.
  • The NTFS MFT may become fragmented, and that can cause performance issues. The MFT reserved size does depend on volume size, so you may or may not encounter this.
    • You can check the MFT fragmentation with defrag /A /V C: (change C: to the actual volume name).
    • You can reserve more MFT space by using fsutil behavior set mftzone 2.
    • FILESTREAM data files should be excluded from antivirus software scanning.
More information
The FILESTREAM feature overview
sp_filestream_force_garbage_collection (Transact-SQL)
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.

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


The SQL 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





خصائص

رقم الموضوع: 2160002 - آخر مراجعة: 08/27/2016 13:30:00 - المراجعة: 6.0

Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 R2 Enterprise

  • KB2160002
تعليقات