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?
Best Answer
Try adding
Id
as one of the key columns ofix_HighSchoolCourseGrade_CourseId_includes
. That may help with the additional sort needs as well as back the foreign key.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.
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.