READ_COMMITTED_SNAPSHOT的利弊 [英] Pros and cons of READ_COMMITTED_SNAPSHOT
问题描述
在SQL Server 2008中设置 READ_COMMITTED_SNAPSHOT ON
What are the pros and cons of setting READ_COMMITTED_SNAPSHOT ON
in SQL server 2008?
通过设置 READ_COMMITTED_SNAPSHOT ON
并禁用锁定升级(仅在导致死锁的事务中使用的表上)的事务死锁问题)。这完成了死锁的问题约90%;但我担心它可能会有一些其他问题,如性能等。
Actually i was running through the the problem of transaction deadlockS, but by setting READ_COMMITTED_SNAPSHOT ON
on and disabling the Lock Escalation (only on table that used in transactions that causing deadlock). This finished the problem of deadlock by about 90%; but i am worried it might have some other problems like performance etc.
任何帮助将非常感激。
推荐答案
RCSI的优点:
- 提供查询开始时数据的一致视图
- 无阻塞
- 减少锁定/升级
这不是免费的,但是; tempdb用于保存它所称的版本存储。其意思是:
This isn't free, however; tempdb is used to keep what it calls a "version store." Which can mean:
- 空间&如果长期运行的事务要求版本长期存在和/或如果存在许多版本,则tempdb的I / O需求会增加以维护版本
- 潜在的性能下降
此外,行版本信息每行增加14个字节。
Also, row version information adds 14 bytes per row.
RCSI的常见替代方法通常包括拆分将写入活动从报告中移除。这可以使用SQL Server 2012中的各种HA技术(例如日志传送,镜像+快照或可用性组+只读辅助节点)来完成。
Common alternatives to RCSI usually involve splitting up the write activity from reporting. This can be done with various HA technologies such as log shipping, mirroring + snapshots, or Availability Groups + read-only secondaries in SQL Server 2012.
某些官方文档引用:
- Working with tempdb in SQL Server 2005
- Choosing Row Versioning-based Isolation Levels
这篇关于READ_COMMITTED_SNAPSHOT的利弊的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!