Tuesday, July 5, 2011

How to find objects/plans in memory in sql server

select
this will give you the database id, page id,page_type, row count so you can identify which pages(data/index) are in buffer for that particular database

select
this will as well give you the cached objects (compiled plans, adhoc, extended procs)

or

select
this will give the same result as syscacheobjects

select * from sys.dm_exec_plan_attributes(pass the plan handle from the above query here)
* from sys.dm_exec_cached_plans * from syscacheobjects * from sys.dm_os_buffer_descriptors

No comments:

Post a Comment