Azure DB/MSSQL 2017 query performance regression
0
I have this pretty simple table with 17m records in it:
CREATE TABLE [dbo].[LineNumbers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LineDescriptionId] [int] NOT NULL,
[ProtocolId] [int] NULL,
[Value] [int] NULL,
CONSTRAINT [PK_LineNumbers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Query to the table with additional join works fine, if there is no ProtocolId in it:
select top 1
ln.LineDescriptionId
from LineNumbers ln
join LineDescriptions ld on ld.Id = ln.LineDescriptionId and ld.ProtocolSetId = 25
-- Elapsed time: 00:00:00.1718750
Execution plan: https://www.brentozar.com/pastetheplan/?id=rJV34gvR7
But when I try to add ProtocolId to the field list, query time grows dramatically:
select top 1
ln.ProtocolId
from LineNumbers ln
join LineDescriptions ld on ld.Id = ln.LineDescriptionId and ld.ProtocolSetId = 25
-- Elapsed time: 00:02:19.6464843
Execution plan: https://www.brentozar.com/pastetheplan/?id=SkG-hyDCQ
Also, this works smooth:
select top 1
(select ProtocolId from LineNumbers where LineNumbers.Id = ln.Id) as ProtocolId
from LineNumbers ln
join LineDescriptions ld on ld.Id = ln.LineDescriptionId and ld.ProtocolSetId = 25
-- Elapsed time: 00:00:00.1718750
Tried this queries and variations on Azure DB and local MSSQL 2017. Results are the same. As long as I keep ProtocolId out of the field list everything is fine.
Is there some mistake in my data scheme (everything was created via migrations of Entity Framework)?
CREATE TABLE [dbo].[LineNumbers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LineDescriptionId] [int] NOT NULL,
[ProtocolId] [int] NULL,
[Value] [int] NULL,
CONSTRAINT [PK_LineNumbers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_LineDescriptionId] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId] ON [dbo].[LineNumbers]
(
[LineDescriptionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_LineDescriptionId_Value] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId_Value] ON [dbo].[LineNumbers]
(
[LineDescriptionId] ASC,
[Value] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_ProtocolId] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_ProtocolId] ON [dbo].[LineNumbers]
(
[ProtocolId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LineNumbers] WITH NOCHECK ADD CONSTRAINT [FK_LineNumbers_LineDescriptions_LineDescriptionId] FOREIGN KEY([LineDescriptionId])
REFERENCES [dbo].[LineDescriptions] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[LineNumbers] CHECK CONSTRAINT [FK_LineNumbers_LineDescriptions_LineDescriptionId]
GO
ALTER TABLE [dbo].[LineNumbers] WITH NOCHECK ADD CONSTRAINT [FK_LineNumbers_Protocols_ProtocolId] FOREIGN KEY([ProtocolId])
REFERENCES [dbo].[Protocols] ([Id])
GO
ALTER TABLE [dbo].[LineNumbers] CHECK CONSTRAINT [FK_LineNumbers_Protocols_ProtocolId]
GOsql
sql-server database 
azure asked Nov 21 '18 at 8:31
Artem BaikuzinArtem Baikuzin
112
|
show 4 more comments
0
I have this pretty simple table with 17m records in it:
CREATE TABLE [dbo].[LineNumbers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LineDescriptionId] [int] NOT NULL,
[ProtocolId] [int] NULL,
[Value] [int] NULL,
CONSTRAINT [PK_LineNumbers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Query to the table with additional join works fine, if there is no ProtocolId in it:
select top 1
ln.LineDescriptionId
from LineNumbers ln
join LineDescriptions ld on ld.Id = ln.LineDescriptionId and ld.ProtocolSetId = 25
-- Elapsed time: 00:00:00.1718750
Execution plan: https://www.brentozar.com/pastetheplan/?id=rJV34gvR7
But when I try to add ProtocolId to the field list, query time grows dramatically:
select top 1
ln.ProtocolId
from LineNumbers ln
join LineDescriptions ld on ld.Id = ln.LineDescriptionId and ld.ProtocolSetId = 25
-- Elapsed time: 00:02:19.6464843
Execution plan: https://www.brentozar.com/pastetheplan/?id=SkG-hyDCQ
Also, this works smooth:
select top 1
(select ProtocolId from LineNumbers where LineNumbers.Id = ln.Id) as ProtocolId
from LineNumbers ln
join LineDescriptions ld on ld.Id = ln.LineDescriptionId and ld.ProtocolSetId = 25
-- Elapsed time: 00:00:00.1718750
Tried this queries and variations on Azure DB and local MSSQL 2017. Results are the same. As long as I keep ProtocolId out of the field list everything is fine.
Is there some mistake in my data scheme (everything was created via migrations of Entity Framework)?
CREATE TABLE [dbo].[LineNumbers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LineDescriptionId] [int] NOT NULL,
[ProtocolId] [int] NULL,
[Value] [int] NULL,
CONSTRAINT [PK_LineNumbers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_LineDescriptionId] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId] ON [dbo].[LineNumbers]
(
[LineDescriptionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_LineDescriptionId_Value] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId_Value] ON [dbo].[LineNumbers]
(
[LineDescriptionId] ASC,
[Value] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_ProtocolId] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_ProtocolId] ON [dbo].[LineNumbers]
(
[ProtocolId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LineNumbers] WITH NOCHECK ADD CONSTRAINT [FK_LineNumbers_LineDescriptions_LineDescriptionId] FOREIGN KEY([LineDescriptionId])
REFERENCES [dbo].[LineDescriptions] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[LineNumbers] CHECK CONSTRAINT [FK_LineNumbers_LineDescriptions_LineDescriptionId]
GO
ALTER TABLE [dbo].[LineNumbers] WITH NOCHECK ADD CONSTRAINT [FK_LineNumbers_Protocols_ProtocolId] FOREIGN KEY([ProtocolId])
REFERENCES [dbo].[Protocols] ([Id])
GO
ALTER TABLE [dbo].[LineNumbers] CHECK CONSTRAINT [FK_LineNumbers_Protocols_ProtocolId]
GOsql
sql-server database azure asked Nov 21 '18 at 8:31
Artem BaikuzinArtem Baikuzin
112
It might be something with statistics. Two questions to consider: 1. do you have anyORDER BYs in your queries? 2. have you tried with some index hints?
– dee zg
Nov 23 '18 at 19:45
1. I'm not using anyORDER BYin this queries (the fieldProtocolIdis mentioned only in field list ofSELECTquery, no any additional filters, etc). 2. there wasn't any index hint while I tried this queries, but finally I resolved it by adding indexLineNumbers.LineDescriptionIdincludeProtocolId.
– Artem Baikuzin
Nov 24 '18 at 11:26
yes, that makes sense to have an index on the column you perform join on and then includeProtocolIdinto that index since that way you avoid going to table but getting that column from index directly.
– dee zg
Nov 24 '18 at 13:40
Understand. It is also means that I have to includeValuecolumn to this new index if I want to retrieveValuelikeProtocolId(because it works also slow without include forValueto index). In other words, I have to include all columns from select list of target table to index when I doSELECT + JOIN?
– Artem Baikuzin
Nov 24 '18 at 15:41
oh, i've just realized you haveTOP 1. check this out: geekswithblogs.net/Martinez/archive/2013/01/30/… you might want to useHASH JOIN. please test and let us know the results here!
– dee zg
Nov 24 '18 at 15:46
|
show 4 more comments
0
0
0
1
I have this pretty simple table with 17m records in it:
CREATE TABLE [dbo].[LineNumbers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LineDescriptionId] [int] NOT NULL,
[ProtocolId] [int] NULL,
[Value] [int] NULL,
CONSTRAINT [PK_LineNumbers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Query to the table with additional join works fine, if there is no ProtocolId in it:
select top 1
ln.LineDescriptionId
from LineNumbers ln
join LineDescriptions ld on ld.Id = ln.LineDescriptionId and ld.ProtocolSetId = 25
-- Elapsed time: 00:00:00.1718750
Execution plan: https://www.brentozar.com/pastetheplan/?id=rJV34gvR7
But when I try to add ProtocolId to the field list, query time grows dramatically:
select top 1
ln.ProtocolId
from LineNumbers ln
join LineDescriptions ld on ld.Id = ln.LineDescriptionId and ld.ProtocolSetId = 25
-- Elapsed time: 00:02:19.6464843
Execution plan: https://www.brentozar.com/pastetheplan/?id=SkG-hyDCQ
Also, this works smooth:
select top 1
(select ProtocolId from LineNumbers where LineNumbers.Id = ln.Id) as ProtocolId
from LineNumbers ln
join LineDescriptions ld on ld.Id = ln.LineDescriptionId and ld.ProtocolSetId = 25
-- Elapsed time: 00:00:00.1718750
Tried this queries and variations on Azure DB and local MSSQL 2017. Results are the same. As long as I keep ProtocolId out of the field list everything is fine.
Is there some mistake in my data scheme (everything was created via migrations of Entity Framework)?
CREATE TABLE [dbo].[LineNumbers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LineDescriptionId] [int] NOT NULL,
[ProtocolId] [int] NULL,
[Value] [int] NULL,
CONSTRAINT [PK_LineNumbers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_LineDescriptionId] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId] ON [dbo].[LineNumbers]
(
[LineDescriptionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_LineDescriptionId_Value] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId_Value] ON [dbo].[LineNumbers]
(
[LineDescriptionId] ASC,
[Value] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_ProtocolId] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_ProtocolId] ON [dbo].[LineNumbers]
(
[ProtocolId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LineNumbers] WITH NOCHECK ADD CONSTRAINT [FK_LineNumbers_LineDescriptions_LineDescriptionId] FOREIGN KEY([LineDescriptionId])
REFERENCES [dbo].[LineDescriptions] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[LineNumbers] CHECK CONSTRAINT [FK_LineNumbers_LineDescriptions_LineDescriptionId]
GO
ALTER TABLE [dbo].[LineNumbers] WITH NOCHECK ADD CONSTRAINT [FK_LineNumbers_Protocols_ProtocolId] FOREIGN KEY([ProtocolId])
REFERENCES [dbo].[Protocols] ([Id])
GO
ALTER TABLE [dbo].[LineNumbers] CHECK CONSTRAINT [FK_LineNumbers_Protocols_ProtocolId]
GOsql
sql-server database azure asked Nov 21 '18 at 8:31
Artem BaikuzinArtem Baikuzin
112
I have this pretty simple table with 17m records in it:
CREATE TABLE [dbo].[LineNumbers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LineDescriptionId] [int] NOT NULL,
[ProtocolId] [int] NULL,
[Value] [int] NULL,
CONSTRAINT [PK_LineNumbers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Query to the table with additional join works fine, if there is no ProtocolId in it:
select top 1
ln.LineDescriptionId
from LineNumbers ln
join LineDescriptions ld on ld.Id = ln.LineDescriptionId and ld.ProtocolSetId = 25
-- Elapsed time: 00:00:00.1718750
Execution plan: https://www.brentozar.com/pastetheplan/?id=rJV34gvR7
But when I try to add ProtocolId to the field list, query time grows dramatically:
select top 1
ln.ProtocolId
from LineNumbers ln
join LineDescriptions ld on ld.Id = ln.LineDescriptionId and ld.ProtocolSetId = 25
-- Elapsed time: 00:02:19.6464843
Execution plan: https://www.brentozar.com/pastetheplan/?id=SkG-hyDCQ
Also, this works smooth:
select top 1
(select ProtocolId from LineNumbers where LineNumbers.Id = ln.Id) as ProtocolId
from LineNumbers ln
join LineDescriptions ld on ld.Id = ln.LineDescriptionId and ld.ProtocolSetId = 25
-- Elapsed time: 00:00:00.1718750
Tried this queries and variations on Azure DB and local MSSQL 2017. Results are the same. As long as I keep ProtocolId out of the field list everything is fine.
Is there some mistake in my data scheme (everything was created via migrations of Entity Framework)?
CREATE TABLE [dbo].[LineNumbers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LineDescriptionId] [int] NOT NULL,
[ProtocolId] [int] NULL,
[Value] [int] NULL,
CONSTRAINT [PK_LineNumbers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_LineDescriptionId] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId] ON [dbo].[LineNumbers]
(
[LineDescriptionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_LineDescriptionId_Value] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId_Value] ON [dbo].[LineNumbers]
(
[LineDescriptionId] ASC,
[Value] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_ProtocolId] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_ProtocolId] ON [dbo].[LineNumbers]
(
[ProtocolId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LineNumbers] WITH NOCHECK ADD CONSTRAINT [FK_LineNumbers_LineDescriptions_LineDescriptionId] FOREIGN KEY([LineDescriptionId])
REFERENCES [dbo].[LineDescriptions] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[LineNumbers] CHECK CONSTRAINT [FK_LineNumbers_LineDescriptions_LineDescriptionId]
GO
ALTER TABLE [dbo].[LineNumbers] WITH NOCHECK ADD CONSTRAINT [FK_LineNumbers_Protocols_ProtocolId] FOREIGN KEY([ProtocolId])
REFERENCES [dbo].[Protocols] ([Id])
GO
ALTER TABLE [dbo].[LineNumbers] CHECK CONSTRAINT [FK_LineNumbers_Protocols_ProtocolId]
GOCREATE TABLE [dbo].[LineNumbers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LineDescriptionId] [int] NOT NULL,
[ProtocolId] [int] NULL,
[Value] [int] NULL,
CONSTRAINT [PK_LineNumbers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_LineDescriptionId] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId] ON [dbo].[LineNumbers]
(
[LineDescriptionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_LineDescriptionId_Value] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId_Value] ON [dbo].[LineNumbers]
(
[LineDescriptionId] ASC,
[Value] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_ProtocolId] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_ProtocolId] ON [dbo].[LineNumbers]
(
[ProtocolId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LineNumbers] WITH NOCHECK ADD CONSTRAINT [FK_LineNumbers_LineDescriptions_LineDescriptionId] FOREIGN KEY([LineDescriptionId])
REFERENCES [dbo].[LineDescriptions] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[LineNumbers] CHECK CONSTRAINT [FK_LineNumbers_LineDescriptions_LineDescriptionId]
GO
ALTER TABLE [dbo].[LineNumbers] WITH NOCHECK ADD CONSTRAINT [FK_LineNumbers_Protocols_ProtocolId] FOREIGN KEY([ProtocolId])
REFERENCES [dbo].[Protocols] ([Id])
GO
ALTER TABLE [dbo].[LineNumbers] CHECK CONSTRAINT [FK_LineNumbers_Protocols_ProtocolId]
GOCREATE TABLE [dbo].[LineNumbers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LineDescriptionId] [int] NOT NULL,
[ProtocolId] [int] NULL,
[Value] [int] NULL,
CONSTRAINT [PK_LineNumbers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_LineDescriptionId] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId] ON [dbo].[LineNumbers]
(
[LineDescriptionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_LineDescriptionId_Value] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId_Value] ON [dbo].[LineNumbers]
(
[LineDescriptionId] ASC,
[Value] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LineNumbers_ProtocolId] Script Date: 21.11.2018 10:47:09 ******/
CREATE NONCLUSTERED INDEX [IX_LineNumbers_ProtocolId] ON [dbo].[LineNumbers]
(
[ProtocolId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LineNumbers] WITH NOCHECK ADD CONSTRAINT [FK_LineNumbers_LineDescriptions_LineDescriptionId] FOREIGN KEY([LineDescriptionId])
REFERENCES [dbo].[LineDescriptions] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[LineNumbers] CHECK CONSTRAINT [FK_LineNumbers_LineDescriptions_LineDescriptionId]
GO
ALTER TABLE [dbo].[LineNumbers] WITH NOCHECK ADD CONSTRAINT [FK_LineNumbers_Protocols_ProtocolId] FOREIGN KEY([ProtocolId])
REFERENCES [dbo].[Protocols] ([Id])
GO
ALTER TABLE [dbo].[LineNumbers] CHECK CONSTRAINT [FK_LineNumbers_Protocols_ProtocolId]
GOsql
sql-server database azure sql
sql-server database azure asked Nov 21 '18 at 8:31
Artem BaikuzinArtem Baikuzin
112
asked Nov 21 '18 at 8:31
Artem BaikuzinArtem Baikuzin
112
edited Nov 24 '18 at 15:57
Artem Baikuzin
asked Nov 21 '18 at 8:31
Artem BaikuzinArtem Baikuzin
112
asked Nov 21 '18 at 8:31
Artem BaikuzinArtem Baikuzin
112
asked Nov 21 '18 at 8:31
Artem BaikuzinArtem Baikuzin
112
112
It might be something with statistics. Two questions to consider: 1. do you have anyORDER BYs in your queries? 2. have you tried with some index hints?
– dee zg
Nov 23 '18 at 19:45
1. I'm not using anyORDER BYin this queries (the fieldProtocolIdis mentioned only in field list ofSELECTquery, no any additional filters, etc). 2. there wasn't any index hint while I tried this queries, but finally I resolved it by adding indexLineNumbers.LineDescriptionIdincludeProtocolId.
– Artem Baikuzin
Nov 24 '18 at 11:26
yes, that makes sense to have an index on the column you perform join on and then includeProtocolIdinto that index since that way you avoid going to table but getting that column from index directly.
– dee zg
Nov 24 '18 at 13:40
Understand. It is also means that I have to includeValuecolumn to this new index if I want to retrieveValuelikeProtocolId(because it works also slow without include forValueto index). In other words, I have to include all columns from select list of target table to index when I doSELECT + JOIN?
– Artem Baikuzin
Nov 24 '18 at 15:41
oh, i've just realized you haveTOP 1. check this out: geekswithblogs.net/Martinez/archive/2013/01/30/… you might want to useHASH JOIN. please test and let us know the results here!
– dee zg
Nov 24 '18 at 15:46
|
show 4 more comments
It might be something with statistics. Two questions to consider: 1. do you have anyORDER BYs in your queries? 2. have you tried with some index hints?
– dee zg
Nov 23 '18 at 19:45
1. I'm not using anyORDER BYin this queries (the fieldProtocolIdis mentioned only in field list ofSELECTquery, no any additional filters, etc). 2. there wasn't any index hint while I tried this queries, but finally I resolved it by adding indexLineNumbers.LineDescriptionIdincludeProtocolId.
– Artem Baikuzin
Nov 24 '18 at 11:26
yes, that makes sense to have an index on the column you perform join on and then includeProtocolIdinto that index since that way you avoid going to table but getting that column from index directly.
– dee zg
Nov 24 '18 at 13:40
Understand. It is also means that I have to includeValuecolumn to this new index if I want to retrieveValuelikeProtocolId(because it works also slow without include forValueto index). In other words, I have to include all columns from select list of target table to index when I doSELECT + JOIN?
– Artem Baikuzin
Nov 24 '18 at 15:41
oh, i've just realized you haveTOP 1. check this out: geekswithblogs.net/Martinez/archive/2013/01/30/… you might want to useHASH JOIN. please test and let us know the results here!
– dee zg
Nov 24 '18 at 15:46
It might be something with statistics. Two questions to consider: 1. do you have any
ORDER BYs in your queries? 2. have you tried with some index hints?– dee zg
Nov 23 '18 at 19:45
It might be something with statistics. Two questions to consider: 1. do you have any
ORDER BYs in your queries? 2. have you tried with some index hints?– dee zg
Nov 23 '18 at 19:45
1. I'm not using any
ORDER BY in this queries (the field ProtocolId is mentioned only in field list of SELECT query, no any additional filters, etc). 2. there wasn't any index hint while I tried this queries, but finally I resolved it by adding index LineNumbers.LineDescriptionId include ProtocolId.– Artem Baikuzin
Nov 24 '18 at 11:26
1. I'm not using any
ORDER BY in this queries (the field ProtocolId is mentioned only in field list of SELECT query, no any additional filters, etc). 2. there wasn't any index hint while I tried this queries, but finally I resolved it by adding index LineNumbers.LineDescriptionId include ProtocolId.– Artem Baikuzin
Nov 24 '18 at 11:26
yes, that makes sense to have an index on the column you perform join on and then include
ProtocolId into that index since that way you avoid going to table but getting that column from index directly.– dee zg
Nov 24 '18 at 13:40
yes, that makes sense to have an index on the column you perform join on and then include
ProtocolId into that index since that way you avoid going to table but getting that column from index directly.– dee zg
Nov 24 '18 at 13:40
Understand. It is also means that I have to include
Value column to this new index if I want to retrieve Value like ProtocolId (because it works also slow without include for Value to index). In other words, I have to include all columns from select list of target table to index when I do SELECT + JOIN?– Artem Baikuzin
Nov 24 '18 at 15:41
Understand. It is also means that I have to include
Value column to this new index if I want to retrieve Value like ProtocolId (because it works also slow without include for Value to index). In other words, I have to include all columns from select list of target table to index when I do SELECT + JOIN?– Artem Baikuzin
Nov 24 '18 at 15:41
oh, i've just realized you have
TOP 1. check this out: geekswithblogs.net/Martinez/archive/2013/01/30/… you might want to use HASH JOIN. please test and let us know the results here!– dee zg
Nov 24 '18 at 15:46
oh, i've just realized you have
TOP 1. check this out: geekswithblogs.net/Martinez/archive/2013/01/30/… you might want to use HASH JOIN. please test and let us know the results here!– dee zg
Nov 24 '18 at 15:46
|
show 4 more comments
1 Answer
1
active
oldest
votes
1
Eventually, I solved it by adding nonclustered index on field LineNumbers.LineDescriptionId with inclusion of LineNumbers.ProtocolId
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId_ProtocolId] ON
[dbo].[LineNumbers]([LineDescriptionId] ASC)
INCLUDE ([ProtocolId])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
Result:
SELECT TOP 1
ln.ProtocolId
FROM LineNumbers ln
JOIN LineDescriptions ld ON ld.Id = ln.LineDescriptionId AND ld.ProtocolSetId = 25
-- Elapsed time: 00:00:00.1403155
Execution plan: https://www.brentozar.com/pastetheplan/?id=Syywn1wRQ
Why does it work that way?
For example, if I'd do similar use case with PostgreSQL then there is no need in any additional indexes at all (beside obvious FK indexes on ProtocolId and LineDescriptionId fields).
answered Nov 23 '18 at 19:26
Artem BaikuzinArtem Baikuzin
112
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});
}
});
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53407962%2fazure-db-mssql-2017-query-performance-regression%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Eventually, I solved it by adding nonclustered index on field LineNumbers.LineDescriptionId with inclusion of LineNumbers.ProtocolId
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId_ProtocolId] ON
[dbo].[LineNumbers]([LineDescriptionId] ASC)
INCLUDE ([ProtocolId])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
Result:
SELECT TOP 1
ln.ProtocolId
FROM LineNumbers ln
JOIN LineDescriptions ld ON ld.Id = ln.LineDescriptionId AND ld.ProtocolSetId = 25
-- Elapsed time: 00:00:00.1403155
Execution plan: https://www.brentozar.com/pastetheplan/?id=Syywn1wRQ
Why does it work that way?
For example, if I'd do similar use case with PostgreSQL then there is no need in any additional indexes at all (beside obvious FK indexes on ProtocolId and LineDescriptionId fields).
answered Nov 23 '18 at 19:26
Artem BaikuzinArtem Baikuzin
112
add a comment |
1
Eventually, I solved it by adding nonclustered index on field LineNumbers.LineDescriptionId with inclusion of LineNumbers.ProtocolId
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId_ProtocolId] ON
[dbo].[LineNumbers]([LineDescriptionId] ASC)
INCLUDE ([ProtocolId])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
Result:
SELECT TOP 1
ln.ProtocolId
FROM LineNumbers ln
JOIN LineDescriptions ld ON ld.Id = ln.LineDescriptionId AND ld.ProtocolSetId = 25
-- Elapsed time: 00:00:00.1403155
Execution plan: https://www.brentozar.com/pastetheplan/?id=Syywn1wRQ
Why does it work that way?
For example, if I'd do similar use case with PostgreSQL then there is no need in any additional indexes at all (beside obvious FK indexes on ProtocolId and LineDescriptionId fields).
answered Nov 23 '18 at 19:26
Artem BaikuzinArtem Baikuzin
112
add a comment |
1
1
1
Eventually, I solved it by adding nonclustered index on field LineNumbers.LineDescriptionId with inclusion of LineNumbers.ProtocolId
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId_ProtocolId] ON
[dbo].[LineNumbers]([LineDescriptionId] ASC)
INCLUDE ([ProtocolId])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
Result:
SELECT TOP 1
ln.ProtocolId
FROM LineNumbers ln
JOIN LineDescriptions ld ON ld.Id = ln.LineDescriptionId AND ld.ProtocolSetId = 25
-- Elapsed time: 00:00:00.1403155
Execution plan: https://www.brentozar.com/pastetheplan/?id=Syywn1wRQ
Why does it work that way?
For example, if I'd do similar use case with PostgreSQL then there is no need in any additional indexes at all (beside obvious FK indexes on ProtocolId and LineDescriptionId fields).
answered Nov 23 '18 at 19:26
Artem BaikuzinArtem Baikuzin
112
Eventually, I solved it by adding nonclustered index on field LineNumbers.LineDescriptionId with inclusion of LineNumbers.ProtocolId
CREATE NONCLUSTERED INDEX [IX_LineNumbers_LineDescriptionId_ProtocolId] ON
[dbo].[LineNumbers]([LineDescriptionId] ASC)
INCLUDE ([ProtocolId])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
Result:
SELECT TOP 1
ln.ProtocolId
FROM LineNumbers ln
JOIN LineDescriptions ld ON ld.Id = ln.LineDescriptionId AND ld.ProtocolSetId = 25
-- Elapsed time: 00:00:00.1403155
Execution plan: https://www.brentozar.com/pastetheplan/?id=Syywn1wRQ
Why does it work that way?
For example, if I'd do similar use case with PostgreSQL then there is no need in any additional indexes at all (beside obvious FK indexes on ProtocolId and LineDescriptionId fields).
answered Nov 23 '18 at 19:26
Artem BaikuzinArtem Baikuzin
112
edited Nov 24 '18 at 15:58
answered Nov 23 '18 at 19:26
Artem BaikuzinArtem Baikuzin
112
answered Nov 23 '18 at 19:26
Artem BaikuzinArtem Baikuzin
112
answered Nov 23 '18 at 19:26
Artem BaikuzinArtem Baikuzin
112
112
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- 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.
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53407962%2fazure-db-mssql-2017-query-performance-regression%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
It might be something with statistics. Two questions to consider: 1. do you have any
ORDER BYs in your queries? 2. have you tried with some index hints?– dee zg
Nov 23 '18 at 19:45
1. I'm not using any
ORDER BYin this queries (the fieldProtocolIdis mentioned only in field list ofSELECTquery, no any additional filters, etc). 2. there wasn't any index hint while I tried this queries, but finally I resolved it by adding indexLineNumbers.LineDescriptionIdincludeProtocolId.– Artem Baikuzin
Nov 24 '18 at 11:26
yes, that makes sense to have an index on the column you perform join on and then include
ProtocolIdinto that index since that way you avoid going to table but getting that column from index directly.– dee zg
Nov 24 '18 at 13:40
Understand. It is also means that I have to include
Valuecolumn to this new index if I want to retrieveValuelikeProtocolId(because it works also slow without include forValueto index). In other words, I have to include all columns from select list of target table to index when I doSELECT + JOIN?– Artem Baikuzin
Nov 24 '18 at 15:41
oh, i've just realized you have
TOP 1. check this out: geekswithblogs.net/Martinez/archive/2013/01/30/… you might want to useHASH JOIN. please test and let us know the results here!– dee zg
Nov 24 '18 at 15:46