following is the script to view all the sessions that are waiting for page-related wait types and get information about the objects that the pages belong to.
use master
go
select
er.session_id, er.wait_type, er.wait_resource,
object_name(page_info.[object_id],page_info.database_id) as [object_name],
er.blocking_session_id, er.command,
SUBSTRING(st.text,(er.statement_start_offset/2)+1,
((case er.statement_end_offset
when -1 then DATALENGTH(St.text)
else er.statement_end_offset
END – er.statement_start_offset)/2)+1) as statement_text,
page_info.database_id,page_info.[file_id],page_info.page_id,page_info.[object_id],
page_info.index_id,page_info.page_type_desc
from sys.dm_exec_requests as er
cross apply sys.dm_exec_sql_Text(er.sql_handle) as st
cross apply sys.fn_pagerescracker (er.page_resource) as r
cross apply sys.dm_db_page_info (r.[db_id], r.[file_id], r.page_id,’DETAILED’) as page_info
where er.wait_type like ‘%page%’
go
selectstarfromyogi 