12

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?

  • 1
    Do you have any specific DMVs in mind? (One example I noticed recently was here sqlskills.com/blogs/jonathan/…) – Martin Smith Jan 17 '13 at 12:11
  • Since Jonathan is doing it, I suspect there must be a reason in some circumstances. ☺ 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. – James L Jan 17 '13 at 12:20
11

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.

7

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.