SQL Server 2008 FILESTREAM performance

performance sql-server-2008 filestream sqlfilestream
John Livermore picture John Livermore · Dec 29, 2009 · Viewed 11.7k times · Source

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?

Answer

marc_s picture marc_s · Dec 29, 2009

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:

  • no access control provided by the database
  • the files aren't part of your SQL Server backup
  • the files aren't handled transactionally, e.g. you could end up with "zombie" files which aren't referenced from the database anymore, or "skeleton" entries in the database without the corresponding file on disk

Those features alone make it absolutely worthwhile to use FILESTREAM.

Related questions

SQL Server SELECT LAST N Rows

This is a known question but the best solution I've found is something like: SELECT TOP N * FROM MyTable ORDER BY Id DESC I've a table with lots of rows. It is not a posibility to use that query because …

Read More
Which are more performant, CTE or temporary tables?

Which are more performant, CTE or Temporary Tables?

Read More
SQL Joins Vs SQL Subqueries (Performance)?

I wish to know if I have a join query something like this - Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id and a subquery something like this - Select E.Id,…

Read More