patternsqlModerate
Setting READ UNCOMMITTED when reading DMVs
Viewed 0 times
readinguncommittedreaddmvssettingwhen
Problem
I've seen several people call
Is there ever any reason to do this, assuming you aren't mixing calls to DMVs and tables in the same transaction?
What prompted me to ask the question was seeing it in a query that joined
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?
What prompted me to ask the question was seeing it in a query that joined
sys.dm_exec_query_stats to sys.dm_exec_sql_text and sys.dm_exec_query_plan.Solution
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.
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.
Context
StackExchange Database Administrators Q#33074, answer score: 11
Revisions (0)
No revisions yet.