performancesql-server-2008filestreamsqlfilestream

SQL Server 2008 FILESTREAM performance

I had some questions around the FILESTREAM capability of SQL Server 2008.

  1. What would the difference in performance be of returning a file streamed from SQL Server 2008 using the FILESTREAM capability vs. directly accessing the file from a shared directory?

  2. If 100 users requested 100 100Mb files (stored via FILESTREAM) within a 10 second window, would SQL Server 2008 performance slow to a crawl?

Solution

  • If 100 users requested 100 100Mb files (stored via FILESTREAM) within a 10 second window, would SQL Server 2008 performance slow to a crawl?

    On what kind of a server?? What kind of hardware to serve those files? What kind of disks, network etc.?? So many questions.......

    There's a really good blog post by Paul Randal on SQL Server 2008: FILESTREAM Performance - check it out. There's also a 25-page whitepaper on FILESTREAM available - also covering some performance tuning tips.

    But also check out the Microsoft Research TechReport To BLOB or Not To BLOB.

    It's a very profound and very well based article that put all those questions through their paces.

    Their conclusion:

    The study indicates that if objects are larger than one megabyte on average, NTFS has a clear advantage over SQL Server. If the objects are under 256 kilobytes, the database has a clear advantage. Inside this range, it depends on how write intensive the workload is, and the storage age of a typical replica in the system.

    So judging from that - if your blobs are typically less than 1 MB, just store them as a VARBINARY(MAX) in the database. If they're typically larger, then just the FILESTREAM feature.

    I wouldn't worry so much about performance rather than other benefits of FILESTREAM over "unmanaged" storage in a NTFS file folder: storing files outside the database without FILESTREAM, you have no control over them:

    Those features alone make it absolutely worthwhile to use FILESTREAM.