|
> What are the different cached objects in the plan cache?
> 1.0 Structure of the Plan Cache and Types of Cached Objects
> 2.0 Sql_Handle and Plan_Handle Explained
> sys.dm_exec_cached_plans的MSDN页面 SELECT cplan.bucketid,cplan.pool_id,cplan.refcounts,cplan.usecounts,cplan.size_in_bytes,cplan.memory_object_address,cplan.cacheobjtype,cplan.objtype,cplan.plan_handle,'---' AS [---],qrypln.[query_plan],sqltxt.[text],planobj.pages_in_bytes,planobj.pages_in_bytes / 1024 AS [BaseSingleStatementPlanKB],'===' AS [===],cplan.size_in_bytes AS [TotalPlanBytes],bytes.AllocatedBytes,(SELECT CONVERT(VARCHAR(30),obj.memory_object_address,1)
AS [memory_object_address],obj.[type]
--,obj.page_size_in_bytes
FROM sys.dm_os_memory_objects obj
WHERE obj.page_allocator_address = planobj.page_allocator_address
FOR XML RAW(N'object'),ROOT(N'memory_objects'),TYPE) AS [MemoryObjects]
FROM sys.dm_exec_cached_plans cplan
OUTER APPLY sys.dm_exec_sql_text(cplan.[plan_handle]) sqltxt
OUTER APPLY sys.dm_exec_query_plan(cplan.[plan_handle]) qrypln
INNER JOIN sys.dm_os_memory_objects planobj
ON planobj.memory_object_address = cplan.memory_object_address
OUTER APPLY (SELECT SUM(domo.[pages_in_bytes]) AS [AllocatedBytes]
FROM sys.dm_os_memory_objects domo
WHERE domo.page_allocator_address = planobj.page_allocator_address) bytes
WHERE cplan.parent_plan_handle IS NULL
AND cplan.cacheobjtype IN (N'Compiled Plan',N'Compiled Plan Stub')
--AND cplan.plan_handle = 0xDECLARE @CompiledPlanAddress VARBINARY(8) = 0x00000001DC4A4060;
SELECT obj.memory_object_address,obj.pages_in_bytes;
DECLARE @CompiledPlanAddress VARBINARY(8) = 0x00000001DC4A4060;
SELECT obj.memory_object_address,obj.pages_in_bytes;D0031CD572910529CE001000000xxxxxxxx
ORDER BY cplan.objtype,cplan.plan_handle;
（编辑：晋中站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
