I've seen several people call
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before reading system DMVs. Is there ever any reason to do this, assuming you aren't mixing calls to DMVs and tables in the same transaction?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
As one of the guys writes demo DMV queries that way, I'll explain why.
Does it matter if you're only querying DMVs? No. But sooner or later, you're going to take one of your DMV scripts and tack on a join to sys.databases or sys.tables or some other system object in order to get more information about what you're looking at. If you don't have read uncommitted on there, you can be blocked by other queries, and block other queries. I've been burned by that repeatedly, so I just use READ UNCOMMITTED by default whenever I'm doing any diagnostic work whatsoever.
I don't see that it makes any difference.
If I try the following and compare the lock output for both isolation levels in winmerge they are exactly the same (and even putting it up to