- 当前位置：首页 >数据库 >SQL Server 执行计划缓存
SQL Server 执行计划缓存
发布时间：2025-11-04 14:21:07 来源：码上建站 作者：应用开发
概述
了解执行计划对数据库性能分析很重要，行计其中涉及到了语句性能分析与存储，划缓这也是行计写这篇文章的目的，在了解执行计划之前先要了解一些基础知识，划缓所以文章前面会讲一些概念，行计学起来会比较枯燥，划缓但是行计这些基础知识非常重要。
基础概念
SQL Server 有一个用于存储执行计划和数据缓冲区的划缓内存池。池内分配给执行计划或数据缓冲区的行计百分比随系统状态动态波动。内存池中用于存储执行计划的划缓部分称为过程缓存。
SQL Server 执行计划包含下列主要组件：
查询计划
执行计划的行计主体是一个重入的只读数据结构，可由任意数量的划缓用户使用。这称为查询计划。行计查询计划中不存储用户上下文。划缓内存中查询计划副本永远不超过两个：一个副本用于所有的行计串行执行，另一个用于所有的并行执行。并行副本覆盖所有的并行执行，与并行执行的并行度无关。
执行上下文
每个正在执行查询的亿华云计算用户都有一个包含其执行专用数据(如参数值)的数据结构。此数据结构称为执行上下文。执行上下文数据结构可以重新使用。如果用户执行查询而其中的一个结构未使用，将会用新用户的上下文重新初始化该结构。
怎样缓存执行计划
SQL Server 有一个高效的算法，可查找用于任何特定 SQL 语句的现有执行计划。在 SQL Server 中执行任何 SQL 语句时，关系引擎将首先查看过程缓存中是否有用于同一 SQL 语句的现有执行计划。SQL Server 将重新使用找到的任何现有计划，从而节省重新编译 SQL 语句的开销。如果没有现有执行计划，SQL Server 将为查询生成新的执行计划。
SQL Server自动删除执行计划
什么情况下会删除执行计划
在没有人工手动清除缓存的情况下，如果出现内存不足的情况下SQL Server会自动清除一部分没被利用到的缓存计划。
所有缓存的***大小取决于max server memory的大小。
怎样判断需要删除的执行计划
如果存在内存不足的高防服务器情况，数据库引擎将使用基于开销的方法来确定从过程缓存中删除哪些执行计划。怎样确定一个执行计划的开销呢，对于一个***次执行的执行计划SQL Server将它的开销值设为0，被多次执行过的执行计划SQL Server将它的开销值设置为原始编译开销，所以数据库引擎会重复检查每个执行计划的状态并将删除当前开销为零的执行计划。如果存在内存不足的情况，当前开销为零的执行计划不会自动被删除，而只有在数据库引擎检查该执行计划并发现其当前开销为零时，才会删除该计划。当检查执行计划时，如果当前没有查询使用该计划，则数据库引擎将降低当前开销以将其推向零。
数据库引擎会重复检查执行计划，直至删除了足够多的执行计划，以满足内存需求为止。如果存在内存不足的情况，执行计划可多次对其开销进行增加或降低。如果内存不足的情况已经消失，数据库引擎将不再降低未使用执行计划的源码下载当前开销，并且所有执行计划都将保留在过程缓存中，即使其开销为零也是如此。
重新编译执行计划
根据数据库新状态的不同，数据库中的某些更改可能导致执行计划效率降低或无效。SQL Server 将检测到使执行计划无效的更改，并将计划标记为无效。此后，必须为执行查询的下一个连接重新编译新的计划。导致计划无效的情况包括：
对查询所引用的表或视图进行更改(ALTER TABLE 和 ALTER VIEW)。 对执行计划所使用的任何索引进行更改。 对执行计划所使用的统计信息进行更新，这些更新可能是从语句(如 UPDATE STATISTICS)中显式生成，也可能是自动生成的。 删除执行计划所使用的索引。 显式调用 sp_recompile。 对键的大量更改(其他用户对由查询引用的表使用 INSERT 或 DELETE 语句所产生的修改)。 对于带触发器的表，插入的或删除的表内的行数显著增长。 使用 WITH RECOMPILE 选项执行存储过程。#p#
测试
--1.缓存的每一个对象返回一行，包括缓存计划的类型、缓存引用的对象、缓存计划占用的空间、被使用次数、以及创建时间等 SELECT * FROM sys.syscacheobjects; --2.缓存的每个查询计划返回一行，包括执行计划被使用的次数、执行计划的大小、内存地址、执行计划的类型、语句等 SELECT * FROM sys.dm_exec_cached_plans; GO ---3.返回由指定的 sql_handle 标识的 SQL 批处理的文本 /*其中sql_handle来自: sys.dm_exec_query_stats sys.dm_exec_requests sys.dm_exec_cursors sys.dm_exec_xml_handles sys.dm_exec_query_memory_grants sys.dm_exec_connections plan_handle来自:sys.dm_exec_cached_plans */ SELECT * FROM sys.dm_exec_sql_text(sql_handle | plan_handle); GO --4.以 XML 格式返回计划句柄指定的批查询的显示计划，主要接受来自sys.dm_exec_cached_plans的plan_handle句柄 SELECT * FROM sys.dm_exec_query_plan(plan_handle); GO --5.每个计划属性返回一行，主要接受来自sys.dm_exec_cached_plans的plan_handle句柄 SELECT * FROM sys.dm_exec_plan_attributes(plan_handle); GO --6.针对每个 Transact-SQL 执行计划、公共语言运行时 (CLR) 执行计划和与计划关联的游标返回一行,，主要接受来自sys.dm_exec_cached_plans的plan_handle句柄 SELECT * FROM sys.dm_exec_cached_plan_dependent_objects(plan_handle); --7.返回缓存查询计划的聚合性能统计信息。缓存计划中的每个查询语句在该视图中对应一行，并且行的生存期与计划本身相关联。在从缓存删除计划时，也将从该视图中删除对应行。*/ --该系统视图针对每一个缓存中的执行计划统计其执行时间、物理、逻辑操作等信息 SELECT * FROM sys.dm_exec_query_stats手动清空缓存执行计划
---清空制定数据库执行计划 DECLARE @DBID INT SET @DBID=DB_ID() DBCC FLUSHPROCINDB(@DBID); GO ---创建测试数据库 CREATE TABLE TPlan (ID INT PRIMARY KEY IDENTITY(1,1), Name NVARCHAR(20) NOT NULL, Istate INT NOT NULL, Idate DATETIME DEFAULT(GETDATE()) ) GO ---创建索引 CREATE INDEX IX_TPlan_NAME ON TPlan (Name ) GO INSERT INTO TPlan(Name,Istate) VALUES(1,1),(2,2),(3,3) GO SELECT NAME FROM TPlan GO SELECT Cacheobjtype,objtype,dbid,objid,usecounts,pagesused,sql FROM sys.syscacheobjects WHERE DBID=DB_ID()使用Profiler监控
使用SQL:StmtRecompile监控，如果是监控存储过程则使用：SP:Recompile
修改索引
在索引中添加字段
DROP INDEX [IX_TPlan_NAME] ON [dbo].[TPlan] WITH ( ONLINE = OFF ) GO USE [Study] GO CREATE NONCLUSTERED INDEX [IX_TPlan_NAME] ON [dbo].[TPlan] ( [Name] ASC ) INCLUDE ( [Istate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] GO再执行查询
SELECT NAME FROM TPlan
测试增加字段对执行计划的影响
增加查询非相关字段
ALTER TABLE [dbo].[TPlan] ADD Number INT
删除查询有关的索引也同样会导致执行计划重编译，这里就不截图贴出来了。
查看执行计划
SELECT Cacheobjtype,objtype,dbid,objid,usecounts,pagesused,sql FROM sys.syscacheobjects WHERE DBID=DB_ID()
执行计划中显示了该执行计划被调用了两次，在随机丛书中写的是会重新编译新的执行计划，如果是这样的话那这里的值应该是1才对。
猜测：SQL Server在架构更改的时候通过检测执行计划已经对原先的执行计划进行了编译，所以在新的查询中还是使用了***次查询的执行计划。
博文地址：http://www.cnblogs.com/chenmh/archive/2015/04/20/4438086.html
- OPPOR9Plus在VR方面的表现如何？（探索OPPOR9Plus在虚拟现实技术应用上的优势与不足）
- windows 7系统运行游戏应用程序报错0xc000007d无法正常启动
- 使用任务计划功能结合shutdown命令让windows 7实现定时自动执行
- 如何关闭windows 7笔记本电脑屏幕的正前方摄像头
- 显示器错误导致的电脑故障（故障原因、解决方法及预防措施）
- windows 7系统禁止U盘使用当u盘插入后不会运行起来
- 闪拍windows 7/windows 8.1任务管理器定格、冰冻住那一瞬间
- 如何使用windows 7分析用电源效揪出耗电大户
- 解决电脑开机密码错误问题的有效方法（密码错误怎么办？电脑开机密码错误解决方案来了！）
- 不装软件如何让Win 7桌面自动换
- 电脑操作教程（以电脑为工具，详细教你制作系统U盘的方法）
- windows 7系统下如何解决鼠标左右键功能互换情况?
- windows 7任务栏高度怎么设置？正常应该是多高？
- 自定义windows 7开机界面背景图片的方法（不用任何软件）
- 联想老办公电脑改造教程（打造高性能工作站的关键步骤和技巧）
- windows 7系统笔记本怎么把自动安装驱动改为手动模式？
- windows 7系统盘瘦身：删除索引记录文档减少系统空间占用
- 让鼠标调用Windows7系统中的立体窗口
- 飞利浦MP3SA0283音质如何？（揭秘飞利浦MP3SA0283的音质表现及特点）
- windows 7音量控制图标不见了怎么办(音量一栏是灰色)
随便看看
- Copyright © 2025 Powered by SQL Server 执行计划缓存,码上建站 滇ICP备2023006006号-47sitemap