sql server - Locking when using Read Uncommitted -

i have query take little while run, run isolation level set read uncommitted, data can dirty since used give quick overview of system @ glance.

the query on day (first thing in morning) takes around 3-15 seconds run. (date sorting slows down, instant without sort)

but day starts takes long , longer run, ends taking > 2 minutes run.

what noticed query seems lot of object/page locks when executed.

what don't understand why acquiring locks while set read uncommitted.

---

one of index's query uses set use row/page locking.

my q:

does index set use row/page locks, cause data locked when being accessed, when query read uncommitted?

read uncommitted isolation level changes behaviour of readers only. in read committed , higher isolation level, when task tries read row, forst asks lock manager shared lock on it. request honored if there no existing exclusive lock on other session, otherwise have wait it.

in read uncommitted reader not request shared lock, hence no chance of waiting on possible exclusive lock released.

however, locking behaviour on update same regardless of isolation level used. exclusive locks still being taken in read uncommitted (and held untill end of transaction) in other pessimistic isolation level.

the increase in time required query finish can attributed either this, or resource contention, memory related. put simply, query competes data cache other queries running simultaneously.

take note of page life expectancy counter on various times of day:

select @@servername [server name], [object_name], instance_name, cntr_value [page life expectancy] sys.dm_os_performance_counters (nolock) [object_name] n'%buffer node%' -- handles named instances , counter_name = n'page life expectancy' option (recompile); 

also, see data taking space, use query:

select object_name(p.[object_id]) [object name], p.index_id,  cast(count(*)/128.0 decimal(10, 2)) [buffer size(mb)],   count(*) [buffercount], p.rows [row count], p.data_compression_desc [compression type] sys.allocation_units (nolock) inner join sys.dm_os_buffer_descriptors b (nolock) on a.allocation_unit_id = b.allocation_unit_id inner join sys.partitions p (nolock) on a.container_id = p.hobt_id b.database_id = convert(int,db_id()) , p.[object_id] > 100 group p.[object_id], p.index_id, p.data_compression_desc, p.[rows] order [buffercount] desc option (recompile); 

[this , other diagnostic queries can found here: http://sqlserverperformance.wordpress.com/]