Unused Memory Grant on Delete

Question

I have this delete statement

delete from dbo.HighSchoolGradeLevel where Id=@P0

This is the execution plan

https://www.brentozar.com/pastetheplan/?id=H1qzvl3nW

when the application is busy and there are many deletes coming, the performance become so bad, My questions are.

How to make this delete perform better?

why the granted memory is so high, it steals the memory from the buffer pool ?

Note: there is on delete cascade on the 2 tables Highschoolcourse and highschoolgrade.

CREATE TABLE [dbo].[HighSchoolCourseGrade](
    [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [TermSubTypeLkupId] [int] NULL,
    [Grade] [varchar](50) NULL,
    [GradePoint] [float] NULL,
    [CourseId] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [UpdatedDate] [datetime] NOT NULL,
    [CourseUnits] [float] NULL,
 CONSTRAINT [PK_HighSchoolCourseGrade] 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]

GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [ix_HighSchoolCourseGrade_CourseId_includes]    Script Date: 10/11/2017 4:33:38 PM ******/
CREATE NONCLUSTERED INDEX [ix_HighSchoolCourseGrade_CourseId_includes] ON [dbo].[HighSchoolCourseGrade]
(
    [CourseId] ASC
)
INCLUDE (   [Id],
    [TermSubTypeLkupId],
    [Grade],
    [GradePoint],
    [CreatedDate],
    [UpdatedDate],
    [CourseUnits]) 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]
GO

ALTER TABLE [dbo].[HighSchoolCourseGrade]  WITH CHECK ADD  CONSTRAINT [FK_HighSchoolCourseGrade_CourseId] FOREIGN KEY([CourseId])
REFERENCES [dbo].[HighSchoolCourse] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[HighSchoolCourseGrade] CHECK CONSTRAINT [FK_HighSchoolCourseGrade_CourseId]
GO


CREATE TABLE [dbo].[HighSchoolCourse](
    [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [CourseName] [varchar](200) NULL,
    [CourseTypeLkupId] [int] NULL,
    [EntryTypeLkupId] [int] NULL,
    [SubjectKey] [int] NULL,
    [GradeLevelId] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [UpdatedDate] [datetime] NOT NULL,
    [TotalCourseUnit] [float] NULL,
    [TotalGradePoint] [float] NULL,
 CONSTRAINT [PK_HighSchoolCourse] 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]

GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [ix_HighSchoolCourse_GradeLevelId_includes]    Script Date: 10/11/2017 4:32:58 PM ******/
CREATE NONCLUSTERED INDEX [ix_HighSchoolCourse_GradeLevelId_includes] ON [dbo].[HighSchoolCourse]
(
    [GradeLevelId] ASC,
    [SubjectKey] ASC
)
INCLUDE (   [Id],
    [CourseName],
    [CourseTypeLkupId],
    [EntryTypeLkupId],
    [CreatedDate],
    [UpdatedDate],
    [TotalCourseUnit],
    [TotalGradePoint]) 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]
GO

ALTER TABLE [dbo].[HighSchoolCourse]  WITH CHECK ADD  CONSTRAINT [FK_HighSchoolCourse_GradeLevelId] FOREIGN KEY([GradeLevelId])
REFERENCES [dbo].[HighSchoolGradeLevel] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[HighSchoolCourse] CHECK CONSTRAINT [FK_HighSchoolCourse_GradeLevelId]
GO

This is from sp_blitzcahce

Update

I find this Delete in the Query Store , there were 3 plans and I forced SQL to use different plan. I am wondering Why SQL choose to use the most expensive execution plan?

Answer 1

1. Try adding Id as one of the key columns of ix_HighSchoolCourseGrade_CourseId_includes. That may help with the additional sort needs as well as back the foreign key.

2. If you can change the foreign key, remove the cascading actions and do your deletes from the referencing tables first. Cascading foreign keys use serializable locks behind the scenes, and when deleting large amounts of data can cause bad blocking scenarios.

3. If you can change code, do your deletes via stored proc. That's easier than creating plan guides for app code. It gives you many tuning options to help tune down the memory grants as well. You can use MAX_GRANT_PERCENT among other things (recompile, etc.) to get a more appropriate plan.

Making the index change should be your first effort, though.