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 ?
Update 11:39
Running a CHECKPOINT and removing buffers DBCC DROPCLEANBUFFERS resolved the blocking issue. Still don't understand why though.
Update 18:46
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?