我有以下形式的查询:
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
恢复“错误”计划结合起来。