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.

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.

enter image description here

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?

share|improve this question
    
Did you try this? –  Jon Seigel May 28 '13 at 14:12
    
The only DMV that was blocking was the os_buffer_descriptors with or without the join to performance counters. After doing the CHECKPOINT & DBCC DROPCLEANBUFFERS, the DMV didn't hang anymore. The memory drain was also very strange, why would that happen in this situation? –  Craig Efrein May 28 '13 at 14:27
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.