概述了解执行计划对于数据库性能分析非常重要，这涉及到语句性能分析和存储。这也是写这篇文章的目的。在理解执行计划之前，必须了解一些基础知识，所以文章前面会讨论一些概念，学起来会比较枯燥，但是这些基础知识非常重要。基本概念SQLServer有一个内存池，用于存储执行计划和数据缓冲区。分配给池中执行计划或数据缓冲区的百分比随系统状态动态波动。用于存储执行计划的内存池部分称为过程缓存。SQLServer执行计划由以下主要组件组成：查询计划执行计划的主体是可重入的只读数据结构，可供任意数量的用户使用。这称为查询计划。用户上下文未存储在查询计划中。内存中的查询计划副本永远不会超过两份：一份用于所有串行执行，一份用于所有并行执行。并行副本涵盖所有并行执行，无论并行执行的并行度如何。执行上下文每个执行查询的用户都有一个数据结构，其中包含特定于其执行的数据，例如参数值。该数据结构称为执行上下文。可以重用执行上下文数据结构。如果用户执行查询并且其中一个结构未使用，它将使用新用户的上下文重新初始化。执行计划如何缓存SQLServer有一个有效的算法来查找任何特定SQL语句的现有执行计划。在SQLServer中执行任何SQL语句时，关系引擎将首先在过程缓存中查找同一SQL语句的现有执行计划。SQLServer将重用它找到的任何现有计划，从而节省重新编译SQL语句的开销。如果不存在现有的执行计划，SQLServer将为查询生成一个新的执行计划。SQLServer在什么情况下会自动删除执行计划？在没有手动清除缓存的情况下，如果内存不足，SQLServer会自动清除一些未使用的缓存计划。所有缓存的最大大小取决于最大服务器内存的大小。如何确定需要删除哪些执行计划如果内存不足，数据库引擎将使用基于成本的方法来确定从过程缓存中删除哪些执行计划。如何确定执行计划的开销？对于第一次执行的执行计划，SQLServer将其开销值设置为0，对于多次执行的执行计划，SQLServer将其开销值设置为原来的编译开销，所以数据库引擎会反复检查每个执行计划的状态，并删除当前成本为零的执行计划。如果出现内存不足的情况，当前开销为零的执行计划不会被自动删除，只有当数据库引擎检查执行计划，发现其当前开销为零时才会删除。检查执行计划时，如果当前没有查询正在使用该计划，数据库引擎将减少当前开销以将其推向零。数据库引擎反复检查执行计划，直到删除足够多的执行计划以满足内存要求。如果存在内存不足的情况，执行计划可以多次增加或减少其开销。如果内存不足的情况已经消失，数据库引擎将不再减少未使用的执行计划的当前开销，并且所有执行计划将保留在过程缓存中，即使它们的开销为零。重新编译执行计划根据数据库的新状态，数据库中的某些更改可能会导致执行计划效率降低或无效。SQLServer将检测使执行计划无效的更改并将该计划标记为无效。此后，必须为执行查询的下一个连接重新编译新计划。可能使计划无效的情况包括：更改查询引用的表或视图（ALTERTABLE和ALTERVIEW）。更改执行计划使用的任何索引。更新执行计划使用的统计信息，可以是从UPDATESTATISTICS等语句显式更新，也可以是自动更新。删除执行计划使用的索引。显式调用sp_recompile。对键的大量更改（由其他用户在查询引用的表上使用INSERT或DELETE语句引起的修改）。对于带有触发器的表，表中插入或删除的行数会显着增长。使用WITHRECOMPILE选项执行存储过程。#p#测试--1。缓存中的每个对象返回一行，包括缓存计划的类型、缓存引用的对象、缓存计划占用的空间、使用次数、创建时间等。SELECT*FROMsys。syscacheobjects;--2.每个缓存的查询计划返回一行，包括执行计划的使用次数、执行计划的大小、内存地址、执行计划的类型、语句等SELECT*FROMsys.dm_exec_cached_plans;GO---3。Returnthespecifiedsql_handle标识的SQL批处理的文本/*其中sql_handle来自:sys.dm_exec_query_statssys.dm_exec_requestssys.dm_exec_cursorssys.dm_exec_xml_handlessys.dm_exec_query_memory_grantssys.dm_exec_connectionsplan_handle来自:sys.dm_exec_cached_plans*/SELECT*FROMsys.dm_exec_sql_text(sql_handle|plan_handle);GO--4.以XML格式返回planhandle指定的批量查询的显示计划，主要接受来自sys.dm_exec_cached_plans的plan_handle句柄SELECT*FROMsys.dm_exec_query_plan(plan_handle);去--5。每个plan属性返回一行，主要接受fromsys.dm_exec_cached_plansplan_handlehandleSELECT*FROMsys.dm_exec_plan_attributes(plan_handle);去--6。为每个Transact-SQL执行计划、公共语言运行时(CLR)执行计划和与该计划关联的游标返回一行，主要是接受来自sys.dm_exec_cached_plans的plan_handle句柄SELECT*FROMsys.dm_exec_cached_plan_dependent_objects(plan_handle);--7.返回缓存查询计划的聚合性能统计信息。缓存计划中的每个查询语句对应于此视图中的一行，并且该行的生命周期与计划本身相关联。从缓存中删除计划时，相应的行也会从视图中删除。*/--系统视图统计缓存中每个执行计划的执行时间、物理、逻辑操作等信息SELECT*FROMsys.dm_exec_query_statsManuallyclearthecacheexecutionplan---清除并制定数据库执行计划DECLARE@DBIDINTSET@DBID=DB_ID()DBCCFLUSHPROCINDB(@DBID);GO---创建一个测试数据库CREATETABLETPlan(IDINTPRIMARYKEYIDENTITY(1,1),NameNVARCHAR(20)NOTNULL,IstateINTNOTNULL,IdateDATETIMEDEFAULT(GETDATE()))GO---创建一个索引CREATEINDEXIX_TPlan_NAMEONTPplan(Name)GOINSERTINTTOTPlan(Name,Istate)VALUES('1',1),('2',2),('3',3)GOSELECTNAMEFROMTPlanGOSELECTCacheobjtype,objtype,dbid,objid,usecounts,pagesused,sqlFROMsys.syscacheobjectsWHEREDBID=DB_ID()使用Profiler监控使用SQL:StmtRecompile监控，如果是监控存储过程，使用：SP:RecompilemodifyindexAddfieldDROPINDEX[IX_TPlan_NAME]ON[dbo].[TPlan]WITH(ONLINE=OFF)GOUSE[Study]GOCREATENONCLUSTEREDINDEX[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再次执行查询SELECTNAMEFROMTPlantest增加字段对执行计划的影响增加查询非相关词SegmentALTERTABLE[dbo].[TPlan]ADDNumberINT删除查询相关的索引也会导致执行计划重新编译，这里就不贴图查看执行计划了SELECTCache的DB_ID()执行计划显示执行计划被调用了两次。随机系列里写着会重新编译一个新的执行计划。如果是这样的话，这里的值应该是1。猜测：SQLServer通过检测schema变化时的执行计划已经编译了原来的执行计划，所以新的查询中仍然使用第一个查询的执行计划。博客地址：http://www.cnblogs.com/chenmh/archive/2015/04/20/4438086.html
