Can I safely force the plan without the table spools?

Question

Can I safely force the plan without the table spools?

SSCertifiable

Points: 7989

December 28, 2021 at 8:32 pm

I have the query below that has had 2 plans since at least Dec 20th. On Dec 22nd, the plan with eager spool operators began to be used orders of magnitude more than the other plan. Those operators show up preceding non-clustered index inserts. For some reason, the other plan shows no indication of updating non-clustered indexes. At the same time, duration has gone up. I think the duration issue is likely attributable to more batches/sec since 12/23. I'd like to use Query Store to force the plan without the spools preceding the index inserts, but I'm sure there is a reason this plan exists. I just don't know what it is.

INSERT INTO dbo.Clicks (LinkID,RecipID,QueueTime,ClickTime,IP,Referrer,Query,IsOpen,Expired,
ClickMailingID,SocialNetworkID,HasLiveContent,PostalCode,City,State,Country,
UserAgentHashFirstMD5,UserAgentHashLastMD5,FormFactor,DeviceOS,MailClient,Suppressed,Signature) 
SELECT LinkID,RecipID,QueueTime,ClickTime, NULLIF(IP, 'null'),
CASE WHEN Referrer LIKE 'null' THEN NULL ELSE Referrer END,Query,IsOpen,Expired,MailingID,
SocialNetworkID,HasLiveContent,PostalCode,City,State,Country,UserAgentHashFirstMD5,
UserAgentHashLastMD5,FormFactor,DeviceOS,MailClient,Suppressed,  @P0  
FROM #Clicks c1 
LEFT OUTER MERGE JOIN #Clicks2 c2 ON c2.ClickID = c1.ClickID 
LEFT OUTER MERGE JOIN #Clicks3 c3 ON c3.ClickID = c1.ClickID OPTION (FORCE ORDER)

Table schema is as follows. I didn't design this and I'm sure it needs attention, but, for now, I need to solve this without changing the table.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Clicks](
[ClickID] [bigint] IDENTITY(1,1) NOT NULL,
[LinkID] [int] NOT NULL,
[RecipID] [int] NOT NULL,
[ClickTime] [datetime] NOT NULL,
127.0.0.1 [varchar](39) NULL,
[Referrer] [nvarchar](max) NULL,
[Query] [varchar](max) NULL,
[Expired] [int] NULL,
[SocialNetworkID] [int] NULL,
[PostalCode] [nvarchar](255) NULL,
[City] [nvarchar](255) NULL,
[State] [nvarchar](255) NULL,
[Country] [nvarchar](255) NULL,
[TaskID] [int] NULL,
[HasLiveContent] [bit] NULL,
[UserAgentHashFirstMD5] [bigint] NULL,
[UserAgentHashLastMD5] [bigint] NULL,
[Signature] [bigint] NULL,
[FormFactor] [varchar](50) NULL,
[DeviceOS] [varchar](50) NULL,
[MailClient] [varchar](50) NULL,
[ClickMailingID] [int] NULL,
[IsOpen] [bit] NULL,
[QueueTime] [int] NULL,
[Suppressed] [bit] NULL,
[SuppressionFilter] [smallint] NULL,
 CONSTRAINT [PK_Clicks_ClickID] PRIMARY KEY CLUSTERED 
(
[ClickID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Clicks] ADD  CONSTRAINT [DF_ClicksNew2_LinkID]  DEFAULT ((0)) FOR [LinkID]
GO

ALTER TABLE [dbo].[Clicks] ADD  CONSTRAINT [DF_ClicksNew2_RecipID]  DEFAULT ((0)) FOR [RecipID]
GO

ALTER TABLE [dbo].[Clicks] ADD  CONSTRAINT [DF_ClicksNew2_IP]  DEFAULT ('') FOR 127.0.0.1
GO

ALTER TABLE [dbo].[Clicks] ADD  CONSTRAINT [DF_Clicks_Referrer]  DEFAULT ('') FOR [Referrer]
GO

ALTER TABLE [dbo].[Clicks] ADD  CONSTRAINT [DF_Clicks_Query]  DEFAULT ('') FOR [Query]
GO

ALTER TABLE [dbo].[Clicks] ADD  CONSTRAINT [DF_ClicksNew2_Expired]  DEFAULT ((0)) FOR [Expired]
GO

ALTER TABLE [dbo].[Clicks] ADD  CONSTRAINT [DF_Clicks_HasLiveContent]  DEFAULT ((0)) FOR [HasLiveContent]
GO

See attached query plans.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

I would review that query - it is forcing a MERGE join which may not be the best option now for those temp tables. You should also review how those temp tables are being created - and whether a clustered index on each one improves the performance.

I also see the option to FORCE ORDER, which shouldn't be needed and could be preventing a better plan.

And finally, you really need to use the table aliases on every column in the query. Without the aliases, there is no way to determine which table each column comes from - which also leads to not being able to determine if an INNER JOIN would satisfy the requirements.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

We need to see the DDL for Clicks2 and Clicks3 as well. Particularly if those tables are not clustered uniquely on ClickID, the MERGE join could be less efficient.

As Jeffrey noted, don't force the order of joins. Also, I understand sometimes that you need to avoid a LOOP join, which sometimes causes big performance issues, but allow SQL to use a HASH join instead if it wants too.

FROM #Clicks c1

LEFT OUTER JOIN #Clicks2 c2 ON c2.ClickID = c1.ClickID

LEFT OUTER JOIN #Clicks3 c3 ON c3.ClickID = c1.ClickID

OPTION ( HASH JOIN, MERGE JOIN )

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

lmarkum SSCertifiable Points: 7989 More actions · Answer 3

This is a query that is created by the application. I didn't write this. I know not to tie the hands of the query optimizer. I will go back to the app/dev team and see about the DDL for the temp tables.