Struggling to create an efficient index for this query
Clash Royale CLAN TAG#URR8PPP
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
sql-server query-performance index-tuning sql-server-2017
add a comment |
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
sql-server query-performance index-tuning sql-server-2017
How hard is the "no change in the query" restriction? Can you remove the hints?
– ypercubeᵀᴹ
Feb 6 at 9:59
2
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♦
Feb 6 at 10:59
add a comment |
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
sql-server query-performance index-tuning sql-server-2017
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
sql-server query-performance index-tuning sql-server-2017
sql-server query-performance index-tuning sql-server-2017
edited Feb 6 at 10:57
Paul White♦
52.7k14281456
52.7k14281456
asked Feb 6 at 7:03
BainAnatorBainAnator
214
214
How hard is the "no change in the query" restriction? Can you remove the hints?
– ypercubeᵀᴹ
Feb 6 at 9:59
2
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♦
Feb 6 at 10:59
add a comment |
How hard is the "no change in the query" restriction? Can you remove the hints?
– ypercubeᵀᴹ
Feb 6 at 9:59
2
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♦
Feb 6 at 10:59
How hard is the "no change in the query" restriction? Can you remove the hints?
– ypercubeᵀᴹ
Feb 6 at 9:59
How hard is the "no change in the query" restriction? Can you remove the hints?
– ypercubeᵀᴹ
Feb 6 at 9:59
2
2
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♦
Feb 6 at 10: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♦
Feb 6 at 10:59
add a comment |
3 Answers
3
active
oldest
votes
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
add a comment |
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
Feb 6 at 8:16
add a comment |
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.
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228990%2fstruggling-to-create-an-efficient-index-for-this-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
add a comment |
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
add a comment |
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
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
edited Feb 6 at 11:21
answered Feb 6 at 10:34
Randi VertongenRandi Vertongen
2,993721
2,993721
add a comment |
add a comment |
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
Feb 6 at 8:16
add a comment |
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
Feb 6 at 8:16
add a comment |
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.
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.
edited Feb 6 at 11:15
answered Feb 6 at 8:04
Rob FarleyRob Farley
13.9k12549
13.9k12549
Unfortunately neither of the suggested indexes were used after creating them.
– BainAnator
Feb 6 at 8:16
add a comment |
Unfortunately neither of the suggested indexes were used after creating them.
– BainAnator
Feb 6 at 8:16
Unfortunately neither of the suggested indexes were used after creating them.
– BainAnator
Feb 6 at 8:16
Unfortunately neither of the suggested indexes were used after creating them.
– BainAnator
Feb 6 at 8:16
add a comment |
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.
add a comment |
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.
add a comment |
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.
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.
answered Feb 6 at 9:01
Denis RubashkinDenis Rubashkin
5587
5587
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228990%2fstruggling-to-create-an-efficient-index-for-this-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
How hard is the "no change in the query" restriction? Can you remove the hints?
– ypercubeᵀᴹ
Feb 6 at 9:59
2
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♦
Feb 6 at 10:59