Delete statement takes too long

Question

I have a ProfileAvatars table with 1000 rows that is the parent table for UserProfile table with 16,557,010 rows.

When I add a new picture (without any child record in UserProfile) and I want to delete it, the Delete statement takes 5-12 seconds to complete the delete query.

This is my query:

DELETE FROM ProfileAvatars
WHERE ProfileAvatarId = 10

Here is the execution plan:

Why is my delete statement taking so long? How can I reduce time needed to delete a single row?

---

Tables definition

ProfileAvatars:

CREATE TABLE [gnr].[ProfileAvatars](
    [ProfileAvatarId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [AvatarPictuerPath] [varchar](250) NOT NULL,
    [Description] [nvarchar](250) NULL,
    [CreateBy] [nvarchar](200) NOT NULL,
    [RecordVersion] [timestamp] NOT NULL,
    [CreatedDateUTC] [datetime] NOT NULL,
    [UpdatedDateUTC] [datetime] NULL,
 CONSTRAINT [PK_ProfileAvatars] PRIMARY KEY CLUSTERED 
(
    [ProfileAvatarId] 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]

UserProfile:

CREATE TABLE [sqr].[UserProfile](
    [ProfileId] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [bigint] NOT NULL,
    [ProfileCaption] [nvarchar](150) NULL,
    [ProfileAvatarId] [int] NOT NULL,
    [MediaAgeRangeId] [int] NOT NULL,
    [IsKid] [bit] NOT NULL,
    [IsDefault] [bit] NOT NULL,
    [Active] [bit] NOT NULL,
    [ApplicationId] [int] NOT NULL,
    [CreateBy] [nvarchar](200) NOT NULL,
    [RecordVersion] [timestamp] NOT NULL,
    [CreatedDateUTC] [datetime] NOT NULL,
    [UpdatedDateUTC] [datetime] NULL,
    [DeletedDateUTC] [datetime] NULL,
    [IsDeleted] [bit] NOT NULL,
    [UserTasteInsertionDateUTC] [datetime] NULL,
    [UserTasteStatus] [int] NULL,
    [UserTasteSkipDateUTC] [datetime] NULL,
    [PinCode] [varchar](20) NULL,
    [IsLock] [bit] NOT NULL,
 CONSTRAINT [PK_UserProfile] PRIMARY KEY CLUSTERED 
(
    [ProfileId] 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]
GO
ALTER TABLE [sqr].[UserProfile] ADD  CONSTRAINT [DF_UserProfile_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO
ALTER TABLE [sqr].[UserProfile] ADD  CONSTRAINT [DF_UserProfile_IsLock]  DEFAULT ((0)) FOR [IsLock]
GO
ALTER TABLE [sqr].[UserProfile]  WITH CHECK ADD  CONSTRAINT [FK_UserProfile_MediaAgeRange] FOREIGN KEY([MediaAgeRangeId])
REFERENCES [gnr].[MediaAgeRange] ([MediaAgeRangeId])
GO
ALTER TABLE [sqr].[UserProfile] CHECK CONSTRAINT [FK_UserProfile_MediaAgeRange]
GO
ALTER TABLE [sqr].[UserProfile]  WITH CHECK ADD  CONSTRAINT [FK_UserProfile_ProfileAvatars] FOREIGN KEY([ProfileAvatarId])
REFERENCES [gnr].[ProfileAvatars] ([ProfileAvatarId])
GO
ALTER TABLE [sqr].[UserProfile] CHECK CONSTRAINT [FK_UserProfile_ProfileAvatars]
GO

Answer 1

Try creating a non-clustered index on your UserProfile table CREATE NONCLUSTERED INDEX IX_UserProfile_ProfileAvatarId ON sqr.UserProfile (ProfileAvatarId);

This will change the scan (that is currently in the plan for foreign key validation) into a seek. A scan is more costly due to the query running through the whole table to find what it needs. By adding the index it gives the query an idea of where to look and turns the scan into a seek.