The following query :
select (buffer_descriptors.datacache) as 'Data Cache (MB)', case when buffer_descriptors.datacache > 4096 then (((buffer_descriptors.datacache)/4096)*300) else '300' end as 'Min Page Life Expectancy', perf_mon.ple_value as 'Actual Page Life Expectancy (sec)' from ( select (count(*)*8/1024) AS 'datacache' from sys.dm_os_buffer_descriptors where page_type in ( 'INDEX_PAGE' ,'DATA_PAGE' ) ) as buffer_descriptors, ( SELECT cntr_value as ple_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy' AND OBJECT_NAME LIKE '%Buffer Manager%' ) as perf_mon
Is generating a SOS_SCHEDULER_YIELD resource wait and a drain on memory
blocking_session_id : 0
wait_type : SOS_SCHEDULER_YIELD
The client hasn't noticed any particular problems and the server appears relatively quiet.
Any thoughts as to why a SELECT on dm_os_buffer_descriptors might cause a drain on memory and a SOS_SCHEDULER_YIELD wait ?
Running a CHECKPOINT and removing buffers DBCC DROPCLEANBUFFERS resolved the blocking issue. Still don't understand why though.
The blocking occured again so I took Jon's advice and set the ISOLATION level to READ UNCOMMITTED. This worked and allowed the query above to complete. Afterwards, I went hunting around for exclusive locks in dm_tran_locks but didn't find any. Running the dm_os_buffer_descriptors query a second time in READ COMMITTED didn't hang.
Here is the query used to view active locks
SELECT SessionID = s.Session_id, resource_type, DatabaseName = DB_NAME(resource_database_id), c.client_net_address, request_mode, request_type, host_name, program_name, client_interface_name, login_name, nt_domain, nt_user_name, s.status, last_request_start_time, last_request_end_time, request_status, request_owner_type, objectid, dbid, a.number, a.encrypted , a.blocking_session_id, a.text FROM sys.dm_tran_locks l JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id JOIN sys.dm_exec_connections c ON c.session_id = s.session_id LEFT JOIN ( SELECT * FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(sql_handle) ) a ON s.session_id = a.session_id
Should I be able to see what's blocking os_buffer_descriptors using dm_tran_locks? If not, what should I use?