解答SQL Server 执行计划缓存详情
当前位置：以往代写 > 数据库教程 >解答SQL Server 执行计划缓存详情

解答SQL Server 执行计划缓存详情

2019-06-14

解答SQL Server 执行计划缓存详情

解答SQL Server 执行计划缓存详情

解答SQL Server 执行计划缓存详情
今天，小编的数据库学习助手要跟大家分享的是关于SQL Server 执行计划缓存的一下内容。想了解的朋友们就继续往下看吧！
SQL Server是由Microsoft开发和推广的关系数据库管理系统（DBMS），它最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的，并于1988年推出了第一个OS/2版本。Microsoft SQL Server近年来不断更新版本，特点：

1．真正的客户机/服务器体系结构。
2．图形化用户界面，使系统管理和数据库管理更加直观、简单。
3．丰富的编程接口工具，为用户进行程序设计提供了更大的选择余地。
4．SQL Server与Windows NT完全集成，利用了NT的许多功能，如发送和接受消息，管理登录安全性等。SQL Server也可以很好地与Microsoft BackOffice产品集成。
5．具有很好的伸缩性，可跨越从运行Windows 95/98的膝上型电脑到运行Windows 2000的大型多处理器等多种平台使用。
6．对Web技术的支持，使用户能够很容易地将数据库中的数据发布到Web页面上。
7．SQL Server提供数据仓库功能，这个功能只在Oracle和其他更昂贵的DBMS中才有。

了解执行计划对数据库性能分析很重要，其中涉及到了语句性能分析与存储，这也是写这篇文章的目的，在了解执行计划之前先要了解一些基础知识，所以文章前面会讲一些概念，学起来会比较枯燥，但是这些基础知识非常重要。
基础概念
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#分页标题#e#

    测试

    1. –1.缓存的每一个对象返回一行，包括缓存计划的类型、缓存引用的对象、缓存计划占用的空间、被使用次数、以及创建时间等 
    2. SELECT * FROM sys.syscacheobjects; 
    3. –2.缓存的每个查询计划返回一行，包括执行计划被使用的次数、执行计划的大小、内存地址、执行计划的类型、语句等 
    4. SELECT * FROM sys.dm_exec_cached_plans; 
    5. GO 
    6. —3.返回由指定的 sql_handle 标识的 SQL 批处理的文本 
    7. /*其中sql_handle来自: 
    8. sys.dm_exec_query_stats 
    9. sys.dm_exec_requests 
    10. sys.dm_exec_cursors 
    11. sys.dm_exec_xml_handles 
    12. sys.dm_exec_query_memory_grants 
    13. sys.dm_exec_connections 
    14. plan_handle来自:sys.dm_exec_cached_plans 
    15. */  
    16. SELECT * FROM sys.dm_exec_sql_text(sql_handle | plan_handle); 
    17. GO 
    18. –4.以 XML 格式返回计划句柄指定的批查询的显示计划，主要接受来自sys.dm_exec_cached_plans的plan_handle句柄 
    19. SELECT * FROM sys.dm_exec_query_plan(plan_handle); 
    20. GO 
    21. –5.每个计划属性返回一行，主要接受来自sys.dm_exec_cached_plans的plan_handle句柄 
    22. SELECT * FROM sys.dm_exec_plan_attributes(plan_handle); 
    23. GO 
    24. –6.针对每个 Transact-SQL 执行计划、公共语言运行时 (CLR) 执行计划和与计划关联的游标返回一行,，主要接受来自sys.dm_exec_cached_plans的plan_handle句柄 
    25. SELECT * FROM sys.dm_exec_cached_plan_dependent_objects(plan_handle); 
    26.  
    27. –7.返回缓存查询计划的聚合性能统计信息。缓存计划中的每个查询语句在该视图中对应一行，并且行的生存期与计划本身相关联。在从缓存删除计划时，也将从该视图中删除对应行。*/ 
    28. –该系统视图针对每一个缓存中的执行计划统计其执行时间、物理、逻辑操作等信息 
    29. SELECT * FROM sys.dm_exec_query_stats 

    手动清空缓存执行计划

    1. —清空制定数据库执行计划 
    2. DECLARE @DBID INT 
    3. SET @DBID=DB_ID() 
    4. DBCC FLUSHPROCINDB(@DBID); 
    5. GO 
    6.  
    7. —创建测试数据库 
    8. CREATE TABLE TPlan 
    9. (ID INT PRIMARY KEY IDENTITY(1,1), 
    10. Name NVARCHAR(20) NOT NULL, 
    11. Istate INT NOT NULL, 
    12. Idate DATETIME DEFAULT(GETDATE()) 
    13. ) 
    14. GO 
    15. —创建索引 
    16. CREATE INDEX IX_TPlan_NAME ON TPlan 
    17. (Name 
    18. ) 
    19. GO 
    20. INSERT INTO TPlan(Name,Istate) 
    21. VALUES('1',1),('2',2),('3',3) 
    22.  
    23. GO 
    24. SELECT NAME FROM TPlan 
    25. GO 
    26. SELECT Cacheobjtype,objtype,dbid,objid,usecounts,pagesused,sql FROM sys.syscacheobjects 
    27. WHERE DBID=DB_ID() 

    使用Profiler监控
    使用SQL:StmtRecompile监控，如果是监控存储过程则使用：SP:Recompile

    修改索引
    在索引中添加字段

    1. DROP INDEX [IX_TPlan_NAME] ON [dbo].[TPlan] WITH ( ONLINE = OFF ) 
    2. GO 
    3. USE [Study] 
    4. GO 
    5. CREATE NONCLUSTERED INDEX [IX_TPlan_NAME] ON [dbo].[TPlan]  
    6. ( 
    7.     [Name] ASC 
    8. ) 
    9. 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] 
    10. GO 

    再执行查询

    1. SELECT NAME FROM TPlan 

    测试增加字段对执行计划的影响
    增加查询非相关字段

    1. ALTER TABLE [dbo].[TPlan] 
    2. ADD Number INT 

    #p#分页标题#e#

    删除查询有关的索引也同样会导致执行计划重编译，这里就不截图贴出来了。
    查看执行计划

    1. SELECT Cacheobjtype,objtype,dbid,objid,usecounts,pagesused,sql FROM sys.syscacheobjects  
    2. WHERE DBID=DB_ID() 

    执行计划中显示了该执行计划被调用了两次，在随机丛书中写的是会重新编译新的执行计划，如果是这样的话那这里的值应该是1才对。

    猜测：SQL Server在架构更改的时候通过检测执行计划已经对原先的执行计划进行了编译，所以在新的查询中还是使用了第一次查询的执行计划。

     解答SQL Server 执行计划缓存详情
    今天的内容就到此结束了。如果你还觉得意犹未尽的话，更多相关的数据库教程视频尽在课课家官方网。

      关键字：

    在线提交作业