Very similar queries, vastly different performance
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 the index seek & nested loops operator performing the join:
While for our bad query plan, the Clustered index seek operator cost is lower
Which 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 the 871.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 hint
SELECT count(*)
FROM Audits a
INNER LOOP 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)
It is closer, but has some join order differences:
Rewriting
My first rewrite attempt could be storing all these numbers in a temp table instead:
CREATE TABLE #Numbers(Numbering INT)
INSERT INTO #Numbers(Numbering)
VALUES
(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);
And then adding a JOIN instead of the big IN()
SELECT count(*)
FROM Audits a
INNER LOOP JOIN AuditRelatedIds ari ON a.Id = ari.AuditId
INNER JOIN #Numbers
ON Numbering = a.TargetTypeId
WHERE
ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1';
Our query plan is different but not yet fixed:
with a huge estimated operator cost on the AuditRelatedIds table
Here 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
SELECT count(*)
FROM Audits a
INNER JOIN AuditRelatedIds ari ON a.Id = ari.AuditId
INNER JOIN #Numbers
ON Numbering = a.TargetTypeId
WHERE
ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1'
OPTION (QUERYTRACEON 7497, QUERYTRACEON 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 the AuditRelatedIds 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
CREATE NONCLUSTERED INDEX [IX_AuditRelatedIdsRelatedId_AuditId] ON [dbo].[AuditRelatedIds]
(
[RelatedId] ASC,
[AuditId] 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];
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 a merge 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
set NOCOUNT ON;
DECLARE @I INT = 0
WHILE @I < 56
BEGIN
INSERT INTO [dbo].[Audits] WITH(TABLOCK)
([TargetTypeId],
[TargetId],
[TargetName],
[Action],
[ActionOverride] ,
[Date] ,
[UserDisplayName],
[DescriptionData],
[IsNotification])
SELECT top(500000) CASE WHEN ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 10000 = 30 then 29 ELSE ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 10000 END as rownum2 -- TILL 50 and no 30
,'bla','bla2',1,1,getdate(),'bla3','Bla4',1
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2;
SET @I +=1;
END
-- 'Bad Query matches'
INSERT INTO [dbo].[AuditRelatedIds] WITH(TABLOCK)
([AuditId] ,
[RelatedId] ,
[AuditTargetTypeId])
SELECT
TOP(25650)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1,
('1DD87CF1-286B-409A-8C60-3FFEC394FDB1') ,
CASE WHEN ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 510 = 30 then 29 ELSE ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 510 END as rownum2 -- TILL 50 and no 30
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2
-- Extra matches with 30
SELECT MAX([Id]) FROM [dbo].[Audits];
--28000001 Upper value
INSERT INTO [dbo].[Audits] WITH(TABLOCK)
([TargetTypeId],
[TargetId],
[TargetName],
[Action],
[ActionOverride] ,
[Date] ,
[UserDisplayName],
[DescriptionData],
[IsNotification])
SELECT top(241829) 30 as rownum2 -- TILL 50 and no 30
,'bla','bla2',1,1,getdate(),'bla3','Bla4',1
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2;
;WITH CTE AS
(SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1,
('1DD87CF1-286B-409A-8C60-3FFEC394FDB1') as gu ,
30 as rownum2 -- TILL 50 and no 30
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2
CROSS APPLY master.dbo.spt_values spt3
)
--267479 - 25650 = 241829
INSERT INTO [dbo].[AuditRelatedIds] WITH(TABLOCK)
([AuditId] ,
[RelatedId] ,
[AuditTargetTypeId])
SELECT TOP(241829) rownum1,gu,rownum2 FROM CTE
WHERE rownum1 > 28000001
ORDER BY rownum1 ASC;