Início / user-114315

Dan Def's questions

Martin Hope
Dan Def
Asked: 2024-05-14 16:09:54 +0800 CST
  • 9

Tenho uma consulta no seguinte formato:

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)
            )
)
...

A definição de 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

A definição de @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

A tabela MyTablecontém aproximadamente 500 mil linhas e possui um índice filtrado exclusivo que possui:

  • WTID, OETIDe SRIDcomo chaves nessa ordem
  • um filtro onde SRID> 0
  • Statuscomo uma coluna incluída

Isso significa que a EXISTSinstrução é SARGable.

No entanto, dependendo de quantos registros estão presentes @InMemoryTableTypeTablee do humor que o SQL Server parece estar, às vezes a busca do índice apenas buscará WTIDe empurrará o restante da predicação para o Left Anti Semi Join. Se isso acontecer e a memória do próprio SQL Server estiver sob pressão, a consulta poderá permanecer lá por cerca de 20 minutos. Para alguns valores, @WTIDpode haver 1 linha ou 200k que acabaram de ser inseridos anteriormente na mesma sessão.

Aqui está o bom plano: https://www.brentozar.com/pastetheplan/?id=H1-V_Jz7R

Aqui está o plano ruim: https://www.brentozar.com/pastetheplan/?id=SJD-QZGQA

Existe uma maneira de forçar o SQL Server a aplicar a predicação a todas as três colunas na busca de índice sempre?

Eu tentei quebrar isso do IF e usar as dicas OPTIMIZE FOR UNKNOWNe OPTIMIZE FOR (@WTID UNKNOWN)sem sucesso.

A busca é mais por simultaneidade: as leituras e gravações nessa tabela para cada sessão serão segregadas pelo WTID. No entanto, remover essas dicas da tabela não faz diferença, ele sempre verifica t e procura m, é a posição da predicação OETID e SRID que parece fazer a diferença.

Esta postagem As linhas reais e estimadas diferem muito e me levou à ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATESdica que produz o plano que desejo (na maioria das vezes) junto com RECOMPILE. Combinar isso com FORCE_LEGACY_CARDINALITY_ESTIMATIONreverte para o plano “errado”.

sql-server