I've inherited a rather complex stored procedure that is causing timeouts in our Production environment. I've used SQL Sentry Plan Explorer to help me look at some of the issues. I've identified a few but I'm having a hard time coming up with optimizations that can be made. We are using SQL Server 2019 with
COMPATIBILITY_VERSION = 150.
This sproc returns data to a web client for via an API call so the performance of the page load is very important. This stored procedure has been identified as the bottleneck thru several app profiling sessions.
Here are some of the issues identified:
- Timeouts in PROD where we have 4+ millions of rows of data (ActualElapsedms = 11499)
- Estimated Subtree Cost is a whopping 187.74 (our next slowest sproc has a subtree cost of .653)
- Parts of the query plan show Residual I/O (surprisingly one of them is even an Index Seek)
- There is a
tempdbspill data warning
- Logical Reads is almost 128k
- Sentry shows 3 Missing JOIN Predicates on the
INSERT INTO @ValidRows....but I don't know how to identify them.
There are indexes on the tables involved and from what I can see they appear to be sufficient. However, I see a number of Index Scans being called out in the Plan as problem areas (yellow highlight)
Here is a link to the Actual Execution Plan: https://www.brentozar.com/pastetheplan/?id=S1TN4NOS9
CREATE VIEW [dbo].[DailyNotePublishedContentView] WITH SCHEMABINDING AS SELECT T.Id AS DailyNoteContentId, T.DateModified, T.ModifiedBy, T.Region, T.DateAdded, T.CreatedBy, V.Id AS VersionId, V.DateAdded AS VersionDateAdded, V.CreatedBy as VersionCreatedBy, V.ContentType, V.DateDue, V.IsPrivate, V.ProjectId, V.PublishDate, V.AuthorTeamId, V.ContactEmail, V.Content, V.ContentText, V.Summary, V.SummaryText, V.AllowAddendums, StoreTeamsList = (SELECT STRING_AGG(CONVERT(VARCHAR(max),STA.[Name]), ', ') FROM [dbo].[DailyNoteContentStoreTeam] (NOLOCK) ST INNER JOIN [dbo].[Audiences] (NOLOCK) STA ON STA.Id = ST.AudienceId AND STA.IsActive = 1 WHERE ST.DailyNoteContentVersionId = V.Id ), AssignedToList = (SELECT STRING_AGG(CONVERT(VARCHAR(max),TAA.[Name]), ', ') FROM [dbo].[DailyNoteContentAssignedTo] (NOLOCK) TA INNER JOIN [dbo].[Audiences] (NOLOCK) TAA ON TAA.Id = TA.AudienceId AND TAA.IsActive = 1 WHERE TA.DailyNoteContentVersionId = V.Id ) FROM [dbo].[DailyNoteContent] (NOLOCK) T CROSS APPLY( SELECT TOP 1 V.Id, V.DateAdded, V.CreatedBy, V.ContentType, V.DateDue, V.IsPrivate, V.ProjectId, V.PublishDate, V.AuthorTeamId, V.ContactEmail, V.Content, V.ContentText, V.Summary, V.SummaryText, V.AllowAddendums FROM [dbo].[DailyNoteContentVersion] (NOLOCK) V WHERE V.DailyNoteContentId = T.Id ORDER BY V.DateAdded DESC ) AS V -- Only inlcude published content INNER JOIN [dbo].[DailyNotePublishStatus] (NOLOCK) PS ON PS.PublishDate = V.PublishDate AND PS.RegionId = T.Region AND PS.IsReadyToPublish = 1 -- TODO: Account for user defined publish time/timezones? AND PS.PublishDate <= CONVERT(DATE, GETUTCDATE()) WHERE T.IsDeleted = 0 WITH CHECK OPTION; GO