SQL Server 2008 FILESTREAM性能 [英] SQL Server 2008 FILESTREAM performance
问题描述
我对SQL Server 2008的FILESTREAM功能有一些疑问.
I had some questions around the FILESTREAM capability of SQL Server 2008.
-
使用FILESTREAM功能返回从SQL Server 2008流式传输的文件与直接从共享目录访问文件的性能有何不同?
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?
如果100个用户在10秒的时间内请求了100个100Mb文件(通过FILESTREAM存储)，SQL Server 2008的性能是否会降低爬网速度?
If 100 users requested 100 100Mb files (stored via FILESTREAM) within a 10 second window, would SQL Server 2008 performance slow to a crawl?
推荐答案
如果100个用户在10秒的时间内请求了100个100Mb文件(通过FILESTREAM存储)，SQL Server 2008的性能是否会降低爬网速度? em>
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.......
Paul Randal在关于FILESTREAM的25页白皮书-也涵盖了其中的一些内容.性能调整技巧.
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.
但也请查看Microsoft Research TechReport 要BLOB还是不要BLOB .
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.
他们的结论:
研究表明，如果对象 大于1兆字节 平均，NTFS具有明显的优势 通过SQL Server.如果对象是 小于256 KB的数据库具有 明显的优势.在这个范围内 这取决于写的强度 工作量和存储期限 系统中的典型副本.
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.
因此，从这一点来看-如果您的Blob通常小于1 MB，只需将其作为VARBINARY(MAX)存储在数据库中.如果它们通常更大，则只有FILESTREAM功能.
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.
相对于NTFS文件夹中非托管"存储，FILESTREAM的性能而不是它的其他优点，我不必太担心:在没有FILESTREAM的情况下将文件存储在数据库外部，您将无法控制它们:
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:
- 数据库未提供访问控制
- 文件不是SQL Server备份的一部分
- 文件不是事务处理的，例如您可能会得到不再从数据库中引用的僵尸"文件，或者最终在数据库中没有磁盘上相应文件的骨架"条目
仅凭这些功能，绝对值得使用FILESTREAM.
Those features alone make it absolutely worthwhile to use FILESTREAM.
这篇关于SQL Server 2008 FILESTREAM性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
