I have two very similar queries
First query:
SELECT count(*)
FROM Audits a
JOIN AuditRelatedIds ari ON a.Id = ari.AuditId
WHERE
ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1'
and a.TargetTypeId IN
(1,2,3,4,5,6,7,8,9,
11,12,13,14,15,16,17,18,19,
21,22,23,24,25,26,27,28,29,30,
31,32,33,34,35,36,37,38,39,
41,42,43,44,45,46,47,48,49,
51,52,53,54,55,56,57,58,59,
61,62,63,64,65,66,67,68,69,
71,72,73,74,75,76,77,78,79)
Result: 267479
Plan: https://www.brentozar.com/pastetheplan/?id=BJWTtILyS
Second query:
SELECT count(*)
FROM Audits a
JOIN AuditRelatedIds ari ON a.Id = ari.AuditId
WHERE
ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1'
and a.TargetTypeId IN
(1,2,3,4,5,6,7,8,9,
11,12,13,14,15,16,17,18,19,
21,22,23,24,25,26,27,28,29,
31,32,33,34,35,36,37,38,39,
41,42,43,44,45,46,47,48,49,
51,52,53,54,55,56,57,58,59,
61,62,63,64,65,66,67,68,69,
71,72,73,74,75,76,77,78,79)
Result: 25650
Plan: https://www.brentozar.com/pastetheplan/?id=S1v79U8kS
The first query takes about one second to complete, while the second query takes about 20 seconds. This is completely counter-intuitive to me because the first query has a much higher count than the second. This is on SQL server 2012
Why is there so much of a difference? How can i speedup the second query to be as fast as the first one?
Here is the Create table script for both tables:
CREATE TABLE [dbo].[AuditRelatedIds](
[AuditId] [bigint] NOT NULL,
[RelatedId] [uniqueidentifier] NOT NULL,
[AuditTargetTypeId] [smallint] NOT NULL,
CONSTRAINT [PK_AuditRelatedIds] PRIMARY KEY CLUSTERED
(
[AuditId] ASC,
[RelatedId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_AuditRelatedIdsRelatedId_INCLUDES] ON [dbo].[AuditRelatedIds]
(
[RelatedId] ASC
)
INCLUDE ( [AuditId]) 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]
ALTER TABLE [dbo].[AuditRelatedIds] WITH CHECK ADD CONSTRAINT [FK_AuditRelatedIds_AuditId_Audits_Id] FOREIGN KEY([AuditId])
REFERENCES [dbo].[Audits] ([Id])
ALTER TABLE [dbo].[AuditRelatedIds] CHECK CONSTRAINT [FK_AuditRelatedIds_AuditId_Audits_Id]
ALTER TABLE [dbo].[AuditRelatedIds] WITH CHECK ADD CONSTRAINT [FK_AuditRelatedIds_AuditTargetTypeId_AuditTargetTypes_Id] FOREIGN KEY([AuditTargetTypeId])
REFERENCES [dbo].[AuditTargetTypes] ([Id])
ALTER TABLE [dbo].[AuditRelatedIds] CHECK CONSTRAINT [FK_AuditRelatedIds_AuditTargetTypeId_AuditTargetTypes_Id]
CREATE TABLE [dbo].[Audits](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[TargetTypeId] [smallint] NOT NULL,
[TargetId] [nvarchar](40) NOT NULL,
[TargetName] [nvarchar](max) NOT NULL,
[Action] [tinyint] NOT NULL,
[ActionOverride] [tinyint] NULL,
[Date] [datetime] NOT NULL,
[UserDisplayName] [nvarchar](max) NOT NULL,
[DescriptionData] [nvarchar](max) NULL,
[IsNotification] [bit] NOT NULL,
CONSTRAINT [PK_Audits] 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] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING ON
CREATE NONCLUSTERED INDEX [IX_AuditsTargetId] ON [dbo].[Audits]
(
[TargetId] 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]
SET ANSI_PADDING ON
CREATE NONCLUSTERED INDEX [IX_AuditsTargetTypeIdAction_INCLUDES] ON [dbo].[Audits]
(
[TargetTypeId] ASC,
[Action] ASC
)
INCLUDE ( [TargetId],
[UserDisplayName]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
ALTER TABLE [dbo].[Audits] WITH CHECK ADD CONSTRAINT [FK_Audits_TargetTypeId_AuditTargetTypes_Id] FOREIGN KEY([TargetTypeId])
REFERENCES [dbo].[AuditTargetTypes] ([Id])
ALTER TABLE [dbo].[Audits] CHECK CONSTRAINT [FK_Audits_TargetTypeId_AuditTargetTypes_Id]
Best Answer
Tl;dr at the bottom
Why was the bad plan chosen
The main reason for choosing one plan over the other is the
Estimated total subtree
cost.This cost was lower for the bad plan than for the better performing plan.
The total estimated subtree cost for the bad plan:
The total estimated subtree cost for your better performing plan
The operator estimated costs
Certain operators can take most of this cost, and could be a reason for the optimizer to choose a different path / plan.
In our better performing plan, the bulk of the
Subtreecost
is calculated on theindex seek
&nested loops operator
performing the join:While for our bad query plan, the
Clustered index seek
operator cost is lowerWhich should explain why the other plan could have been chosen.
(And by adding the parameter
30
increasing the bad plan's cost where it has risen above the871.510000
estimated cost). Estimated guess™The better performing plan
The bad plan
Where does this take us?
This information brings us to a way to force the bad query plan on our example (See DML to almost replicate OP's Issue for the data used to replicate the issue)
By adding an
INNER LOOP JOIN
join hintIt is closer, but has some join order differences:
Rewriting
My first rewrite attempt could be storing all these numbers in a temp table instead:
And then adding a
JOIN
instead of the bigIN()
Our query plan is different but not yet fixed:
with a huge estimated operator cost on the
AuditRelatedIds
tableHere is where I noticed that
The reason that I cannot directly recreate your plan is optimized bitmap filtering.
I can recreate your plan by disabling optimized bitmap filters by using traceflags
7497
&7498
More information on optimized bitmap filters here.
This means, that without the bitmap filters, the optimizer deems it better to first join to the
#number
table and then join to theAuditRelatedIds
table.When forcing the order
OPTION (QUERYTRACEON 7497, QUERYTRACEON 7498, FORCE ORDER);
we can see why:&
Not good
Removing the ability to go parallel with maxdop 1
When adding
MAXDOP 1
the query performs faster, single threaded.And adding this index
While using a merge join.
The same is true when we remove the force order query hint or not using the #Numbers table and using the
IN()
instead.My advice would be to look into adding
MAXDOP(1)
and see if that helps your query, with a rewrite if needed.Ofcourse you should also keep in mind that on my end it performs even better due to the optimized bitmap filtering & actually using multiple threads to good effect:
TL;DR
Estimated costs will define the plan chosen, I was able to replicate the behaviour and saw that
optimized bitmap filters
+parallellism
operators where added on my end to perform the query in a performant and fast manner.You could look into adding
MAXDOP(1)
to your query as a way to hopefully get the same controlled outcome each time, with amerge join
and no 'bad'parallellism
.Upgrading to a newer version and using a higher cardinality estimator version than
CardinalityEstimationModelVersion="70"
might also help.A numbers temporary table to do the multi value filtering can also help.
DML to almost replicate OP's Issue
I spent more time on this than i would like to admit