SQL Server 2005和文件系统，任何建议 [英] SQL Server 2005 and file systems, any recommendations
问题描述
我们计划将我们的数据库迁移到新的和更多的磁盘驱动器，更快的速度
RAID级别和更多的专用磁盘使用（例如，将转换日志放在
专用磁盘）。数据库服务器在Win2003上运行。
现在我们正在考虑在新的
驱动器上使用什么文件系统。我们选择性能，但也期望可靠性。（不管怎么说，恕我直言，恕我直言;-)）
>从我的内容可以说，NTFS是一个日志文件系统，至少它是
做一些日记。我宁愿没有日记和牺牲
的性能启动时间，并且想知道它是否可能
在NTFS中关闭日记或至少移动将期刊分成一个单独的磁盘。这是可行的，有没有人这样做，是否有任何
福利？有什么缺点吗？
当服务器和
磁盘专用于SQL Server时，是否有任何通常建议的NTFS调整？好像
NTFSDisableLastAccessUpdate。
如何替代NTFS？ FAT32是否可行或者我们需要看看Veritas或其他人吗？
TIA所有评论。
Bj？Augestad
bj ************ @ gmail.com （bj ********** **@gmail.com）写道：
我们计划将数据库迁移到新的和更多的磁盘驱动器，更快
RAID级别和更多专用磁盘使用（例如将translog放在
专用磁盘上）。数据库服务器在Win2003上运行。
现在我们正在考虑在新的
驱动器上使用什么文件系统。我们选择性能，但也期望可靠性。（Go /
不说，恕我直言;-)）
>>来自我能说的是，NTFS是一个日志文件系统，至少它是
做一些日记。我宁愿没有日记和牺牲
的性能启动时间，并且想知道它是否可能
在NTFS中关闭日记或至少移动将期刊分成一个单独的磁盘。这是可行的，有没有人这样做，是否有任何
福利？有什么缺点吗？
当服务器和
磁盘专用于SQL Server时，是否有任何通常建议的NTFS调整？好像
NTFSDisableLastAccessUpdate。
如何替代NTFS？ FAT32是否可行或者我们需要看看Veritas或其他人吗？
不，FAT32不是一个很好的选择。
SQL Server中有几个功能不适用于FAT32。我从一个SQL Server开发人员的b $ b获得了这个列表：
1.数据库快照 - 需要支持可用的稀疏文件
仅在NTFS下
2. DBCC快照使用 - 需要支持备用流，这是仅在NTFS下可用的
3.挂载集群下的点数
4.使用NTFS压缩的数据文件压缩
5.仅在NTFS下支持数据库文件的加密
6.差异备份，恢复全文目录文件仅支持
在NTFS下
他说列表可能不完整。他还补充说：我相信恢复
机制在NTFS下更加强大，可以防止系统故障。
我觉得用户担心NTFS太多了其他
明显的性能瓶颈。
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server 2005联机丛书
http://www.microsoft.com/technet/pro ... ads / books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Erland Sommarskog写道：
bj *** ********* @ gmail的。 com （bj************@gmail.com）写道：
我们计划迁移我们的db到新的和更多的磁盘驱动器，更快
RAID级别和更多的专用磁盘使用（例如将translog放在
专用磁盘上）。数据库服务器在Win2003上运行。
现在我们正在考虑在新的
驱动器上使用什么文件系统。我们选择性能，但也期望可靠性。（不管怎么说，恕我直言，恕我直言;-)）
>从我的内容可以说，NTFS是一个日志文件系统，至少它是
做一些日记。我宁愿没有日记和牺牲
的性能启动时间，并且想知道它是否可能
在NTFS中关闭日记或至少移动将期刊分成一个单独的磁盘。这是可行的，有没有人这样做，是否有任何
福利？有什么缺点吗？
当服务器和
磁盘专用于SQL Server时，是否有任何通常建议的NTFS调整？好像
NTFSDisableLastAccessUpdate。
如何替代NTFS？ FAT32是否可行或者我们需要看看Veritas或其他人吗？
不，FAT32不是一个很好的选择。
SQL Server中有几个功能不适用于FAT32。我从一个SQL Server开发人员的b $ b获得了这个列表：
1.数据库快照 - 需要支持可用的稀疏文件
仅在NTFS下
2. DBCC快照使用 - 需要支持备用流，这是仅在NTFS下可用的
3.挂载集群下的点数
4.使用NTFS压缩的数据文件压缩
5.仅在NTFS下支持数据库文件的加密
6.差异备份，恢复全文目录文件仅支持
在NTFS下
他说列表可能不完整。他还补充说：我相信恢复
机制在NTFS下更加强大，可以防止系统故障。
我觉得用户担心NTFS太多了其他
明显的性能瓶颈。
我得到你的观点:-)所以FAT32出局了，不是什么大惊喜。
从前我正在玩文件系统（JFS，XFS和
其他），Oracle和AIX以及OSF / 1。我花了相当长的时间
测量不同配置和日志文件的性能
systems * with *与db数据（或translog）在同一磁盘上的日志
与其他配置相比，
非常慢。我以为是
是由于磁盘头在日志之间来回移动和
文件。
所以甚至如果文件系统现在不是性能瓶颈，
我希望从第一天起最佳配置所有新磁盘驱动器。
我读了NTFS规范和浏览所有选项，但无法b / b
找到有关调整期刊的任何内容。我将不得不重新阅读它，我猜b $ b猜猜...
谢谢，
Bj？rn
>我得到你的观点:-)所以FAT32已经出局了，不是什么大惊喜。
>
曾几何时我都在玩文件系统（JFS，XFS和其他人），Oracle和AIX作为OSF / 1。我花了相当长的时间来测量不同配置和日志文件系统*的性能*与其他配置相比，与db数据（或translog）相同的磁盘上的日志非常慢。我认为这是因为磁盘头在日志和文件之间来回移动。
所以即使文件系统不是性能瓶颈也是如此现在，我希望从第一天开始就优化配置所有新磁盘驱动器。
我阅读了NTFS规范并浏览了所有选项，但无法找到任何内容关于调整期刊。我将不得不重新阅读它，我猜测...
可以选择原始分区。一位MVP告诉我，他在几年前做了一些测试（所以它不在SQL 2005上），并且使用原始分区获得了20％的改进。但原始分区很难管理，我想你仍然需要使用NTFS来支持数据库快照。就个人而言，我永远不会考虑在生产系统中使用原始的
分区。
20％可能听起来很重要，但还有其他方法可以获得20％的加速，
，例如坚持使用二进制排序规则，或者使用SQL
对varchar数据进行排序。事实上，对于像这样的查询。
SELECT ... FROM tbl WHER col LIKE''％Whatever％''
区别在CI / CS校对和二进制校对之间可以是
因子七。
通过编写错误的查询，你的性能也会下降90％。 />
您可能对这篇文章感兴趣：
http://www.microsoft.com/technet/pro...hysdbstor.mspx 。
-
斯德哥尔摩Erland Sommarskog， es****@sommarskog.se
We''re planning to migrate our db to new and more disk drives, faster
RAID levels and more dedicated disk usage(e.g. placing the translog on
dedicated disks). The db server runs on Win2003.
Right now we''re thinking about what file system to use on the new
drives. We opt for performance, but expect reliability as well.(Goes
without saying, IMHO ;-))
>From what I can tell, NTFS is a journaling file system, at least it
does some journaling. I''d prefer to have no journaling and sacrifice
boot time for performance, and was wondering if it either is possible
to turn journaling off in NTFS or at least move the journal to a
separate disk. Is it doable, has anyone done this and are there any
benefits? Any downside?
Are there any commonly recommended tweaks to NTFS when the server and
disk is dedicated to SQL Server? Stuff like
NTFSDisableLastAccessUpdate.
How about alternatives to to NTFS? Is FAT32 viable or do we need to
look at Veritas or others?
TIA for all comments.
Bj?rn Augestad
bj************@gmail.com (bj************@gmail.com) writes:We''re planning to migrate our db to new and more disk drives, faster
RAID levels and more dedicated disk usage(e.g. placing the translog on
dedicated disks). The db server runs on Win2003.
Right now we''re thinking about what file system to use on the new
drives. We opt for performance, but expect reliability as well.(Goes
without saying, IMHO ;-))
>>From what I can tell, NTFS is a journaling file system, at least itdoes some journaling. I''d prefer to have no journaling and sacrifice
boot time for performance, and was wondering if it either is possible
to turn journaling off in NTFS or at least move the journal to a
separate disk. Is it doable, has anyone done this and are there any
benefits? Any downside?
Are there any commonly recommended tweaks to NTFS when the server and
disk is dedicated to SQL Server? Stuff like
NTFSDisableLastAccessUpdate.
How about alternatives to to NTFS? Is FAT32 viable or do we need to
look at Veritas or others?No, FAT32 is not a very good alternative. There are several features in
SQL Server that are not available with FAT32. I got this list from one
of the SQL Server developers:
1. Database snapshots - require support for sparse files which is available
only under NTFS
2. DBCC snapshot usage - requires support for alternate streams which is
available only under NTFS
3. Mount points under cluster
4. Data file compression which uses NTFS compression
5. Encryption of database files is supported only under NTFS
6. Differential backup, restore of full-text catalog files is supported only
under NTFS
He says the list may not be complete. He also added "I believe that recovery
mechanisms are more robust under NTFS and protects against system failures.
I think the user is worrying too much about NTFS when there are other
obvious performance bottlenecks."
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Erland Sommarskog wrote:bj************@gmail.com (bj************@gmail.com) writes:We''re planning to migrate our db to new and more disk drives, faster
RAID levels and more dedicated disk usage(e.g. placing the translog on
dedicated disks). The db server runs on Win2003.
Right now we''re thinking about what file system to use on the new
drives. We opt for performance, but expect reliability as well.(Goes
without saying, IMHO ;-))
>From what I can tell, NTFS is a journaling file system, at least itdoes some journaling. I''d prefer to have no journaling and sacrifice
boot time for performance, and was wondering if it either is possible
to turn journaling off in NTFS or at least move the journal to a
separate disk. Is it doable, has anyone done this and are there any
benefits? Any downside?
Are there any commonly recommended tweaks to NTFS when the server and
disk is dedicated to SQL Server? Stuff like
NTFSDisableLastAccessUpdate.
How about alternatives to to NTFS? Is FAT32 viable or do we need to
look at Veritas or others?
No, FAT32 is not a very good alternative. There are several features in
SQL Server that are not available with FAT32. I got this list from one
of the SQL Server developers:
1. Database snapshots - require support for sparse files which is available
only under NTFS
2. DBCC snapshot usage - requires support for alternate streams which is
available only under NTFS
3. Mount points under cluster
4. Data file compression which uses NTFS compression
5. Encryption of database files is supported only under NTFS
6. Differential backup, restore of full-text catalog files is supported only
under NTFS
He says the list may not be complete. He also added "I believe that recovery
mechanisms are more robust under NTFS and protects against system failures.
I think the user is worrying too much about NTFS when there are other
obvious performance bottlenecks."I get your point(s) :-) So FAT32 is out, not a big surprise.
Once upon a time I was playing around with file systems(JFS, XFS and
others), Oracle and AIX as well as OSF/1. I spent a considerable time
measuring performance for different configurations and journaling file
systems *with* the journal on the same disk as the db data(or translog)
was awfully slow compared to other configurations. I assumed that is
was due to disk head movements back and forth between the journal and
the file.
So even if the file system isn''t the performance bottleneck right now,
I''d prefer to configure all the new disk drives optimally from day one.
I read the NTFS spec and browsed through all the options, but couldn''t
find anything about tuning the journal. I''ll have to reread it, I
guess...
Thanks,
Bj?rn
>I get your point(s) :-) So FAT32 is out, not a big surprise.>
Once upon a time I was playing around with file systems(JFS, XFS and
others), Oracle and AIX as well as OSF/1. I spent a considerable time
measuring performance for different configurations and journaling file
systems *with* the journal on the same disk as the db data(or translog)
was awfully slow compared to other configurations. I assumed that is
was due to disk head movements back and forth between the journal and
the file.
So even if the file system isn''t the performance bottleneck right now,
I''d prefer to configure all the new disk drives optimally from day one.
I read the NTFS spec and browsed through all the options, but couldn''t
find anything about tuning the journal. I''ll have to reread it, I
guess...There is the option of raw partition. A fellow MVP told me that he
made some tests some years back (so it was not on SQL 2005), and got
a 20% improvement with raw partition. But raw partitions are difficult
to manage, and I guess that you would still have to use NTFS for things
like database snapshots. Personally, I would never consider using raw
partition for a production system.
20% may sound significant, but there other ways to get a 20% speedup,
for instance by sticking to a binary collation, or using an SQL
collation for varchar data. In fact, for a query like.
SELECT ... FROM tbl WHER col LIKE ''%Whatever%''
the difference between a CI/CS collation and a binary collation can be
factor seven.
You also lose 90% in performance by writing a bad query.
You may be interested in this article:
http://www.microsoft.com/technet/pro...hysdbstor.mspx.
--
Erland Sommarskog, Stockholm, es****@sommarskog.se
这篇关于SQL Server 2005和文件系统，任何建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
