Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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 Lupolt Jan 17 '13 at 12:20
up vote 11 down vote accepted

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.

share|improve this answer
1  
@MartinSmith You're absolutely right. However just as an FYI sp_tables continues to be blocked even under READ UNCOMMITTED, but SELECT * FROM sys.tables does not. Both get blocked under READ COMMITTED and SNAPSHOT. I tracked this down and the blocking has nothing to do with sys.all_objects etc., but rather has_perms_by_name('Foo')... – Aaron Bertrand Jan 17 '13 at 14:01
1  
@AaronBertrand - Well I suppose it helps if you can avoid the function. e.g. SELECT object_id FROM sys.objects WHERE name = 'Foo' works under read uncommitted but SELECT OBJECT_ID('Foo') blocks. – Martin Smith Jan 17 '13 at 14:12
2  
@MartinSmith my blog post wheels are turnin'... – Aaron Bertrand Jan 17 '13 at 14:13
4  
just sayin'. :) – swasheck Jan 18 '13 at 22:20
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 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);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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