Struggling to create an efficient index for this query

Question

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.

Here is the execution plan:

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

How hard is the "no change in the query" restriction? Can you remove the hints? — ypercubeᵀᴹ, Commented Feb 6, 2019 at 9:59
As a side note, you really ought to not be still on 2017 RTM - there have been 13 cumulative updates since then. You could also help people reproduce the problem by scripting out indexes and statistics and adding that to your question. — Paul White, Commented Feb 6, 2019 at 10:59

Randi Vertongen · Accepted Answer · 2019-02-06 11:21:31Z

If you where to go the route of plan guides as @DenisRubashkin mentioned, you could create an 'empty' plan guide to remove the existing hints: OPTION(OPTIMIZE for UNKNOWN, FAST 50).

An example of a plan guide you could use, you might have to change some datatypes.

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 will override the existing hints, by not specifying any, OPTION(OPTIMIZE for UNKNOWN, FAST 50) is removed and no other hints are used.

In my tests, while my data is different, this index was used

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

This could not be the most optimal index for your dataset! Used solely as a way to show that the plan guide works. YMMV

A seek predicate on both Notify and Company where used. They are not very selective in my dataset, resulting in many rows read.

Part of the new plan

PasteThePlan

Comment by @PaulWhite

You might find an addition benefit in parameter embedding using OPTION (RECOMPILE) in the plan guide.

You would have to change the @hints parameter in the plan guide to

    @hints = 'OPTION(RECOMPILE)';

Keep in mind that your query plan will be recompiled each time, but if it is not executed that frequently it should not be a problem.

Plan with option recompile

A better plan was used, with seek predicates on all 3 key colums.

PasteThePlan

Test Query used

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'

Test Data used

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

Rob Farley · Accepted Answer · 2019-02-06 11:15:22Z

As you have exactly two Companies it could be, I’d go with an index on (Company, Notify, [Link ID]) INCLUDE ([To User ID]). Having Link ID in the key is redundant because of the clustered index key, but I’d leave it in there because of the explicit sorting.

Hopefully this will produce a plan that has two Index Seeks, with a Merge Join (Concat) operator that’s pulling the data from them.

You may need to INCLUDE ([To User ID], "timestamp","Record ID","URL1","URL2","URL3","URL4","Description","Type","Created","User ID") to avoid the tipping point of lookups, but see how you go.

Edited:

I used this test data:

INSERT INTO [dbo].[Record Link]
(       

    [Record ID]   ,
    [URL1]    ,
    [URL2]    ,
    [URL3]    ,
    [URL4]    ,
    [Description]    ,
    [Type]  ,
    [Note]  ,
    [Created]   ,
    [User ID]   ,
    [Company]   ,
    [Notify]   ,
    [To User ID]   )
    VALUES(newid(), newid(), newid(),newid(), newid(), newid(),abs(checksum(newid())) % 100, cast(newid() as varbinary(max)), getdate(), newid(), checksum(newid()), abs(checksum(newid())) % 255,newid())
go 10000


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 [record link]
go 5

...and then my index:

create index ixTest on [Record Link] (Company, Notify, [Link ID])  include ([To User ID], "timestamp","Record ID","URL1","URL2","URL3","URL4","Description","Type","Created","User ID")

And then I queried the data to get some values that would work:

declare @0 int = 50;
declare @1 int = 3000;
declare @2 nvarchar(30) = N'473788597'
declare @3 nvarchar(30) = N'508414347'
declare @4 tinyint = 100;
declare @5 nvarchar(132) = N'E3CB6DFC-8311-4DB4-9735-F2AF90747DCF'
declare @6 nvarchar(132) = N'Test2'

SELECT  TOP (@0) "timestamp","Link ID","Record ID","URL1","URL2","URL3","URL4","Description","Type","Created","User ID","Company","Notify","To User ID" 
FROM 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)

...and I got the following plan:

It basically does a Nested Loop on the two companies. It's not quite as good as I'd like, because there's still a Sort, but it definitely uses the index.

Unfortunately neither of the suggested indexes were used after creating them. — BainAnator, Commented Feb 6, 2019 at 8:16

Denis Rubashkin · Accepted Answer · 2019-02-06 09:01:53Z

I don't think any index will be used instead of the clustered one because of using the OPTIMIZE for UNKNOWN option. The option makes the query optimizer use statistics' density instead of its histogram so estimated cardinality will always be big enough so that the optimizer will deside that scanning of the clustered index will be cheaper than such a big number of Key lookup operations.

If you can't change the query on the client side, maybe using of Plan Guides is your last resort.

Stack Exchange Network

Struggling to create an efficient index for this query

3 Answers 3

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
performance
index-tuning
sql-server-2017
query-performance
or ask your own question.

Linked

Hot Network Questions

Struggling to create an efficient index for this query

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serverperformanceindex-tuningsql-server-2017query-performance or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
performance
index-tuning
sql-server-2017
query-performance
or ask your own question.