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?
HighSchoolCourseGrade
), and then delete fromHighSchoolGradeLevel
, is it faster (what is "so bad", anyway)? You shouldn't have to roll your own cascading deletes, of course, but I wonder if a poor execution plan is amplifying the problem. – Jon of All Trades Oct 11 '17 at 20:57