sql-server – 规划缓存大小和保留内存

发布时间：2021-03-14 02:43:41 所属栏目：MsSql教程来源：网络整理

导读：副标题#e# 运行包含实际执行计划的查询时,根运算符(SELECT)告诉我缓存计划大小为32KB. 连接sys.dm_exec_cached_plans和sys.dm_os_memory_objects的查询,查看有问题的计划,表示pages_in_bytes和max_pages_in_bytes的值为32768(32KB),与缓存的计划大小相匹配.

> 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 = 0x

DECLARE @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;

（编辑：应用网_阳江站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

3/3

首页

sqlserver 文件数据库	sqlserver中获取月份的
sqlserver 临时表 Vs	SQL多表视图更新的办法