撕裂页面检测和自动缩小：性能？ [英] torn page detection and auto shrink: performance?
问题描述
各位大家好，
第一：SQL Server 2000 sp3a，HP集群服务器，MS 2003服务器，
数据库恢复模型简单
撕裂页面检测：
当我打开此选项时，在数据库中写入更新和
写入的进程比如果我有这个选项
关闭。
它接触到我，当打开此选项时，sqlserver是
任何与写作相关的操作，
要慢得多，而不是没有
这个选项。
这个选项是保证所必需的，数据是写好
在媒体上正确。
我的问题是：
如果我的数据库在集群下运行，带有RAID高清系统，
包含写入缓存，附加检查，数据将正确写入，是否需要保持此选项的转换
on ？
自动收缩：
当它打开时，它会影响性能吗？
Personaly，我不这么认为，但是有人在猜它，而且我是
希望得到独立消息来源的相关信息。
祝你好运
Matik
Hello everybody,
First: SQL Server 2000 sp3a, HP cluster server, MS 2003 server,
database recovery model simple
Torn page detection:
When I have this option turned on, processes conected with updating and
writing in database, was MUCH more slower, than if I''ve this option
turned off.
It seams to me, that when this option is turned on, the sqlserver is
much more slower by any operation connected with writing, than without
this option.
This option is required for ensurance, that the data are written
properly on the media.
My question is:
if I have my database running under cluster, with RAID hd system, which
contains write cache, with additional checking, that the data will be
written correctly, is it necessary for me to keep this option turned
on?
Auto shrink:
When it is turned on, does it affect performance?
Personaly, I don''t think so, but somebody is insysting on it, and I
would like to have an information from independent source about that.
Best regards
Matik
推荐答案
Matik（magate@sauron.xo.pl）写道：
Matik (ma****@sauron.xo.pl) writes:
撕裂页面检测：
我打开了这个选项，进行了更新和在数据库中写入的进程，比我关闭这个选项要慢得多。
它接缝对我来说，当打开这个选项时，任何与写入相关的操作都比没有
这个选项的sqlserver慢得多。
这个选项是必需的保证，数据在媒体上正确写好。
我的问题是：
如果我的数据库在集群下运行，有RAID高清系统，那就是
ntains写入缓存，通过额外的检查，数据将被正确写入，是否有必要让这个选项保持打开状态？
我的直觉是，撕页检测应该是这样一个性能值，但这只是一种感觉。我从来没玩过它，所以我已经向我的MVP伙伴发了一个问题，看看是否有什么可以补充的。
自动缩小：
当它打开时，它是否会影响性能？
有点，我不这么认为，但是有人在猜它，我想
从独立来源获得有关该信息的信息。
Torn page detection:
When I have this option turned on, processes conected with updating and
writing in database, was MUCH more slower, than if I''ve this option
turned off.
It seams to me, that when this option is turned on, the sqlserver is
much more slower by any operation connected with writing, than without
this option.
This option is required for ensurance, that the data are written
properly on the media.
My question is:
if I have my database running under cluster, with RAID hd system, which
contains write cache, with additional checking, that the data will be
written correctly, is it necessary for me to keep this option turned
on?
My gut feeling is that torn-page detection should be such a
performance hog, but that is just a feeling. I have never played
with it, so I''ve posted a question to my MVP mates, to see if thety
have anything to add.
Auto shrink:
When it is turned on, does it affect performance?
Personaly, I don''t think so, but somebody is insysting on it, and I
would like to have an information from independent source about that.
几乎没有任何理由可以自动收缩。是的，它会影响性能。
会影响性能。萎缩需要机器的电源，而b
可能会产生影响。但不仅如此，缩小的东西可能会再次增长，并且自动增长可能会很痛苦。
通常，SQL Server中的数据库会动态增长，对于一个好的
调整系统，最好提前计划，并在
maintenenace窗口期间扩展数据库。在生产时间内自动增长应该是b
。
-
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
There are rarely any reasons to have auto-shrink on. And yes, it
affects performance. The shrinking takes power of the machine, and
may incur looking. But not only that, things that have shrunk may
grow again, and auto-grow can be painful.
Generally, while databases in SQL Server grow dynamically, for a well-
tuned system it''s best to plan ahead, and grow the database during
maintenenace windows. Auto-grow during production hours should be
avoided.
--
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（es****@sommarskog.se）写道：
Erland Sommarskog (es****@sommarskog.se) writes:
我的直觉是，撕页检测应该是这样的表现，但这只是一种感觉。我从来没玩过它，所以我已经向我的MVP队友发了一个问题，看看是否有什么要补充的。
My gut feeling is that torn-page detection should be such a
performance hog, but that is just a feeling. I have never played
with it, so I''ve posted a question to my MVP mates, to see if thety
have anything to add.
我得到了一些答案。一位MS开发人员告诉我，更新不应该受到影响，因为他们不会查看校验和。
Checkpoint / Lazy编写器进程会受到影响，但用户线程
不应该注意到这一点。我自己的反应是，如果你看到因页面检测破坏而导致
退化，它可能是导致你的备份占用机器的同一个问题。
另一个MVP告诉我这个关于撕裂的页面：
我认为应该提到的一件事，无论
性能讨论是他/她不需要认为，因为
他们有写缓存和Raid，他们可以安全地撕掉页面。
我有一个系统有一个带缓存的raid后端和各种各样的b&b冗余"内置于它中并且需要一个管理员在san
开关上移动电缆以弄乱并导致页面撕裂。我很感谢
检测，因为我没有丢失整个数据库或导致更多问题和
能够修复它。因此，可能很高兴提及仅仅因为你拥有硬件并不意味着你总是安全的。 :)
-
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
I got some answers. A MS developer told me that updates should not be
affected at all, as they don''t look at the checksums then. The
Checkpoint/Lazy writer processes would be affected, but user threads
should not notice this. My own reaction is that if you are seeing
degradation with torn-page detection, it may be a token of the same
problem that causes your backups to hog the machine.
Another MVP told me this about torn pages:
One thing I think that should be mentioned regardless of the
performance discussion is that he/she needs to not think that since
they have write cache and a Raid that they are safe from torn page''s.
I had a system that had a raid backend with cache and all sorts of
"redundancy" built into it and it took one admin moving cables on the san
switch to mess things up and cause torn pages. I''m thankful for the
detection since I didn''t lose the whole database or cause more issues and
was able to fix it. So it might be good to mention that just because you
have Hardware doesn''t mean your always safe. :)
--
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
Thnks Erland！一如既往的好建议。
我认为现在我有一些想法，那里发生了什么:(
我真的很扭曲那一点点，那里发生了什么。
你已经提到，备份不应该影响性能......但是
它可能会影响IO操作（关于备份的帖子）。问题是，
我正在运行的系统进行大量的插入和更新操作。
关于这一点，如果会有一个备份执行，这已经
影响磁盘写入程序，另外，这个进程运行时可能没有那么多的空间（??）。
我几乎不怀疑（在帖子讲座后）这可能是一个问题，我想b / b
认为你的展位是正确的，原因是某个地方否则......
这就是为什么，我现在会玩猫捉老鼠......
非常感谢
Matik
Thnks Erland! As always good tips.
I thinks now I''ve something to think about, what is going on there :(
I''m really twisted with that a little, what is going on there.
You''ve mentioned, that the backup shouldn''t affect performance ... but
it might affect IO operations (post regarding backups). The problem is,
I''ve a lot of insert and update operations by the running system.
Regarding to that, if there will be a backup performed, which already
affect disk write procedure, additionaly, there can be not so much
space for this processes running (??).
I hardly doubt (after lecture of posts) that this might be an issue, I
think, that you are booth right, that the reason is somewhere else ...
that''s why, I''ll play cat and mouse now ...
Thank you very much
Matik
这篇关于撕裂页面检测和自动缩小：性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
