sqlserver2005:T-Sql查询之物理查询处理
1.SqlServer执行两个主要的步骤来产生期望的查询结果:查询编译(query compilation)---生成查询计划和执行查询计划。
2.SqlServer2005中查询编译由三个步骤组成:分析、代数化以及查询优化,完成这些步骤后编译器把经过优化的查询计划保存到过程缓存(procedure cache)
3.编译过程
首先执行分析、绑定(聚合绑定和分组绑定)、优化(细微计划优化;简化(阶段0、阶段1、阶段2))
4.动态管理视图 sys.dm_exec_query_optimizer_info 提供了从SqlServer启动以来执行的所有优化的累积信息
5. 清空过程缓存 DBCC FREEPROCCACHE 删除过程缓存的内容
sys.dm_exec_query_optimizer_info SET NOCOUNT ON; USE Northwind; -- use your database name here DBCC FREEPROCCACHE; -- empty the procedure cache GO -- we will use tempdb..OptStats table to capture -- the information from several executions -- of sys.dm_exec_query_optimizer_info IF (OBJECT_ID('tempdb..OptStats') IS NOT NULL) DROP TABLE tempdb..OptStats; GO -- the purpose of this statement is -- to create the temporary table tempdb..OptStats SELECT 0 AS Run, * INTO tempdb..OptStats FROM sys.dm_exec_query_optimizer_info; GO -- this will populate the procedure cache -- with this statement's plan so that it will not -- generate any optimizer events when executed -- next time -- the following GO is intentional to ensure -- the query plan reuse will happen for the following -- INSERT for its next invocation in this script GO INSERT INTO tempdb..OptStats SELECT 1 AS Run, * FROM sys.dm_exec_query_optimizer_info; GO -- same reason as above; observe the "2" replaced "1" -- therefore we will have different plan GO INSERT INTO tempdb..OptStats SELECT 2 AS Run, * FROM sys.dm_exec_query_optimizer_info; GO -- empty the temporary table TRUNCATE TABLE tempdb..OptStats GO -- store the "before run" information -- in the temporary table with the output -- of sys.dm_exec_query_optimizer_info -- with value "1" in the column Run GO INSERT INTO tempdb..OptStats SELECT 1 AS Run, * FROM sys.dm_exec_query_optimizer_info; GO -- your statement or batch is executed here /*** the following is an example***/ SELECT C.CustomerID, COUNT(O.OrderID) AS NumOrders FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O ON C.CustomerID = O.CustomerID WHERE C.City = 'London' GROUP BY C.CustomerID HAVING COUNT(O.OrderID) > 5 ORDER BY NumOrders; GO -- store the "after run" information -- in the temporary table with the output -- of sys.dm_exec_query_optimizer_info -- with value "2" in the column Run GO INSERT INTO tempdb..OptStats SELECT 2 AS Run, * FROM sys.dm_exec_query_optimizer_info; GO -- extract all "events" that changed either -- the Occurrence or Value column value between -- the Runs 1 and 2 from the temporary table. -- Display the values of Occurrence and Value -- for all such events before (Run1Occurrence and -- Run1Value) and after (Run2Occurrence and -- Run2Value) executing your batch or query. -- This is the result set generated by the script. WITH X (Run,Counter, Occurrence, Value) AS ( SELECT * FROM tempdb..OptStats WHERE Run=1 ), Y (Run,Counter, Occurrence, Value) AS ( SELECT * FROM tempdb..OptStats WHERE Run=2 ) SELECT X.Counter, Y.Occurrence-X.Occurrence AS Occurrence, CASE (Y.Occurrence-X.Occurrence) WHEN 0 THEN (Y.Value*Y.Occurrence-X.Value*X.Occurrence) ELSE (Y.Value*Y.Occurrence-X.Value*X.Occurrence)/(Y.Occurrence-X.Occurrence) END AS Value FROM X JOIN Y ON (X.Counter=Y.Counter AND (X.Occurrence<>Y.Occurrence OR X.Value<>Y.Value)); GO -- drop the temporary table DROP TABLE tempdb..OptStats; GO
6.使用查询计划
Sqlserver2005 可以生产三种不同格式的显示计划:图形、文本和XML,`下图为生成不同格式的计划的命令:
|
文本 | XML | 图形 |
运算符 |
SET SHOWPLAN_TEXT ON |
N/A |
N/A |
运算符和估计成本 |
SET SHOWPLAN_ALL ON |
SET SHOWPLAN_XML ON |
在SSMS中显示估计的执行计划 |
运行时信息 |
SET STATISTICS PROFILE ON |
SET STATISTICS XML ON |
在SSMS中显示实际的执行计划 |
|
SET SHOWPLAN_TEXT ON和SET SHOWPLAN_ALL ON
当执行计划时,数据的传递通常是从上到下从右到左的。缩进多的运算符生成的行供缩进少的运算符使用,后者再生成由下一级运算符使用的行,以此类推。
XML格式的显示计划
有两种XML格式的显示计划。一种是通过 SET SHOWPLAN_XML ON(编译批处理时生成的,为整个批处理生成一个XML文档)得到的,包含估计的执行计划,另一种是有SET STATISTICS XML ON (运行时产生的,为批处理中的每个语句生成单独的XML文档)得到的,包含运行时信息。
还有两种方法可以得到XML格式的计划:保存SSMS中显示的图形化显示计划;使用SQL Server Profiler。
SET STATISTICS XML ON/OFF
SET STATISTICS PROFILE ON/OFF
用SQL跟踪捕获显示计划
从过程缓存中提取显示计划
几个动态管理视图和函数、DBCC PROCCACHE、以及目录视图sys.syscacheobjects (不推荐)
sys.dm_exec_query_plan DMF以XML格式返回位于过程缓存的计划,其要求一个计划句柄作文唯一的参数,计划句柄是一个VARBINARY(64)类型的查询计划标识符,sys.dm_exec_query_stats DMF为当前过程缓存中的每个查询返回改标识符。‘
下面从查询为所有缓存的查询计划返回XML显示计划,如果批处理或存储过程包含多条SQL语句,则该视图将为每条语句包含一行:
select qplan.query_plan as a
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qplan
更新计划
IUD计划包括2个阶段,第一阶段是读取数据,它通过生成用于描述更改的数据流来确定哪些行将被插入、更新、删除;第二个阶段把数据流中的更改应用到表。
查询优化器执行IUD语句时有两种不同的策略:每行维护(每一行同时维护索引和基表);每索引维护
spool运算符提供Halloween保护