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?
SQL Server – Setting READ UNCOMMITTED When Reading DMVs
dmvsql server
Related Question
- SQL Server – Benefits of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
- SQL Server – Pros and Cons of Read Committed Snapshot Isolation vs Read Committed
- Sql-server – Dirty Read on SQL Server
- SQL Server – Problem Reading Data from Secondary When Reorganizing Clustered Index
- Sql-server – Setting isolation level at database level
- SQL Server – Overriding ReadCommittedSnapshot Isolation Level
- SQL Server – Transaction Isolation Level Declaration Inside vs Outside a Transaction
Best Answer
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.