后面的练习中需要下载 Demo 数据库, 有很多不同的版本, 可以根据个人需要下载. 下载地址 - http://msftdbprodsamples./ 1. 什么是执行计划
2. 当一个 Query 被提交后到底发生了什么?
实际上,当一个查询被提交到 SQL Server 后,服务器端很多进程实际上要做很多事情来确保数据出入的完整性.
对于 T-SQL 来说, 处理的主要有两个阶段: 关系引擎阶段和存储引擎阶段.
关系引擎主要要做的事情就是首先确保 Query 语句能够正确的解析, 然后交给查询优化器并产生执行计划. 然后执行计划就以二进制格式发到存储引擎来更新或者读取数据.
存储引擎主要处理的比如像锁, 索引的维护和事务等. 所以对于执行计划,重点的是关注关系引擎.
3. Query Parsing - 查询解析
首先这里的查询解析发生在关系引擎上, 关系引擎专门有一项任务就是先检查下这个 SQL 语句写的对不对,如果能够顺利解析那么就会解析出一个"解析树"出来. 这个解析树能够呈现要执行这个 SQL 语句的逻辑步骤.
当 SQL 语句不是 DML 类型的话, 那么就不需要被优化, 因为比如像创建表这类语句目标性都比较明确就是创建表,非常的直接,所以就这类语句就不需要被调优.
4. 查询优化器
查询优化器实际上是一个在关系型引擎上的小软件模块,它根据上面的解析树和一些有关数据的统计信息来进行一些分析,然后就算出它认为一种效率比较高的能够执行这个 SQL 的执行计划.
具体的比如决定是否可以通过索引来直接获取数据,或者选择哪种 JOIN 方式会更好等等. 实际上除非这里有缓存的执行计划, 那么它就会计算几种不同的执行计划,但是根据 CPU 运行还有I/O的一些估算,看看那种候选的执行计划使用的资源可能会最少,这样最终通过比较就选择一种成本最低的执行计划.
但是有时,当一个 SQL 语句很简单的时候,执行这个 SQL 语句可能花费的时间比分析不同的候选计划的时间可能还要少,那么查询优化器可能会选择一种相对简单的执行计划就可以了. 如果 SQL 语句它认为比较重要,那么就花时间去排列不同的执行计划最后选一个成本最低的计划.
能做到这一点,实际上要依赖于被 SQL Server 维护的一些统计数据. 这些统计数据比如像目标对象上的数据列,索引,唯一性等等,这些内容都是查询优化器分析执行计划的依据. 比如有的列有索引,有的没有,有的列在 JOIN 条件或者 WHERE 条件上, 查询优化器都会对这些统计信息做分析来尝试不同的比如 JOIN 顺序, 尝试不同的索引等等,然后选择一个执行最快的执行计划. 在这种执行计划当中实际上都有一个一个执行的步骤,查询优化器对执行这些步骤耗费的时间有一个大概的估算,那么这样一步一步到整个执行计划就有了一个“成本估算”Estimated Cost.
一旦这个执行计划被查询优化器计算出来, 那么它就会被在一个叫做 "缓存计划" 的内存空间里被创建, 创建的时候也会检查下"缓存计划中" 是不是已经存在这个计划,如果没有就直接创建,如果有就用已经存在的计划.
5. 查询执行
一旦执行计划被创建, 那么处理的程序就移动到存储引擎, 在存储引擎中这个查询才会被真正的执行, 执行的方式就是按照之前产生出来的"执行计划".
6. 估算执行计划和实际执行计划 Estimated and Actual Execution Plans
它们之间的区别就是- 估算的执行计划是从查询优化器来的, 是输入关系引擎的, 它的执行步骤包括一些运算符等等都是通过一系列的逻辑分析出来的,是一种通过逻辑推算出来的计划,只能代表查询优化器的观点.
实际执行计划是真实的执行了"估算执行计划"后的一种真实的结果,是实实在在真实的执行反馈, 是属于存储引擎.
7. 查询计划重用
SQL Server 上维护和生产这些查询计划的成本比较大, 所以 Server 会尽量的考虑重用这些计划. 一旦创建在"缓存计划"里,每次从查询优化器出来的"估算执行计划"就会和"缓存计划"中的计划进行对比, 如果有的话就直接使用"缓存计划"中已经存在的计划,这样就可以重用.
特别是一个系统有大批大批的 SQL 语句需要被分析被创建执行计划,通过这种方式可以避免反复的创建,减少不必要的开销. 比如有 1000 组 SQL 语句, 有一部分还非常复杂,并且在1分钟内可能被反复调用了成千上万次, 如果每次进来都由查询优化器估算并在缓存里创建一遍, 这个开销还是非常大的.
但是有时查询优化器也会去考虑性能的问题,如果它认为这个计划在某种条件下比如CPU,内存等等足够足够,并且如果并行执行计划时性能更好的话,那么也有可能会为同一个 SQL 创建两个执行计划.
8. 执行计划的清除
执行计划并不是一直会保存在内存中, Server 会定期的对执行计划进行清理. 执行计划一般都会有一个类似于倒数计数器的一个记录, 比如有一个初始值 50, 每用一次减一个数,减到0的时候系统就会释放这一块内存, 总结起来有这么几种情况:
9. 执行计划的重新编译,在这些情况下执行计划会被重新编译:
10. 可以通过 DBCC FREEPROCCACHE 来清除缓存
11. 查看系统中已有的执行计划 SELECT [cp].[refcounts]
,[cp].[usecounts]
,[cp].[objtype]
,[st].[dbid]
,[st].[objectid]
,[st].[text]
,[qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
qp
执行结果
可以打开一个 Query Plan 来看
12. 为什么 Estimated Execution Plan 和 Actual Execution Plan 不一样
CREATE TABLE TempTable ( Id INT IDENTITY(1, 1) ,Dsc NVARCHAR(50) ); INSERT INTO TempTable ( Dsc ) SELECT Value FROM dbo.Brand SELECT * FROM TempTable; DROP TABLE TempTable; 如果点击 "Display Estimated Execution Plan" 将会产生以下错误信息: Msg 208, Level 16, State 1, Line 6 Invalid object name 'TempTable'. 原因就是 TempTable 是不可能在 Estimated Execution Plan 阶段创建的, 因为还处于在关系引擎控制阶段, 还没有到存储引擎. 13. Execution Plan Formats 执行计划的3种格式 (个人比较喜欢图形执行计划, 但有时也会使用文本执行计划查看具体的数据)
注意 - 加密的存储过程不会在执行计划中呈现
14. 查看 Execution Plan 的权限
用户如果不是 sysadmin, dbcreator 和 db_owner. 就需要 SHOWPLAN 权限 - GRANT SHOWPLAN TO [username]
15. 测试语句
SELECT *
FROM [dbo].[DatabaseLog]
Table scan – 出现在当存储引擎强制性的一行一行的遍历整张表或者返回所有内容, 因为我们并没有使用任何的 WHERE 条件并且我们也没有使用到任何的索引(索引上应该包含它所在表上的所有列). 16.对于 Estimated Execution Plan 执行计划
查看查询优化器给出 SELECT 操作符的执行成本情况
查看 Table Scan
17. 预估的文本执行计划 Getting the Estimated Text Plan 在启用文本的预估执行计划时, 先要打开这个计划. SET SHOWPLAN_ALL ON; 并且要注意在使用完了之后也一定要 SET SHOWPLAN_ALL OFF. 18. 实际文本执行计划 Getting the Actual Text Plan SET STATISTICS PROFILE ON SET STATISTICS PROFILE OFF 19. XML 执行计划 Estimated Plan - SET SHOWPLAN_XML ON / SET SHOWPLAN_XML OFF 20. XML 实际执行计划 Actual Plan – SET STATISTICS XML ON / SET STATISTICS XML OFF 21. 如何将XML 计划保存为图形执行计划 可以将XML 格式的执行计划保存为后缀为 “.sqlplan” 的文件, 这个文件显示的就是图形执行计划. 因为有的时候需要把某个执行计划的结果发给其他人来调整我们的SQL 语句. 22. 在 SQL Profiler 中自动捕获执行计划 在实际开发过程中, 可能随时需要查看不同SQL 语句的执行计划, 并且最好是能够自动捕获 SQL 的执行计划以便随时查看有哪些查询耗费的成本比较高, 或者像这种有 Table Scan 的情况都需要被截获并查看他们的执行计划. 在SQL Server 2005可以通过 SQL Profiler 来帮助捕获这些计划, 后期的版本也支持这个功能. 捕获后的计划可以直接在SQL Profiler 中浏览或者保存到文件, 再或者存放到数据库. 在 SQL Profiler 中选中以下几种事件
选中 Showplan XML 事件后会出现一个新的Tab – Events Extraction Settings. 在这里可以配置 XML Execution plan 保存的地点以便以后查看, 还可以选择将Execution Plan 保存在一个文件还是每个单独放在一个文件里.
配置完成后点击 ”Run”, 然后就可以在 SQL Profiler 中查看SQL 的执行计划, 并且还可以单独保存成一个文件作为以后分析的依据. |
|
来自: icecity1306 > 《数据库资料》