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?
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.
-
1@MartinSmith it took a while, but I did eventually blog about how many of the metadata functions ignore isolation level. I've tried to clean up a lot of my code, but anything new I've developed since then prefers joins rather than built-ins like
OBJECT_ID()
,SCHEMA_NAME()
, etc. – Aaron Bertrand Feb 12 '16 at 19:39
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 SERIALIZABLE
doesn't change the output).
/*Do once so compilation and caching out the way*/
EXEC('select st.text, qp.query_plan, cp.cacheobjtype, cp.objtype, cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp')
DBCC TRACEON(1200,3604,-1);
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
PRINT 'READ COMMITTED'
EXEC('select st.text, qp.query_plan, cp.cacheobjtype, cp.objtype, cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp')
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
PRINT 'READ UNCOMMITTED'
EXEC('select st.text, qp.query_plan, cp.cacheobjtype, cp.objtype, cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp')
DBCC TRACEOFF(1200,3604,-1);
sys.dm_exec_query_stats
tosys.dm_exec_sql_text
andsys.dm_exec_query_plan
. – James L Jan 17 '13 at 12:20