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.
December 28, 2021 at 9:10 pm
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 28, 2021 at 9:23 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 29, 2021 at 12:49 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply