I am struggling to create an index for a query and unfortunately I cannot change the query at all as it part of an ERP system. The problem is this query has over 1m reads and sometimes a duration of 10s
I Have tried using just the predicate, the seek predicate and a combination of both in different orders to no success.
Here is the query that cannot be changed:
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)
Here is the script for the table if it helps:
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
Here is the stats for the time and 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.
I would greatly appreciate it if someone could help me create an efficient index for this.
Here is a link to the execution plan: https://www.brentozar.com/pastetheplan/?id=BkTxCbdN4