努力為此查詢創建有效的索引

我正在努力為查詢創建索引，不幸的是我根本無法更改查詢，因為它是 ERP 系統的一部分。問題是這個查詢有超過 1m 的讀取，有時持續 10s

我試過只使用謂詞、搜尋謂詞和兩者的組合以不同的順序沒有成功。

這是無法更改的查詢：

SELECT  TOP (@0) "timestamp","Link ID","Record ID","URL1","URL2","URL3","URL4","Description","Type","Created","User ID","Company","Notify","To User ID" 
FROM "Database".dbo."Record Link" WITH(READUNCOMMITTED)  
WHERE ("Link ID">@1 AND (("Company"=@2 OR "Company"=@3) AND "Notify"=@4 AND ("To User ID" COLLATE Latin1_General_100_CI_AI LIKE @5 OR "To User ID" COLLATE Latin1_General_100_CI_AI LIKE @6))) 
ORDER BY "Link ID" ASC OPTION(OPTIMIZE for UNKNOWN, FAST 50)

如果有幫助，這是表格的腳本：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Record Link](
   [timestamp] [timestamp] NOT NULL,
   [Link ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
   [Record ID] [varbinary](448) NOT NULL,
   [URL1] [nvarchar](250) NOT NULL,
   [URL2] [nvarchar](250) NOT NULL,
   [URL3] [nvarchar](250) NOT NULL,
   [URL4] [nvarchar](250) NOT NULL,
   [Description] [nvarchar](250) NOT NULL,
   [Type] [int] NOT NULL,
   [Note] [image] NULL,
   [Created] [datetime] NOT NULL,
   [User ID] [nvarchar](132) NOT NULL,
   [Company] [nvarchar](30) NOT NULL,
   [Notify] [tinyint] NOT NULL,
   [To User ID] [nvarchar](132) NOT NULL,
CONSTRAINT [Record Link$0] PRIMARY KEY CLUSTERED 
(
   [Link ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

這是時間和 IO 的統計數據

 SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 4 ms.

(0 rows affected)
Table 'Record Link'. Scan count 1, logical reads 1018402, physical reads 3, read-ahead reads 1018391, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

SQL Server Execution Times:
  CPU time = 8234 ms,  elapsed time = 12641 ms.

這是執行計劃：

如果有人可以幫助我為此創建一個有效的索引，我將不勝感激。

這是執行計劃的連結： https ://www.brentozar.com/pastetheplan/?id=BkTxCbdN4

如果您像@DenisRubashkin 提到的那樣去哪裡計劃指南的路線，您可以創建一個“空”計劃指南來刪除現有的提示：OPTION(OPTIMIZE for UNKNOWN, FAST 50).

您可以使用的計劃指南範例，您可能需要更改某些數據類型。

EXEC sp_create_plan_guide   
   @name =  N'Guide1',  
   @stmt = N'SELECT  TOP (@0) "timestamp","Link ID","Record ID","URL1","URL2","URL3","URL4","Description","Type","Created","User ID","Company","Notify","To User ID" 
FROM test.dbo."Record Link" WITH(READUNCOMMITTED)  
WHERE ("Link ID">@1 AND (("Company"=@2 OR "Company"=@3) AND "Notify"=@4 AND ("To User ID" COLLATE Latin1_General_100_CI_AI LIKE @5 OR "To User ID" COLLATE Latin1_General_100_CI_AI LIKE @6))) 
ORDER BY "Link ID" ASC OPTION(OPTIMIZE for UNKNOWN, FAST 50)',  
   @type = N'SQL',  
   @params = '@0 int, @1 int, @2  [nvarchar](30), @3  [nvarchar](30), @4 tinyint, @5 nvarchar(264),@6 nvarchar(264)',  
   @hints = NULL;

@hints 將覆蓋現有提示，不指定任何提示，OPTION(OPTIMIZE for UNKNOWN, FAST 50)刪除並且不使用其他提示。

在我的測試中，雖然我的數據不同，但使用了這個索引

CREATE INDEX IX_Notify_Company_Link_ID
ON [dbo].[Record Link](Notify,Company,[Link ID])
INCLUDE([To User ID]);

這可能不是您數據集的最佳索引！僅用作表明計劃指南有效的一種方式。YMMV

一個搜尋謂詞在這兩者上Notify以及Company在哪裡使用。它們在我的數據集中不是很有選擇性，導致讀取了很多行。

新計劃的一部分

粘貼計劃

@PaulWhite 發表評論

您可能會在計劃指南中發現使用 OPTION (RECOMPILE) 進行參數嵌入的額外好處。

您必須將計劃指南中的 @hints 參數更改為

   @hints = 'OPTION(RECOMPILE)';

請記住，您的查詢計劃每次都會重新編譯，但如果不經常執行它應該不是問題。

帶有選項重新編譯的計劃

使用了更好的計劃，在所有 3 個關鍵列上都使用了搜尋謂詞。

粘貼計劃

使用的測試查詢

SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL N'SELECT  TOP (@0) "timestamp","Link ID","Record ID","URL1","URL2","URL3","URL4","Description","Type","Created","User ID","Company","Notify","To User ID" 
FROM test.dbo."Record Link" WITH(READUNCOMMITTED)  
WHERE ("Link ID">@1 AND (("Company"=@2 OR "Company"=@3) AND "Notify"=@4 AND ("To User ID" COLLATE Latin1_General_100_CI_AI LIKE @5 OR "To User ID" COLLATE Latin1_General_100_CI_AI LIKE @6))) 
ORDER BY "Link ID" ASC OPTION(OPTIMIZE for UNKNOWN, FAST 50)',N'@0 int, @1 int, @2  [nvarchar](30), @3  [nvarchar](30), @4 tinyint, @5 nvarchar(264),@6 nvarchar(264)',@0=100,@1 = 99 ,@2  = 'NNNNNNNV',@3 = 'NNNNNNN',@4=1,@5='NNNNNNN1',@6='NNNNNNN2'

使用的測試數據

CREATE TABLE [dbo].[Record Link](
   [timestamp] [timestamp] NOT NULL,
   [Link ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
   [Record ID] [varbinary](448) NOT NULL,
   [URL1] [nvarchar](250) NOT NULL,
   [URL2] [nvarchar](250) NOT NULL,
   [URL3] [nvarchar](250) NOT NULL,
   [URL4] [nvarchar](250) NOT NULL,
   [Description] [nvarchar](250) NOT NULL,
   [Type] [int] NOT NULL,
   [Note] [image] NULL,
   [Created] [datetime] NOT NULL,
   [User ID] [nvarchar](132) NOT NULL,
   [Company] [nvarchar](30) NOT NULL,
   [Notify] [tinyint] NOT NULL,
   [To User ID] [nvarchar](132) NOT NULL,
CONSTRAINT [Record Link$0] PRIMARY KEY CLUSTERED 
(
   [Link ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET NOCOUNT ON;
DECLARE @I INT = 0;
WHILE @I <= 10000
BEGIN
INSERT INTO [dbo].[Record Link]
(       

   [Record ID]   ,
   [URL1]    ,
   [URL2]    ,
   [URL3]    ,
   [URL4]    ,
   [Description]    ,
   [Type]  ,
   [Note]  ,
   [Created]   ,
   [User ID]   ,
   [Company]   ,
   [Notify]   ,
   [To User ID]   )
   VALUES(convert(varbinary(448),'NNNNNNN'),'NNNNNNN','NNNNNNN','NNNNNNN','NNNNNNN','NNNNNNN',@I,'NNNNNNN',GETDATE(),'NNNNNNN'+CAST(@i as nvarchar(10)),'NNNNNNN',1,'NNNNNNN'+CAST(@i as nvarchar(10)))
   SET @I += 1
   END
   SET NOCOUNT OFF
INSERT INTO [dbo].[Record Link]
(       

   [Record ID]   ,
   [URL1]    ,
   [URL2]    ,
   [URL3]    ,
   [URL4]    ,
   [Description]    ,
   [Type]  ,
   [Note]  ,
   [Created]   ,
   [User ID]   ,
   [Company]   ,
   [Notify]   ,
   [To User ID]   )

   SELECT
    [Record ID]   ,
    [URL1] ,
    [URL2]    ,
    [URL3]    ,
    [URL4]    ,
    [Description] , 
    [Type]  ,
    [Note]  ,
    [Created]   ,
    [User ID]   ,
    [Company]   ,
    [Notify]   ,
    [To User ID]   
    FROM   [dbo].[Record Link]
    GO 7

引用自：https://dba.stackexchange.com/questions/228990