为什么更好的隔离级别意味着 SQL Server 更好的性能
在测量查询性能时，我发现隔离级别和运行时间之间存在依赖关系，这让我感到惊讶。
READUNCOMMITTED - 409024
READCOMMITTED - 368021
REPEATABLEREAD - 358019
SERIALIZABLE - 348019
左列是表提示，右列是运行时间（以微秒为单位）（sys.dm_exec_query_stats.total_elapsed_time）。为什么更好的隔离级别可以提供更好的性能？这是一台开发机器，不会发生任何并发情况。由于锁定开销较少，我希望 READUNCOMMITTED 会被禁食。
更新：我确实对此进行了测量
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
，Profiler 确认没有发生缓存命中。
When measuring performance on my query I came up with a dependency between isolation level and elapsed time that was surprising to me
READUNCOMMITTED - 409024
READCOMMITTED - 368021
REPEATABLEREAD - 358019
SERIALIZABLE - 348019
Left column is table hint, and the right column is elapsed time in microseconds (sys.dm_exec_query_stats.total_elapsed_time). Why better isolation level gives better performance? This is a development machine and no concurrency whatsoever happens. I would expect READUNCOMMITTED to be the fasted due to less locking overhead.
Update: I did measure this with
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
issued and Profiler confirms there're no cache hits happening.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
首先，您需要在每个隔离级别下重复运行查询并对结果进行平均，丢弃时间最长的查询。这将消除缓冲区预热的影响：您希望所有运行都在热缓存上，而不是让一个查询预热缓存并付出比较代价。
接下来，您需要确保在现实的并发场景下进行测量。如果您将在现实生活中发生更新/插入/删除，那么您必须将它们添加到您的测试中，因为它们将极大地影响各种隔离级别下的读取。您最不想看到的就是得出“可序列化读取速度最快，让我们在任何地方使用它们”的结论，然后看着系统在生产中崩溃，因为一切都已序列化。
除此之外，唯一更快的隔离级别是脏读，因为它不获取锁。读取提交的快照（您没有测量）也不会获取锁，但由于行版本控制开销，它确实会影响整体性能。
First of all, you need to run the query repeatedly under each isolation level and average the result, discarding the one with the maximum time. This will eliminate the buffer warm up impact: you want all runs to be on a warm cache, not have one query warm the cache and pay the penalty in comparison.
Next, you need to make sure you measure under realistic concurrency scenario. IF you will have updates/inserts/deletes occur under real life, then you must add them to your test, since they will impact tremendously the reads under various isolation level. The last thing you want is to conclude 'serializable reads are fastest, lets use them everywhere' and then watch the system melt down in production because everything is serialized.
Other than that, the only isolation level that is legitimately faster is dirty reads, since it doesn't acquire locks. Read committed snapshot (which you did not measure) also doesn't acquire locks, but it does impact performance overall due to row versioning overhead.
现在我更好地了解了隔离级别，我发现更好的隔离级别可以实现一些智能优化。例如，一旦事务读取某些数据，隔离级别可能规定它应该使用该数据直到最后，而不是尝试从磁盘重新读取它们。
我仍然有兴趣阅读一些对此的深入研究。
Now that I understand isolation levels better I see that better isolation level could allow some smart optimizations. For example, once transaction reads some data isolation level might stipulate it is ought to use that data till the end rather than try to re-read them from disk.
I still would be interested to read some in-depth look on this.