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保护