Take the 2-minute tour ×
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.

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 Jan 17 '13 at 12:20
add comment

2 Answers 2

up vote 10 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
    
@MartinSmith Interesting, I can't seem to get sp_tables to block, in READ COMMITTED or READ UNCOMMITTED, in SQL Server 2012 or 2008 R2. Do you still have a repro handy? Also, using your test and setting SNAPSHOT against catalog views like sys.databases and sys.all_objects, I do see slightly different locking output, but nothing that would suggest locks are avoided. –  Aaron Bertrand Jan 17 '13 at 13:45
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
show 2 more comments

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
add comment

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.