[转]The Hidden Cost of Metadata Cleanup in Merge Replication (SQL Server 2000) [私人]
[ 2008-11-18 01:09:37 | 作者: progame ]
Article by Nigel Maneffa
Introduction: A Brief History of Metadata Table Cleanup
Those using merge replication will know that it uses several system tables, three of which are particularly important: MSmerge_genhistory, MSmerge_contents and MSmerge_tombstone with the first 2 holding all the metadata relating to inserts and updates, and Msmerge_tombstone maintaining metadata on deletes.
Early adopters of Merge replication (introduced in SQL Server 7.0) found that these merge replication metadata tables would consistently grow over time, causing ever increasing slowdowns and intense locking/blocking issues. One possible 'fix' was to completely remove replication and subsequently reinstall it from scratch, which shows that this was without doubt real “version 1.0” stuff. Also, the fact that servers at that time were dramatically less powerful further magnified these issues.
Soon after there followed unofficial, non-Microsoft stored procedures (eg Michael Hotek) to clear out the unnecessary metadata, and ultimately an official MS stored procedure (sp_mergecleanupmetadata) which had the same aim. Having to run these type of procedures manually at infrequent intervals was a real pain as I remember – many years ago now thankfully. There was also the added problem that the system had to be quiesced during this process.
Eventually everyone's prayers were answered when SQL 2000 SP1 introduced automatic metadata cleanup at every synchronisation. The parameter “–MetadataRetentionCleanup” was added to the Merge profile and everyone was home and dry – cleaned up metadata by default and no more manual work.
Performance Implications of Cleaning up the Metadata
Like most things in computing you never get something for nothing, and my findings are that the metadata cleanup process absorbs very large amounts of system processes so if you are running merge synchronizations on a frequent schedule the performance hit on the servers in question can be very noticeable and we ...
阅读全文...
Introduction: A Brief History of Metadata Table Cleanup
Those using merge replication will know that it uses several system tables, three of which are particularly important: MSmerge_genhistory, MSmerge_contents and MSmerge_tombstone with the first 2 holding all the metadata relating to inserts and updates, and Msmerge_tombstone maintaining metadata on deletes.
Early adopters of Merge replication (introduced in SQL Server 7.0) found that these merge replication metadata tables would consistently grow over time, causing ever increasing slowdowns and intense locking/blocking issues. One possible 'fix' was to completely remove replication and subsequently reinstall it from scratch, which shows that this was without doubt real “version 1.0” stuff. Also, the fact that servers at that time were dramatically less powerful further magnified these issues.
Soon after there followed unofficial, non-Microsoft stored procedures (eg Michael Hotek) to clear out the unnecessary metadata, and ultimately an official MS stored procedure (sp_mergecleanupmetadata) which had the same aim. Having to run these type of procedures manually at infrequent intervals was a real pain as I remember – many years ago now thankfully. There was also the added problem that the system had to be quiesced during this process.
Eventually everyone's prayers were answered when SQL 2000 SP1 introduced automatic metadata cleanup at every synchronisation. The parameter “–MetadataRetentionCleanup” was added to the Merge profile and everyone was home and dry – cleaned up metadata by default and no more manual work.
Performance Implications of Cleaning up the Metadata
Like most things in computing you never get something for nothing, and my findings are that the metadata cleanup process absorbs very large amounts of system processes so if you are running merge synchronizations on a frequent schedule the performance hit on the servers in question can be very noticeable and we ...
阅读全文...
守望相助 血脉相连 举国同心 共渡难关
[ 2008-05-17 07:25:28 | 作者: progame ]
地震过去有几天了, 尽管电视台里播报着一个又一个"好消息", 但这些幸运的生还者在所有不幸者中百中无一
生命很脆弱, 一瞬间的死亡并不知道痛苦, 但那种长时间在死亡线上挣扎徘徊并最终绝望的痛苦是一种怎样的折磨
很小的时候经历过一次溺水, 脚下的流沙呈斜坡状, 越踩越向下滑, 巨大的惊恐, 想着自己是不是就要这样死去了?
这种惊恐一直以来就很清晰地存在脑海里, 那是一种彻底的绝望
这于我只不过不到一分钟的时间, 但地震中被埋的人们, 是3天, 是72小时, 是分分秒秒的绝望
一直以来, 写日志以玩世不恭的口吻, 对于这样的一个题材, 才发现竟然没有一个分类可以放置, 于是只能是未分类.
同样的, 人们在灾难面前, 又怎样的分类, 好人? 坏人? 穷人? 富人?
现在全国都在捐助, 我一直对捐助的最终去向持一定的怀疑态度, 并且认为国家的财力不应该呼吁民间捐赠
但有人说得好, 即使是一小部分能够到达灾区人民的手中, 那也是一种欣慰
最终我选择了通过腾讯的在线捐赠渠道, 没有财富通, 只好网银, 进入一看, 活期钱太少了
还好有笔钱是5月12号到期的, 5.12, 居然是这样巧合的一个数字
活着, 是幸运的, 虽然生活, 仍然是痛苦的
于我, 失去和拥有这点钱改变不了太多, 但希望, 于灾区人民, 是一点帮助.
生命很脆弱, 一瞬间的死亡并不知道痛苦, 但那种长时间在死亡线上挣扎徘徊并最终绝望的痛苦是一种怎样的折磨
很小的时候经历过一次溺水, 脚下的流沙呈斜坡状, 越踩越向下滑, 巨大的惊恐, 想着自己是不是就要这样死去了?
这种惊恐一直以来就很清晰地存在脑海里, 那是一种彻底的绝望
这于我只不过不到一分钟的时间, 但地震中被埋的人们, 是3天, 是72小时, 是分分秒秒的绝望
一直以来, 写日志以玩世不恭的口吻, 对于这样的一个题材, 才发现竟然没有一个分类可以放置, 于是只能是未分类.
同样的, 人们在灾难面前, 又怎样的分类, 好人? 坏人? 穷人? 富人?
现在全国都在捐助, 我一直对捐助的最终去向持一定的怀疑态度, 并且认为国家的财力不应该呼吁民间捐赠
但有人说得好, 即使是一小部分能够到达灾区人民的手中, 那也是一种欣慰
最终我选择了通过腾讯的在线捐赠渠道, 没有财富通, 只好网银, 进入一看, 活期钱太少了
还好有笔钱是5月12号到期的, 5.12, 居然是这样巧合的一个数字
活着, 是幸运的, 虽然生活, 仍然是痛苦的
于我, 失去和拥有这点钱改变不了太多, 但希望, 于灾区人民, 是一点帮助.
1
