September 24, 2008 at 9:11 am
I'm excited about the new FILESTREAM object, but it has a hidden flaw that must be discussed! If you start loading 100,000+ files into a table, the performance for inserting rows will continue to slow down to the point where it is crawling (several seconds for a single insert!)
This problem occurs because NTFS is continuing to create 8.3 file names for each file that is created in the FILESTREAM directory.
This is not a SQL command, or configuration issue - but an NTFS known problem that was not addressed when SQL creates the directory.
You can stop NTFS from creating 8.3 filesname, and therefore restore performance!
Here is the command to be issued in the diretory: fsutil behavior set disable8dot3
More information here: http://technet.microsoft.com/en-us/library/bb457112.aspx - scroll down to Optimizing NTFS Performance. - credit to channel9!
September 25, 2008 at 10:56 am
Thanks for the great tip
Something to keep in mind for future FILESTREAM implementation
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply