为什么更好的隔离级别意味着更好的SQL Server性能 [英] Why better isolation level means better performance in 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.

解决方案

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.

这篇关于为什么更好的隔离级别意味着更好的SQL Server性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！