我有以下形式的查询:
IF EXISTS (
SELECT 1
FROM (
SELECT RowID, OETID
FROM @InMemoryTableTypeTable i
UNION
SELECT RowID, OETID
FROM @InMemoryTableTypeTable d
) AS t
WHERE NOT EXISTS (
SELECT 1
FROM dbo.MyTable m WITH(FORCESEEK, ROWLOCK, UPDLOCK)
WHERE (m.OETID = t.RowID)
AND (m.SRID = t.OETID)
AND (m.WTID = @WTID)
AND (m.Status <> 1)
AND (m.SRID > 0)
)
)
...
的定义dbo.MyTable
是:
CREATE TABLE [dbo].[MyTable](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[RowGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[WTID] [bigint] NOT NULL,
[OETID] [int] NOT NULL,
[SRID] [bigint] NOT NULL,
[Status] [tinyint] NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IDX] ON [dbo].[MyTable]
(
[WTID] ASC,
[OETID] ASC,
[SRID] ASC
)
INCLUDE([Status])
WHERE ([SRID]>(0))
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, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_MyTable_RowGUID] DEFAULT (NEWID()) FOR [RowGUID]
GO
的定义@InMemoryTableTypeTable
是
CREATE TYPE [dbo].[TableType] AS TABLE(
[ID] [bigint] NOT NULL,
[RowID] [int] NOT NULL,
[OETID] [int] NOT NULL,
PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)
)
WITH ( MEMORY_OPTIMIZED = ON )
GO
该表MyTable
包含约 500k 行,并具有唯一的筛选索引,该索引具有:
WTID
,OETID
并按SRID
该顺序作为键- 过滤器,其中
SRID
> 0 Status
作为包含的列
这意味着该EXISTS
语句是可SARGable 的。
然而,根据有多少记录@InMemoryTableTypeTable
以及 SQL Server 似乎处于什么状态,有时索引查找只会继续查找WTID
并将其余谓词推入左反半连接。如果发生这种情况并且 SQL Server 本身的内存面临压力,则查询可能会等待 20 分钟左右。对于某些值,@WTID
可能有 1 行,也可能有 200k 行刚刚在同一会话中插入。
这是一个好的计划:https://www.brentozar.com/pastetheplan/?id=H1- V_Jz7R
这是糟糕的计划:https://www.brentozar.com/pastetheplan/? id=SJD-QZGQA
有没有办法强制 SQL Server 每次都将谓词应用于索引查找中的所有 3 列?
我尝试将其从 IF 中打破并使用OPTIMIZE FOR UNKNOWN
和OPTIMIZE FOR (@WTID UNKNOWN)
提示,但无济于事。
查找更多的是为了并发性:每个会话在该表中的读取和写入将由 WTID 分隔。然而,删除这些表提示没有什么区别,它总是扫描 t 并查找 m,OETID 和 SRID 谓词的位置似乎会产生差异。
这篇文章《实际行数和估计行数差异很大》让我得到了ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES
提示,该提示会生成我想要的计划(大多数时候)以及RECOMPILE
. 将此与FORCE_LEGACY_CARDINALITY_ESTIMATION
恢复“错误”计划结合起来。
解决方案
您可以将扩展
FORCESEEK
提示与所需的搜索键一起使用:例如:
解释
正如您所注意到的,原因是成本估算。由 引入的查询
EXISTS
带有行目标,这使事情变得复杂。再加上包含其他几个难以估计的功能的查询,在这种情况下可能需要提示才能一致地获得所需的计划形状。成本核算和勘探
“好”和“坏”计划的总估计成本都非常低,因此优化器不会花费太多时间进行搜索(请注意在根操作符处提前终止的“ Good Enough Plan Found”原因)。
优化器会考虑许多您在最终计划中未看到的替代方案。如果没有早期发现的低成本,它将继续考虑进一步的策略,如下图所示,将反半连接(作为apply)推到union之下:
无论如何,优化器并没有“被 迷惑
UNION
”——它只是没有考虑到连接APPLY
,而只是考虑连接(可以实现为嵌套循环、散列或合并)。嵌套循环连接计划确实在内侧有一个查找,但这是不相关的谓词
WTID = @WTID
,它也可以出现在散列或合并连接中。其余谓词都是相关的,因此它们需要应用来下推。如果其中的概念不清楚,请参阅我的文章“应用与嵌套循环连接” 。IF EXISTS
通常,您可以使用
OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'))
查询提示来禁用行目标行为,这也很可能可靠地生成所需的计划。不幸的是,查询提示仅适用于顶级查询(
IF EXISTS
此处),而不适用于嵌套查询(您关心的查询)。添加提示时您确实看到了不同的计划,但这是因为查询文本不同,因此需要重新编译。使用 时
IF EXISTS
,您需要设置记录的跟踪标志 4138来禁用行目标。它必须在会话级别设置(使用DBCC TRACEON
),因为QUERYTRACEON
也仅适用于顶层。您可以通过使用如下所示的模式来避免这种不明显的行为,而不是
IF EXISTS
(请参阅下面的相关问答)我提到所有这些都是为了兴趣。由于您已经使用了多个提示,因此扩展
FORCESEEK
是最佳选择。相关问答:
由于某些不清楚的原因,编译器似乎对 感到困惑,并且没有一路
UNION
推送反连接谓词,因此没有完全匹配索引。NOT EXISTS
以下几件事似乎没有什么区别:
RowId
是int
并且应该是bigint
(无论如何你都应该这样做)。UNION
为UNION ALL
(无论如何你都应该这样做)。EXCEPT
代替WHERE NOT EXISTS
.GROUP BY
获得统一的外部集。COUNT(*) = 0
。FORCESEEK
或INDEX
提示。我能看到的唯一解决方法是重复整个过程
WHERE NOT EXISTS
。你可以使用OR EXISTS
或者你可以使用UNION ALL
它。