4

I have spent some time on this issue and finally have an example which reproduces the issue (even with the appropriate non-clustered indexes on all FKs).

This a brief overview of the database operation:

a) The updates/inserts are done under read committed (snapshot) isolation.

b) The removal of items is done under snapshot isolation.

c) There are indexes for all FKs.

The purge process removes rows that are old from the database. After between 20-60 minutes the delete script below will fail with the snapshot error. I have been told that FK checks revert to read committed isolation but even this would not explain what we are seeing below.

Steps to reproduce:

a) Create a database and ensure that the snapshot option is True. We called the database SnapshotTest.

b) Create the database using this script below:

ALTER DATABASE [SnapshotTest] SET READ_COMMITTED_SNAPSHOT ON
GO
CREATE TYPE [dbo].[udtPPChildObject] AS TABLE(
    [InsertionId] [bigint] NOT NULL,
    [ChildInsertionId] [bigint] NOT NULL,
    [PropertyMapNameId] [int] NOT NULL,
    [UpdateId] [bigint] NULL,
    PRIMARY KEY CLUSTERED 
(
    [ChildInsertionId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
CREATE TYPE [dbo].[udtPPChildObjectList] AS TABLE(
    [InsertionId] [bigint] NOT NULL,
    [ChildInsertionId] [bigint] NOT NULL,
    [SortIndex] [int] NULL,
    [UpdateId] [bigint] NULL,
    [SortText] [nvarchar](260) NULL,
    [RemovalThreshold] [bigint] NULL,
    PRIMARY KEY CLUSTERED 
(
    [ChildInsertionId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
CREATE TYPE [dbo].[udtPPGuidList] AS TABLE(
    [Guid] [uniqueidentifier] NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [Guid] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
CREATE TYPE [dbo].[udtPPObject] AS TABLE(
    [InsertionId] [bigint] NOT NULL,
    [ObjectId] [uniqueidentifier] NOT NULL,
    [TypeNameId] [int] NOT NULL,
    [UpdateId] [bigint] NULL,
    [SourceInterpreterID] [uniqueidentifier] NULL,
    [LevelID] [nvarchar](max) NULL,
    [SearchParentInsertionID] [bigint] NULL,
    PRIMARY KEY CLUSTERED 
(
    [InsertionId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
CREATE TYPE [dbo].[udtPPObjectBlobProperty] AS TABLE(
    [InsertionId] [bigint] NOT NULL,
    [PropertyMapNameId] [int] NOT NULL,
    [BlobValue] [varbinary](max) NULL,
    [UpdateId] [bigint] NULL,
    PRIMARY KEY CLUSTERED 
(
    [InsertionId] ASC,
    [PropertyMapNameId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
CREATE TYPE [dbo].[udtPPObjectProperty] AS TABLE(
    [InsertionId] [bigint] NOT NULL,
    [PropertyMapNameId] [int] NOT NULL,
    [UpdateId] [bigint] NOT NULL,
    [BitValue] [bit] NULL,
    [UIDValue] [uniqueidentifier] NULL,
    [FloatValue] [float] NULL,
    [BigIntValue] [bigint] NULL,
    [IntValue] [int] NULL,
    [NVarCharValue] [nvarchar](max) NULL,
    [SearchText] [nvarchar](max) NULL,
    [TypeIndex] [tinyint] NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [InsertionId] ASC,
    [PropertyMapNameId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
CREATE TABLE [dbo].[tblPPObject](
    [ObjectID] [uniqueidentifier] NOT NULL,
    [UpdateTime] [datetime] NULL,
    [InsertionID] [bigint] NOT NULL,
    [SourceInterpreterID] [uniqueidentifier] NULL,
    [LevelID] [nvarchar](260) NULL,
    [UpdateID] [bigint] NULL,
    [TypeNameID] [int] NOT NULL,
    [SearchParentInsertionID] [bigint] NULL,
 CONSTRAINT [PK_tblPPObject] PRIMARY KEY CLUSTERED 
(
    [InsertionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPPObjectBlobProperty](
    [Value] [varbinary](max) NULL,
    [UpdateTime] [datetime] NULL,
    [InsertionID] [bigint] NOT NULL,
    [UpdateID] [bigint] NULL,
    [PropertyMapNameID] [int] NOT NULL,
 CONSTRAINT [PK_tblPPObjectBlobProperty] PRIMARY KEY CLUSTERED 
(
    [InsertionID] ASC,
    [PropertyMapNameID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPPObjectChildObject](
    [UpdateTime] [datetime] NULL,
    [InsertionID] [bigint] NOT NULL,
    [ChildInsertionID] [bigint] NOT NULL,
    [UpdateID] [bigint] NULL,
    [PropertyMapNameID] [int] NULL,
 CONSTRAINT [PK_tblPPObjectChildObject] PRIMARY KEY CLUSTERED 
(
    [InsertionID] ASC,
    [ChildInsertionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPPObjectChildObjectList](
    [SortIndex] [int] NOT NULL,
    [UpdateTime] [datetime] NULL,
    [InsertionID] [bigint] NOT NULL,
    [ChildInsertionID] [bigint] NOT NULL,
    [SortText] [nvarchar](260) NULL,
    [UpdateID] [bigint] NULL,
    [RemovalThreshold] [bigint] NULL,
 CONSTRAINT [PK_tblPPObjectChildObjectList] PRIMARY KEY CLUSTERED 
(
    [InsertionID] ASC,
    [ChildInsertionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPPObjectProperty](
    [UpdateTime] [datetime] NULL,
    [InsertionID] [bigint] NOT NULL,
    [BitValue] [bit] NULL,
    [UIDValue] [uniqueidentifier] NULL,
    [FloatValue] [float] NULL,
    [BigIntValue] [bigint] NULL,
    [IntValue] [int] NULL,
    [NVarCharValue] [nvarchar](max) NULL,
    [TypeIndex] [tinyint] NOT NULL,
    [SearchText] [nvarchar](max) NULL,
    [UpdateID] [bigint] NULL,
    [PropertyMapNameID] [int] NOT NULL,
 CONSTRAINT [PK_tblPPObjectProperty] PRIMARY KEY CLUSTERED 
(
    [InsertionID] ASC,
    [PropertyMapNameID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPPPropertyMapName](
    [PropertyMapName] [varchar](max) NOT NULL,
    [PropertyMapNameID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblPPPropertyMapName] PRIMARY KEY CLUSTERED 
(
    [PropertyMapNameID] 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] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPPRedundantObjects](
    [InsertionID] [bigint] NOT NULL,
    [UpdateTime] [datetime] NULL,
 CONSTRAINT [PK_tblPPRedundantObjects] PRIMARY KEY CLUSTERED 
(
    [InsertionID] 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
CREATE TABLE [dbo].[tblPPTypeName](
    [TypeName] [varchar](max) NOT NULL,
    [TypeNameID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblPPTypeName] PRIMARY KEY CLUSTERED 
(
    [TypeNameID] 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] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObject_LevelID_TypeNameID_InsertionID_INC_ObjectID] ON [dbo].[tblPPObject]
(
    [LevelID] ASC,
    [TypeNameID] ASC,
    [InsertionID] ASC
)
INCLUDE([ObjectID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_tblPPObject_ObjectID_INC_InsertionID_UpdateID_TypeNameID_SourceInterpreterID_LevelID] ON [dbo].[tblPPObject]
(
    [ObjectID] ASC
)
INCLUDE([InsertionID],[UpdateID],[TypeNameID],[SourceInterpreterID],[LevelID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObject_SearchParentInsertionID_SourceInterpreteID_InsertionID_INC_ObjectID_LevelID_UpdateID] ON [dbo].[tblPPObject]
(
    [SearchParentInsertionID] ASC,
    [SourceInterpreterID] ASC,
    [InsertionID] ASC
)
INCLUDE([ObjectID],[LevelID],[UpdateID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObjectBlobProperty_InsertionID] ON [dbo].[tblPPObjectBlobProperty]
(
    [InsertionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObjectChildObject_ChildInsertionID] ON [dbo].[tblPPObjectChildObject]
(
    [ChildInsertionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObjectChildObjectList_ChildInsertionID] ON [dbo].[tblPPObjectChildObjectList]
(
    [ChildInsertionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObjectProperty_InsertionID] ON [dbo].[tblPPObjectProperty]
(
    [InsertionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObjectProperty_InsertionID_TypeIndex_INC_SearchText] ON [dbo].[tblPPObjectProperty]
(
    [InsertionID] ASC,
    [TypeIndex] ASC
)
INCLUDE([SearchText]) 
WHERE ([TypeIndex]=(6))
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPPObjectProperty] ADD  CONSTRAINT [DF_ObjectProperty_SearcText]  DEFAULT (NULL) FOR [SearchText]
GO
ALTER TABLE [dbo].[tblPPObject]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObject_tblPPTypeName] FOREIGN KEY([TypeNameID])
REFERENCES [dbo].[tblPPTypeName] ([TypeNameID])
GO
ALTER TABLE [dbo].[tblPPObject] CHECK CONSTRAINT [FK_tblPPObject_tblPPTypeName]
GO
ALTER TABLE [dbo].[tblPPObjectBlobProperty]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObjectBlobProperty_tblPPObject] FOREIGN KEY([InsertionID])
REFERENCES [dbo].[tblPPObject] ([InsertionID])
GO
ALTER TABLE [dbo].[tblPPObjectBlobProperty] CHECK CONSTRAINT [FK_tblPPObjectBlobProperty_tblPPObject]
GO
ALTER TABLE [dbo].[tblPPObjectBlobProperty]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObjectBlobProperty_tblPPPropertyMapName] FOREIGN KEY([PropertyMapNameID])
REFERENCES [dbo].[tblPPPropertyMapName] ([PropertyMapNameID])
GO
ALTER TABLE [dbo].[tblPPObjectBlobProperty] CHECK CONSTRAINT [FK_tblPPObjectBlobProperty_tblPPPropertyMapName]
GO
ALTER TABLE [dbo].[tblPPObjectChildObject]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObjectChildObject_tblPPObject] FOREIGN KEY([ChildInsertionID])
REFERENCES [dbo].[tblPPObject] ([InsertionID])
GO
ALTER TABLE [dbo].[tblPPObjectChildObject] CHECK CONSTRAINT [FK_tblPPObjectChildObject_tblPPObject]
GO
ALTER TABLE [dbo].[tblPPObjectChildObject]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObjectChildObject_tblPPPropertyMapName] FOREIGN KEY([PropertyMapNameID])
REFERENCES [dbo].[tblPPPropertyMapName] ([PropertyMapNameID])
GO
ALTER TABLE [dbo].[tblPPObjectChildObject] CHECK CONSTRAINT [FK_tblPPObjectChildObject_tblPPPropertyMapName]
GO
ALTER TABLE [dbo].[tblPPObjectChildObjectList]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObjectChildObjectList_tblPPObject] FOREIGN KEY([ChildInsertionID])
REFERENCES [dbo].[tblPPObject] ([InsertionID])
GO
ALTER TABLE [dbo].[tblPPObjectChildObjectList] CHECK CONSTRAINT [FK_tblPPObjectChildObjectList_tblPPObject]
GO
ALTER TABLE [dbo].[tblPPObjectProperty]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObjectProperty_tblPPObject] FOREIGN KEY([InsertionID])
REFERENCES [dbo].[tblPPObject] ([InsertionID])
GO
ALTER TABLE [dbo].[tblPPObjectProperty] CHECK CONSTRAINT [FK_tblPPObjectProperty_tblPPObject]
GO
ALTER TABLE [dbo].[tblPPObjectProperty]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObjectProperty_tblPPPropertyMapName] FOREIGN KEY([PropertyMapNameID])
REFERENCES [dbo].[tblPPPropertyMapName] ([PropertyMapNameID])
GO
ALTER TABLE [dbo].[tblPPObjectProperty] CHECK CONSTRAINT [FK_tblPPObjectProperty_tblPPPropertyMapName]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObjectChildObject_UpdateID] ON [dbo].[tblPPObjectChildObject]
(
    [UpdateID] ASC
)
INCLUDE([ChildInsertionID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObjectChildObjectList_UpdateID] ON [dbo].[tblPPObjectChildObjectList]
(
    [UpdateID] ASC
)
INCLUDE([ChildInsertionID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE procedure [dbo].[spMergePPObject]
(
    @values dbo.udtPPObject readonly
)
as
begin

    begin try

        declare @updatetime datetime
        set @updatetime = GetUTCDate()

        update t
        set t.UpdateTime = @updatetime, t.UpdateId = s.UpdateId, t.TypenameId = s.TypeNameId, t.SourceInterpreterID = s.SourceInterpreterID, t.LevelID = s.LevelID, t.SearchParentInsertionID = s.SearchParentInsertionID           
        from tblPPObject as t
        join @values as s
        on s.InsertionId = t.InsertionID and (s.UpdateID is not null)

        insert into tblPPObject (InsertionId, ObjectId, TypeNameId, UpdateId, UpdateTime, SourceInterpreterID, LevelID, SearchParentInsertionID)  
        select s.InsertionId, s.ObjectId, s.TypeNameId, s.UpdateId, @updatetime, s.SourceInterpreterID, s.LevelID, s.SearchParentInsertionID
        from @values as s
        left join tblPPObject as t
        on s.InsertionID = t.InsertionID
        where t.InsertionID is null

    end try
    begin catch

        declare @errormessage varchar(256)
        select @errormessage = ERROR_MESSAGE()
        raiserror('Error updating entries in the tblPPObject Table. %s', 16, 1, @errormessage)

    end catch

end
GO
CREATE procedure [dbo].[spMergePPObjectBlobProperty]
(
    @values dbo.udtPPObjectBlobProperty readonly
)
as
begin

    begin try

        declare @updatetime datetime
        set @updatetime = GetUTCDate()

        update t
        set t.UpdateTime = @updatetime, t.[Value] = s.BlobValue, t.UpdateId = s.UpdateId
        from tblPPObjectBlobProperty as t
        join @values as s
        on s.InsertionId = t.InsertionID and t.PropertyMapNameId = s.PropertyMapNameID

        insert into tblPPObjectBlobProperty (PropertyMapNameId, Value, UpdateId, UpdateTime, InsertionId) 
        select s.PropertyMapNameId, s.BlobValue, s.UpdateId, @updatetime, s.InsertionId
        from @values as s
        left join tblPPObjectBlobProperty as t
        on s.InsertionID = t.InsertionID and t.PropertyMapNameId = s.PropertyMapNameID
        where t.InsertionID is null and t.PropertyMapNameID is null

    end try
    begin catch

        declare @errormessage varchar(256)
        select @errormessage = ERROR_MESSAGE()
        raiserror('Error updating entries in the tblPPObjectBlobProperty Table. %s', 16, 1, @errormessage)

    end catch

end
GO
create procedure [dbo].[spMergePPObjectChildObject]
(
    @values dbo.udtPPChildObject readonly
)
as
begin

    begin try

        declare @updatetime datetime
        set @updatetime = GetUTCDate()

        update t
        set t.UpdateID = s.UpdateID, t.UpdateTime = @updatetime
        from tblPPObjectChildObject as t
        join @values as s
        on s.InsertionId = t.InsertionID and t.ChildInsertionId = t.ChildInsertionID

        insert into tblPPObjectChildObject (InsertionID, ChildInsertionID, PropertyMapNameID, UpdateID, UpdateTime) 
        select s.InsertionID, s.ChildInsertionID, s.PropertyMapNameID, s.UpdateID, @updatetime
        from @values as s
        left join tblPPObjectChildObject as t
        on s.InsertionId = t.InsertionID and s.ChildInsertionID = t.ChildInsertionID
        where t.ChildInsertionID is null

    end try
    begin catch

        declare @errormessage varchar(256)
        select @errormessage = ERROR_MESSAGE()
        raiserror('Error updating entries in the tblPPObjectChildObject Table. %s', 16, 1, @errormessage)

    end catch

end
GO
create procedure [dbo].[spMergePPObjectChildObjectList]
(
    @values dbo.udtPPChildObjectList readonly
)
as
begin

    begin try

        declare @updatetime datetime
        set @updatetime = GetUTCDate()

        update t
        set t.UpdateID = isnull(s.UpdateID, t.UpdateID), t.UpdateTime = @updatetime, t.RemovalThreshold = isnull(s.RemovalThreshold, t.RemovalThreshold), t.SortIndex = isnull(s.SortIndex, t.SortIndex), t.SortText = isnull(s.SortText, t.SortText)
        from tblPPObjectChildObjectList as t
        join @values as s
        on s.InsertionID = t.InsertionID and s.ChildInsertionId = t.ChildInsertionID

        insert into tblPPObjectChildObjectList (InsertionID, ChildInsertionID, SortIndex, UpdateID, UpdateTime, SortText, RemovalThreshold) 
        select s.InsertionID, s.ChildInsertionID, isnull(s.SortIndex, -1), s.UpdateID, @updatetime, s.SortText, s.RemovalThreshold
        from @values as s
        left join tblPPObjectChildObjectList as t
        on s.InsertionId = t.InsertionID and s.ChildInsertionID = t.ChildInsertionID
        where t.ChildInsertionID is null

    end try
    begin catch

        declare @errormessage varchar(256)
        select @errormessage = ERROR_MESSAGE()
        raiserror('Error updating entries in the tblPPObjectChildObjectList Table. %s', 16, 1, @errormessage)

    end catch

end
GO
CREATE procedure [dbo].[spMergePPObjectProperty]
(
    @values dbo.udtPPObjectProperty readonly
)
as
begin

    begin try

        declare @updatetime datetime
        set @updatetime = GetUTCDate()

        update t 
        set t.UpdateTime = @updatetime, t.BitValue = s.BitValue, t.UIDValue = s.UIDValue, t.FloatValue = s.FloatValue, t.IntValue = s.IntValue, t.NVarCharValue = s.NVarCharValue, t.BigIntValue = s.BigIntValue, t.UpdateId = s.UpdateId, t.TypeIndex = s.TypeIndex, t.SearchText = s.SearchText
        from tblPPObjectProperty as t
        join @values as s
        on s.InsertionId = t.InsertionID and t.PropertyMapNameId = s.PropertyMapNameID

        insert into tblPPObjectProperty (InsertionId, PropertyMapNameId, UpdateID, UpdateTime, BitValue, UIDValue, FloatValue, IntValue, NVarCharValue, BigIntValue, TypeIndex, SearchText)
        select s.InsertionId, s.PropertyMapNameId, s.UpdateId, @updatetime, s.BitValue, s.UidValue, s.floatValue, s.IntValue, s.NVarCharValue, s.BigIntValue, s.TypeIndex, s.SearchText
        from @values as s
        left join tblPPObjectProperty as t
        on s.InsertionID = t.InsertionID and t.PropertyMapNameId = s.PropertyMapNameID
        where t.InsertionID is null and t.PropertyMapNameID is null

    end try
    begin catch

        declare @errormessage varchar(256)
        select @errormessage = ERROR_MESSAGE()
        raiserror('Error updating entries in the tblPPObjectProperty Table. %s', 16, 1, @errormessage)

    end catch

end
GO
create procedure [dbo].[spPurge](@modelinsertionid bigint)
as
begin

    set deadlock_priority low
    set nocount on

    print 'Start Purge'

    -- Work out what can be removed
    begin transaction

    declare @RedundantInsertionIDs table (InsertionID bigint PRIMARY KEY, UpdateTime DateTime)
        
    declare @currenttime datetime
    set @currenttime = GetUTCDate()

    insert into @RedundantInsertionIDs 
    select pocol.ChildInsertionId, @currenttime from tblPPObjectChildObjectList as pocol
    where pocol.UpdateID = -1

    insert into @RedundantInsertionIDs 
    select poco.ChildInsertionId, @currenttime from tblPPObjectChildObject as poco 
    where poco.UpdateID = -1

    delete from tblPPRedundantObjects
    from @RedundantInsertionIDs as ri
    right join tblPPRedundantObjects as ro
    on ri.InsertionID = ro.InsertionID
    where (ri.InsertionID is null) and (ro.UpdateTime is not null)

    insert into tblPPRedundantObjects
    select ri.InsertionID, @currenttime from @RedundantInsertionIDs as ri
    left join tblPPRedundantObjects as ro
    on ri.InsertionID = ro.InsertionID
    where ro.InsertionID is null

    commit transaction;

    -- Carry out the removal
    declare @RedundantInsertionIDsToRemove table (InsertionID bigint, PRIMARY KEY (InsertionID))

    declare @thresholddatetime datetime
    set @thresholddatetime = DATEADD(mi, -2, @currenttime)

    declare @purgecount int = 500
    declare @removalcount int

    delete top (@purgecount) from tblPPRedundantObjects
    output deleted.InsertionID into @RedundantInsertionIDsToRemove
    from tblPPRedundantObjects as ro
    where (ro.UpdateTime is null) or (ro.UpdateTime < @thresholddatetime)

    declare @ObjectIDs table (ObjectID uniqueidentifier, PRIMARY KEY (ObjectID))

    insert into @ObjectIDs 
    select ObjectID from tblPPObject as po
    join @RedundantInsertionIDsToRemove as ii
    on po.InsertionID = ii.InsertionID

    insert into tblPPRedundantObjects
    select ChildInsertionID, null from tblPPObjectChildObjectList as pocol 
    join @RedundantInsertionIDsToRemove as ii on pocol.InsertionID = ii.InsertionID
    where not exists (select InsertionID from tblPPRedundantObjects where InsertionID = ChildInsertionID)

    insert into tblPPRedundantObjects
    select ChildInsertionID, null from tblPPObjectChildObject as poco 
    join @RedundantInsertionIDsToRemove as ii on poco.InsertionID = ii.InsertionID
    where not exists (select InsertionID from tblPPRedundantObjects where InsertionID = ChildInsertionID)

    delete from op  
    from tblPPObjectProperty as op 
    join @RedundantInsertionIDsToRemove as ii
    on op.InsertionID = ii.InsertionID

    delete from bop 
    from tblPPObjectBlobProperty as bop 
    join @RedundantInsertionIDsToRemove as ii
    on bop.InsertionID = ii.InsertionID

    delete from poco 
    from tblPPObjectChildObject as poco 
    join @RedundantInsertionIDsToRemove as ii
    on poco.ChildInsertionID = ii.InsertionID

    delete from pocol  
    from tblPPObjectChildObjectList as pocol 
    join @RedundantInsertionIDsToRemove as ii
    on pocol.ChildInsertionID = ii.InsertionID

    print 'Remove objects'

    delete from po 
    from tblPPObject as po  
    join @RedundantInsertionIDsToRemove as ii
    on po.InsertionID = ii.InsertionID

    print 'End Purge'

    select ObjectID
    from @ObjectIDs
    
end
GO

c) Run this update/insert query in one session:

delete from tblPPObjectBlobProperty
delete from tblPPObjectProperty
delete from tblPPObjectChildObject
delete from tblPPObjectChildObjectList
delete from tblPPObject
delete from tblPPPropertyMapName
delete from tblPPRedundantObjects
delete from tblPPTypeName
go

DBCC CHECKIDENT (tblPPPropertyMapName, RESEED, 0)
DBCC CHECKIDENT (tblPPTypeName, RESEED, 0)

declare @index int = 1
while (@index <= 1000)
begin

    insert into [dbo].tblPPPropertyMapName select 'Test ' + cast(@index as varchar)
    insert into [dbo].tblPPTypeName select 'Test ' + cast(@index as varchar)
    set @index = @index + 1

end
go
    
DECLARE @values1 dbo.udtPPObject
DECLARE @values2 dbo.udtPPChildObject
DECLARE @values3 dbo.udtPPChildObjectList
DECLARE @values4 dbo.udtPPObjectProperty
DECLARE @values5 dbo.udtPPObjectBlobProperty

declare @insertionid bigint = 0
declare @childobjectinsertionid bigint
declare @childobjectlistinsertionid bigint

while (1 = 1)
begin

    WAITFOR DELAY '00:00:01'

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    BEGIN TRANSACTION;

    declare @updatetime datetime
    set @updatetime = GetUTCDate()

    set @insertionid = @insertionid + 1
    set @childobjectinsertionid = @insertionid + 1
    set @childobjectlistinsertionid = @childobjectinsertionid + 1

    delete from @values1
    delete from @values2
    delete from @values3
    delete from @values4
    delete from @values5

    insert into @values1 select @insertionid, NEWID(), 1, 1, null, null, null
    insert into @values1 select @childobjectinsertionid, NEWID(), 1, 2, null, null, null
    insert into @values1 select @childobjectlistinsertionid, NEWID(), 1, 3, null, null, null

    insert into @values2 select @insertionid, @childobjectinsertionid, 1, -1
    insert into @values3 select @insertionid, @childobjectlistinsertionid, -1, -1, null, null

    -- Updates
    if ((@insertionid - 20) > 0)
    begin
    
        declare @i1 bigint = (((@insertionid - 20)/ 3) * 3) + 1

        print 'Updating child object insertion id ' + cast((@i1 + 1) as varchar(max))
        insert into @values2 select @i1, @i1 + 1, 1, -1

        print 'Updating child object list insertion id ' + cast((@i1 + 2) as varchar(max))
        insert into @values3 select @i1, @i1 + 2, -1, -1, null, null

    end

    declare @index int = 1
    while (@index <= 100)
    begin

        insert into @values4 select @childobjectinsertionid, @index, -1, 1, null, null, null, null, null, null, 1
        insert into @values5 select @childobjectlistinsertionid, @index, null, -1

        set @index = @index + 1

    end

    exec spMergePPObject @values1
    exec spMergePPObjectChildObject @values2
    exec spMergePPObjectChildObjectList @values3
    exec spMergePPObjectProperty @values4
    exec spMergePPObjectBlobProperty @values5

    set @insertionid = @childobjectlistinsertionid

    commit transaction;

end

d) Run this update/insert query in another session (a few seconds after the query in (c)):

DECLARE @values1 dbo.udtPPObject
DECLARE @values2 dbo.udtPPChildObject
DECLARE @values3 dbo.udtPPChildObjectList
DECLARE @values4 dbo.udtPPObjectProperty
DECLARE @values5 dbo.udtPPObjectBlobProperty

declare @insertionid bigint = 900000000
declare @childobjectinsertionid bigint
declare @childobjectlistinsertionid bigint

while (1 = 1)
begin

    WAITFOR DELAY '00:00:01'

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    BEGIN TRANSACTION;

    declare @updatetime datetime
    set @updatetime = GetUTCDate()

    set @insertionid = @insertionid + 1
    set @childobjectinsertionid = @insertionid + 1
    set @childobjectlistinsertionid = @childobjectinsertionid + 1

    delete from @values1
    delete from @values2
    delete from @values3
    delete from @values4
    delete from @values5

    insert into @values1 select @insertionid, NEWID(), 1, 1, null, null, null
    insert into @values1 select @childobjectinsertionid, NEWID(), 1, 2, null, null, null
    insert into @values1 select @childobjectlistinsertionid, NEWID(), 1, 3, null, null, null

    insert into @values2 select @insertionid, @childobjectinsertionid, 1, -1
    insert into @values3 select @insertionid, @childobjectlistinsertionid, -1, -1, null, null

    -- Updates
    if ((@insertionid - 900000020) > 0)
    begin
    
        declare @i1 bigint = (((@insertionid - 20)/ 3) * 3) + 1

        print 'Updating child object insertion id ' + cast((@i1 + 1) as varchar(max))
        insert into @values2 select @i1, @i1 + 1, 1, -1

        print 'Updating child object list insertion id ' + cast((@i1 + 2) as varchar(max))
        insert into @values3 select @i1, @i1 + 2, -1, -1, null, null

    end

    declare @index int = 1
    while (@index <= 100)
    begin

        insert into @values4 select @childobjectinsertionid, @index, -1, 1, null, null, null, null, null, null, 1
        insert into @values5 select @childobjectlistinsertionid, @index, null, -1

        set @index = @index + 1

    end

    exec spMergePPObject @values1
    exec spMergePPObjectChildObject @values2
    exec spMergePPObjectChildObjectList @values3
    exec spMergePPObjectProperty @values4
    exec spMergePPObjectBlobProperty @values5

    set @insertionid = @childobjectlistinsertionid

    commit transaction;

end

e) Finally, run the delete query in another session:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

while (1 = 1)
begin

    WAITFOR DELAY '00:00:05'

    exec spPurge 0 

end

So this is constantly inserting/updating rows in the tables and the spPurge procedure is removing them when they are no longer in use and they are at least 2 minutes old.

The issue happens in the final part of the part procedure i.e.

delete from po 
from tblPPObject as po  
join @RedundantInsertionIDsToRemove as ii
on po.InsertionID = ii.InsertionID

Taking off the FKs resolves the issue but this is not a solution to the problem. Since the rows accessed for the FK check do not clash with those being inserted/updated this should not be an issue should it?

What I do not understand is that the error is occurring on the delete from tbPPObject part of the purge procedure. The purge call is not in a transaction so my understanding is that each statement executed is in its own transaction so previous scans etc should make no difference? The rows being removed are not being updated at the same time so this error makes no sense.

And also this error is very intermittent - it will take between 20 and 60 mins to fail with scripts constantly inserting, updating and deleting rows. If this was an issue with the purge procedure it would happen much faster than that.

1 Answer 1

5

The delete execution plans show merge joins with full scans so all rows are touched. The culprit is the table variable in the dbo.spPurge procedure:

declare @RedundantInsertionIDs table (InsertionID bigint, UpdateTime DateTime)

This lacks an index to help optimize the delete queries. I observed that a primary key on the table variable improves the plan such that the target tables are accessed with a seek instead of scan, touching only those rows to be deleted rather than the active rows too.

declare @RedundantInsertionIDs table (InsertionID bigint primary key, UpdateTime DateTime);

If you still experience issues, a temporary table instead of table variable may help. You could also implment a retry in the purge proc as a last resort.

2
  • I am using SQL Server 2019 as well. From what I can see the new plan still shows scans on tblPPObjectChildObject and tblPPObjectChildObjectList which I guess cause the issues? I have added new non-clustered indexes on the UpdateID column (including the ChildInsertionID column) on the tblPPObjectChildObject and tblPPObjectChildObjectList tables. See brentozar.com/pastetheplan/?id=SkTlNd-Ct There are now no table scans on the main tables and issue persists.
    – Sputnik
    Jan 28 at 13:50
  • @Sputnik, I missed that there are still a couple of scans in the in the improved plan. It's going to boil down to query/index tuning to mitigate the issue.
    – Dan Guzman
    Jan 28 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.