Azure DB/MSSQL 2017 query performance regression

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]

GO

edited Nov 24 '18 at 15:57

asked Nov 21 '18 at 8:31

Artem Baikuzin

112

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

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

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

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]

GO

edited Nov 24 '18 at 15:57

asked Nov 21 '18 at 8:31

Artem Baikuzin

112

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

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

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

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]

GO

edited Nov 24 '18 at 15:57

asked Nov 21 '18 at 8:31

Artem 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]

GO

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]

GO

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]

GO

sql sql-server database azure

edited Nov 24 '18 at 15:57

asked Nov 21 '18 at 8:31

Artem Baikuzin

112

edited Nov 24 '18 at 15:57

asked Nov 21 '18 at 8:31

Artem Baikuzin

112

edited Nov 24 '18 at 15:57

asked Nov 21 '18 at 8:31

Artem Baikuzin

112

asked Nov 21 '18 at 8:31

Artem Baikuzin

112

asked Nov 21 '18 at 8:31

Artem Baikuzin

112

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

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

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

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

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

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

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

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

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

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).

edited Nov 24 '18 at 15:58

answered Nov 23 '18 at 19:26

Artem 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');
});

Post as a guest

Name

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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).

edited Nov 24 '18 at 15:58

answered Nov 23 '18 at 19:26

Artem Baikuzin

112

add a comment |

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).

edited Nov 24 '18 at 15:58

answered Nov 23 '18 at 19:26

Artem Baikuzin

112

add a comment |

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).

edited Nov 24 '18 at 15:58

answered Nov 23 '18 at 19:26

Artem 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).

edited Nov 24 '18 at 15:58

answered Nov 23 '18 at 19:26

Artem Baikuzin

112

edited Nov 24 '18 at 15:58

answered Nov 23 '18 at 19:26

Artem Baikuzin

112

answered Nov 23 '18 at 19:26

Artem Baikuzin

112

answered Nov 23 '18 at 19:26

Artem Baikuzin

112

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Cfrgtkky