SQL Server 2008 - FileStream - Disabling 8.3 name and last access time for files

Question

I am trying to configure SQL Server 2008 filestream in my machine by following the below article :-

http://msdn.microsoft.com/en-us/library/cc949109(lightweight).aspx

The article says one should disable 8.3 names and the last access for files in windows for performance reasons before proceeding to the SQL Server changes for enabling filestream.

Question:- (If you can let me know your experience on this, It would be better)

1. What If I choose not to disable 8.3 names and the last access for files in windows? Would I still be able and work with Filestream?

2. Would there be any problem If I do the SQL Server configuration for filestream first and If I notice performance problem, I would do the windows changes?

Thanks in advance for your suggestions.

Ashish

Answer 1

1. You can still use the Filestream type if you do not disable this. In low volume situations, as in one implementation that I administer, there have been no complaints about performance. This is with only 3-6 concurrent users looking at 1-2 images every minute, or there about. There is a daily job to dump updated blobs to a web server to actually host the images (up to 150 a day after initial load). I would say that the only way to really be sure is to test with the peak load that you expect before you put the server into production.

2. According to msdn choosing to not disable the 8.3 feature may cause continued performance degradation as the change will only effect future files, so enumerating old files will be slower (not certain about that last part, but the msdn article does not specify if disabling after the fact will disregard the 8.3 names when locating files). You may also want to consider cluster size in your implementation if your blob data has predictable size patterns. If all files are between 25 and 30 KB you might want to set the cluster size at 32 just to reduce fragmentation and i/o ops (you will not want to go too big though because it will waste space on small files).

Opinion following: If you know that you can change this setting and it will only prevent future problems, I would do it that way.

Answer 2

The 8.3 performance degradation is caused by NTFS implementation detail - when new 8.3 name is generated, all files within particular folder are queried to check if given name doesn't already exist. So the more files you have in a folder, the longer it takes to generate such a name.

Paul Randal wrote a few tips regarding FILESTREAM some time ago.

Regards

Piotr