with cte(guid,other) as (
select newid(),1 union all
select newid(),2 union all
select newid(),3)
select a.guid, a.other, b.guid guidb, b.other otherb
from cte a
cross join cte b
order by a.other, b.other;
创建一个计划指南来缓存（延迟读取）CTE结果
- 孔夫子
2
@Quassnoi 在这里博客上介绍了如何使用计划指南。 - Martin Smith
在研究过程中，我在发布前后已经访问了那个链接两次，并且很久以前我已经看过它。我有一个将GUID缩减到6位的计划，但我的目标是3位。 - 孔夫子
5个回答
LogOp_OrderByCOL: Union1007 ASC COL: Union1015 ASC
LogOp_Project COL: Union1006 COL: Union1007 COL: Union1014 COL: Union1015
LogOp_Join
LogOp_ViewAnchor
LogOp_UnionAll
LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const
LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const
LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const
LogOp_ViewAnchor
LogOp_UnionAll
LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const
LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const
LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const
更通用的实现将使优化器考虑将任意常见表达式具现化以提高性能（CASE与子查询是当前可能出现问题的另一个示例）。 Microsoft Research 在2007年发表了一篇论文（PDF），但至今未实施。 目前，我们只能通过使用诸如表变量和临时表之类的显式具现化。
这只是我一厢情愿的想法，远远超出了修改计划指南的概念。原则上可以编写一个直接操作显示计划 XML 的工具，但是如果没有特定的优化器仪表盘，使用该工具可能会让用户（以及开发人员）感到沮丧。 在这个问题的特定背景下，这样的工具仍然无法以能够被多个消费者使用的方式实现CTE（通用表达式）内容（例如，在这种情况下为交叉连接提供输入）。优化器和执行引擎确实支持多消费者 spool，但仅用于特定目的，其中没有一个可以应用于此特定示例。SQLKiwi曾经提到在SSIS中制定计划，有没有办法或有用的工具来协助为SQL Server制定良好的计划？
这里有相当的灵活性。XML计划的大致形状用于指导最终计划的搜索（尽管许多属性完全被忽略，例如交换的分区类型），并且正常的搜索规则也得到了相当程度的放宽。例如，基于成本考虑的早期替代方案修剪被禁用，允许显式引入交叉连接，并且标量操作被忽略。 有太多细节需要深入讨论，但是无法强制执行过滤器和计算标量的位置，并且形如“column = value”的谓词被泛化，因此包含“X = 1”或“X = @X”的计划可以应用于包含“X = 502”或“X = @Y”的查询。这种特定的灵活性在找到强制计划时非常有帮助。 在具体的例子中，常数Union All总是可以作为常数扫描来实现；Union All的输入数量并不重要。虽然我不确定，但我有很强的预感，即使查询与计划不完全相同，关系运算符（Nested Loop，Lazy Spool）也可以跟踪 - 例如，如果您向CTE添加4和5，它仍然继续使用相同的计划（经过测试，在SQL Server 2012 RTM Express 上）。
- Paul White
在 SQL Server 2012 及之前的版本中，无法重复使用单个 spool 来处理 CTE 的两个实例。有关详细信息，请参见 SQLKiwi 的答案。下面进一步介绍了两种材料化 CTE 两次的方法，这是查询性质所不可避免的。这两种选项都会导致净唯一 guid 计数为 6。
Martin 的评论中链接到 Quassnoi 网站上关于计划指导 CTE 的博客部分启发了这个问题。它描述了一种材料化 CTE 的方法，用于相关子查询，尽管相关性可能导致其被多次评估，但仅引用一次。这不适用于问题中的查询。
选项 1 - 计划指南
借鉴 SQLKiwi 的答案，我将指南简化到最少，仍然可以完成工作，例如 ConstantScan 节点仅列出 2 个标量运算符，可以足够扩展到任意数量。
;with cte(guid,other) as (
select newid(),1 union all
select newid(),2 union all
select newid(),3)
select a.guid, a.other, b.guid guidb, b.other otherb
from cte a
cross join cte b
order by a.other, b.other
OPTION(USE PLAN
N'<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.2100.60" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1600" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0444433" StatementText="with cte(guid,other) as (
 select newid(),1 union all
 select newid(),2 union all
 select newid(),3
select a.guid, a.other, b.guid guidb, b.other otherb
from cte a
cross join cte b
order by a.other, b.other;
" StatementType="SELECT" QueryHash="0x43D93EF17C8E55DD" QueryPlanHash="0xF8E3B336792D84" RetrievedFromCache="true">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan NonParallelPlanReason="EstimatedDOPIsOne" CachedPlanSize="96" CompileTime="13" CompileCPU="13" CompileMemory="1152">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="157240" EstimatedPagesCached="1420" EstimatedAvailableDegreeOfParallelism="1" />
<RelOp AvgRowSize="47" EstimateCPU="0.006688" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1600" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0444433">
<OutputList>
<ColumnReference Column="Union1163" />
</OutputList>
<Warnings NoJoinPredicate="true" />
<NestedLoops Optimized="false">
<RelOp AvgRowSize="27" EstimateCPU="0.000432115" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="40" LogicalOp="Sort" NodeId="1" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0117335">
<OutputList>
<ColumnReference Column="Union1080" />
<ColumnReference Column="Union1081" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Column="Union1081" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="27" EstimateCPU="4.0157E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="40" LogicalOp="Constant Scan" NodeId="2" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="4.0157E-05">
<OutputList>
<ColumnReference Column="Union1080" />
<ColumnReference Column="Union1081" />
</OutputList>
<ConstantScan>
<Values>
<Row>
<ScalarOperator ScalarString="newid()">
<Intrinsic FunctionName="newid" />
</ScalarOperator>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</Row>
<Row>
<ScalarOperator ScalarString="newid()">
<Intrinsic FunctionName="newid" />
</ScalarOperator>
<ScalarOperator ScalarString="(2)">
<Const ConstValue="(2)" />
</ScalarOperator>
</Row>
</Values>
</ConstantScan>
</RelOp>
</Sort>
</RelOp>
<RelOp AvgRowSize="27" EstimateCPU="0.0001074" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="39" EstimatedExecutionMode="Row" EstimateRows="40" LogicalOp="Lazy Spool" NodeId="83" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0260217">
<OutputList>
<ColumnReference Column="Union1162" />
<ColumnReference Column="Union1163" />
</OutputList>
<Spool>
<RelOp AvgRowSize="27" EstimateCPU="0.000432115" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="40" LogicalOp="Sort" NodeId="84" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0117335">
<OutputList>
<ColumnReference Column="Union1162" />
<ColumnReference Column="Union1163" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Column="Union1163" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="27" EstimateCPU="4.0157E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="40" LogicalOp="Constant Scan" NodeId="85" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="4.0157E-05">
<OutputList>
<ColumnReference Column="Union1162" />
<ColumnReference Column="Union1163" />
</OutputList>
<ConstantScan>
<Values>
<Row>
<ScalarOperator ScalarString="newid()">
<Intrinsic FunctionName="newid" />
</ScalarOperator>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</Row>
<Row>
<ScalarOperator ScalarString="newid()">
<Intrinsic FunctionName="newid" />
</ScalarOperator>
<ScalarOperator ScalarString="(2)">
<Const ConstValue="(2)" />
</ScalarOperator>
</Row>
</Values>
</ConstantScan>
</RelOp>
</Sort>
</RelOp>
</Spool>
</RelOp>
</NestedLoops>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>'
);
选项2 - 远程扫描
通过增加查询的开销并引入远程扫描，结果被实现。
with cte(guid,other) as (
select *
from OPENQUERY([TESTSQL\V2012], '
select newid(),1 union all
select newid(),2 union all
select newid(),3') x)
select a.guid, a.other, b.guid guidb, b.other otherb
from cte a
cross join cte b
order by a.other, b.other;
- 孔夫子
UPDATE STATISTICS
[dbo].[yourTable]([PK_yourTable])
WITH
STATS_STREAM = 0x0100etc,
ROWCOUNT = 10000,
PAGECOUNT = 93
- wBob
2
真的吗，XML计划可以被忽略？！我以为这正是它的全部意义所在呢？如果它们无效，应该会抛出异常才对。 - crokusek
我指的是计划指南不成功的事件。 - wBob
-- Configure the XEvents session; with ring buffer target so we can collect it
CREATE EVENT SESSION [query_trace_column_values] ON SERVER
ADD EVENT sqlserver.query_trace_column_values
ADD TARGET package0.ring_buffer( SET max_memory = 2048 )
WITH ( MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF , STARTUP_STATE = OFF )
GO
-- Start the session
ALTER EVENT SESSION [query_trace_column_values] ON SERVER
STATE = START;
GO
-- Run the query, including traceflag
DBCC TRACEON(2486);
SET STATISTICS XML ON;
GO
-- Original query
;with cte(guid,other) as (
select newid(),1 union all
select newid(),2 union all
select newid(),3)
select a.guid, a.other, b.guid guidb, b.other otherb
from cte a
cross join cte b
order by a.other, b.other
option ( recompile )
go
SET STATISTICS XML OFF;
DBCC TRACEOFF(2486);
GO
DECLARE @target_data XML
SELECT @target_data = CAST( target_data AS XML )
FROM sys.dm_xe_sessions AS s
INNER JOIN sys.dm_xe_session_targets AS t ON t.event_session_address = s.address
WHERE s.name = 'query_trace_column_values'
--SELECT @target_data td
-- Arbitrarily fish out 3 unique guids from intermediate stage of the query as collected by XEvent session
;WITH cte AS
(
SELECT
n.c.value('(data[@name = "row_id"]/value/text())[1]', 'int') row_id,
n.c.value('(data[@name = "column_value"]/value/text())[1]', 'char(36)') [guid]
FROM @target_data.nodes('//event[data[@name="column_id"]/value[. = 1]][data[@name="row_number"]/value[. < 4]][data[@name="node_name"]/value[. = "Nested Loops"]]') n(c)
)
SELECT *
FROM cte a
CROSS JOIN cte b
GO
-- Stop the session
ALTER EVENT SESSION [query_trace_column_values] ON SERVER
STATE = STOP;
GO
-- Drop the session
IF EXISTS ( select * from sys.server_event_sessions where name = 'query_trace_column_values' )
DROP EVENT SESSION [query_trace_column_values] ON SERVER
GO
- wBob
with cte(guid,other) as (
select newid(),1 union all
select newid(),2 union all
select newid(),3)
select a.guid, a.other, b.guid guidb, b.other otherb
from cte a
cross join cte b
order by a.other, b.other
option ( querytraceon 8649 )
- wBob
4
谢谢你再次尝试。无论是否在2008/2012上启用了该跟踪标志，查询看起来都没有任何不同。我真的不确定是我的SQL Server实例还是你想要展示的内容有问题。我仍然看到18个GUID。你看到了什么？ - 孔夫子
左侧有3个不同的guid（guid列），每个重复三次。右侧有9个唯一的guid（guidb列），所以至少左侧已经按您的要求运作了，哈哈。我已经在另一个答案中添加了一张图片，希望能更清楚一些。小步骤。我还应该注意，在SQL 2005中，我获得了6个唯一的guid，左侧3个，右侧3个。 - wBob
还注意到，删除“全部”也可以得到6个唯一的GUID，每边3个。 - wBob
通过将服务器的 maxdop 设置为 1，可以使跟踪标志 _不起作用_。 - wBob
- 相关问题
- 7 过程缓存（计划缓存）大小