8

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]
  • 3
    Would we be able to get some table schema and index details. As I am sure you noticed plans are a little different but evidently it is making a big difference. If we can get those details than maybe we can see what options we have. – Kirk Saunders Jun 18 at 12:56
  • 2
    As a very quick tip, instead of using IN create a TempTable with a single TINYINT /INT column (clustered) with the numbers you want, and then INNER JOIN to it. Other than that we'll likely need DDL information as @KirkSaunders mentioned above – George.Palacios Jun 18 at 13:03
  • 2
    Is there anything special about TargetTypeId = 30? Seems the plans are different because this one value really skews the amount of data (expected to be) returned. – Aaron Bertrand Jun 18 at 13:04
  • I realize it's awfully pedantic but the statement "the first query returns a lot more rows than the second." is not correct. Both return 1 row ;) – ypercubeᵀᴹ Jun 18 at 13:08
  • 1
    I updated the question with the create table statements for both tables – Chocoman Jun 18 at 13:16
7

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:

enter image description here

The total estimated subtree cost for your better performing plan

enter image description here


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:

enter image description here

While for our bad query plan, the Clustered index seek operator cost is lower

enter image description here

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

enter image description here

The bad plan

enter image description here


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:

enter image description here


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:

enter image description here

with a huge estimated operator cost on the AuditRelatedIds table

enter image description here


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.

enter image description 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:

enter image description here

& enter image description here

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

enter image description here

While using a merge join. enter image description here

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:

enter image description here

enter image description here


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;
  • Very nice explanation! Adding MAXDOP 0 seems to have fixed it. Thank you very much! – Chocoman Jun 19 at 11:52
  • 1
    MAXDOP 1 ** (typo) – Chocoman Jun 19 at 12:06
  • @Chocoman Great! Happy to help :) – Randi Vertongen Jun 19 at 12:35
1

From what I can tell the primary difference between the two plans is the difference in what is the "Primary Filter".

With the first version the main filter was deriving which Audit.ID is related to ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1' then filter that list down to those who's Audit.TargetTypeID were in the list.

With the second version the main filter was deriving which Audit.ID is related to the list of Audit.TargetTypeID.

Since the addition of Audit.TargetTypeID = 30 appeared to dramatically increase the record count (267,479 and 25,650 respectively according to the Original Question). That is probably why the execution plans are different. (As I understand it) SQL will try to do the most selective function first and then apply the rest of the rules after that. With the first version, querying by AuditRelatedID.RelatedID to then find Audit.ID was probably more selective than trying to use Audit.TargetTypeID to then find Audit.ID.

To ypercube's credit. You can certainly update [AuditRelatedIds].[IX_AuditRelatedIdsRelatedId_INCLUDES] to have both RelatedID and AuditID as part of in index instead of having AuditID as part of an INCLUDE. It shouldn't take up any additional index space and would allow you to use both columns in JOIN clauses. That may help the Query Optimizer create the same execution plan for both queries.

Operating with a similar logic, there may be some benefit to an index on Audit which contains TargetTypeID ASC, ID ASC on the actual ordered/filtering nodes (not as part of the INCLUDE). This should allow the Query optimizer to filter by Audit.TargetTypeID then quickly join to AuditReferenceIds.AuditID. Now this may end up with both queries choosing the less efficient plan so I would only give it a shot after trying ypercube's recommendation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.