os_buffer_descriptors blocking problem SQL Server 2008 R2

2 votes
0 answers
147 views
sql-server-2008-r2 locked-objects
                          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?


                        
Asked by user4659
May 28, 2013, 09:17 AM
Last activity: May 28, 2013, 04:53 PM
os_buffer_descriptors blocking problem SQL Server 2008 R2

Related Questions
