Every 3rd Insert Is Slow On Ms Sql 2008

Question

I have a function that writes 3 lines into a empty table like so:

INSERT [dbo].[yaf_ForumAccess] ([GroupID], [ForumID], [AccessMaskID]) VALUES (1, 8, 1) 
INSERT [dbo].[yaf_ForumAccess] ([GroupID], [ForumID], [AccessMaskID]) VALUES (2, 8, 4)
INSERT [dbo].[yaf_ForumAccess] ([GroupID], [ForumID], [AccessMaskID]) VALUES (3, 8, 3)

For some reason only the third query takes a long time to execute - and with each insert it grows longer.

Profiler Image

I have tried disabling all constraints on the table - same result. I just can't figure out why the first two would run so fast - and the last one would take so long.

Any help would be greatly appreciated.

Here is the statistics for a query ran MSSMS:

Query:

ALTER TABLE [dbo].[yaf_ForumAccess] NOCHECK CONSTRAINT ALL
INSERT [dbo].[yaf_ForumAccess] ([GroupID], [ForumID], [AccessMaskID]) VALUES (1, 9, 1) 
INSERT [dbo].[yaf_ForumAccess] ([GroupID], [ForumID], [AccessMaskID]) VALUES (2, 9, 4)
INSERT [dbo].[yaf_ForumAccess] ([GroupID], [ForumID], [AccessMaskID]) VALUES (3, 9, 3)
ALTER TABLE [dbo].[yaf_ForumAccess] CHECK CONSTRAINT ALL

Stats:

Stats

STATS IO ON:

Table 'yaf_vaccess_group'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_UserGroup'. Scan count 1, logical reads 709, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_AccessMask'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_Group'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_ForumAccess'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)
Table 'yaf_vaccess_group'. Scan count 0, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_UserGroup'. Scan count 1, logical reads 709, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_AccessMask'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_Group'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_ForumAccess'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)
Table 'yaf_vaccess_group'. Scan count 0, logical reads 1340999, physical reads 0, read-ahead reads 3326, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_UserGroup'. Scan count 1, logical reads 709, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_Group'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_AccessMask'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_ForumAccess'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)

yaf_vaccess_group View:

CREATE VIEW [dbo].[yaf_vaccess_group]
WITH SCHEMABINDING
AS
        select
            b.UserID,
            c.ForumID,
            d.AccessMaskID,
            b.GroupID,
            ReadAccess      = convert(int,d.Flags & 1),
            PostAccess      = convert(int,d.Flags & 2),
            ReplyAccess     = convert(int,d.Flags & 4),
            PriorityAccess  = convert(int,d.Flags & 8),
            PollAccess      = convert(int,d.Flags & 16),
            VoteAccess      = convert(int,d.Flags & 32),
            ModeratorAccess = convert(int,d.Flags & 64),
            EditAccess      = convert(int,d.Flags & 128),
            DeleteAccess    = convert(int,d.Flags & 256),
            UploadAccess    = convert(int,d.Flags & 512),
            DownloadAccess  = convert(int,d.Flags & 1024),
            AdminGroup      = convert(int,e.Flags & 1)
        from
            [dbo].[yaf_UserGroup] b
            INNER JOIN [dbo].[yaf_ForumAccess] c on c.GroupID=b.GroupID
            INNER JOIN [dbo].[yaf_AccessMask] d on d.AccessMaskID=c.AccessMaskID
            INNER JOIN [dbo].[yaf_Group] e on e.GroupID=b.GroupID

CREATE UNIQUE CLUSTERED INDEX [yaf_vaccess_group_UserForum_PK] ON [dbo].[yaf_vaccess_group] 
(
    [UserID] ASC,
    [ForumID] ASC,
    [AccessMaskID] ASC,
    [GroupID] ASC
)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) ON [PRIMARY]
GO

STATS IO ON w/ 4 Inserts:

Table 'yaf_vaccess_group'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_UserGroup'. Scan count 1, logical reads 709, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_AccessMask'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_Group'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_ForumAccess'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)
Table 'yaf_vaccess_group'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_UserGroup'. Scan count 1, logical reads 709, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_AccessMask'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_Group'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_ForumAccess'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)
Table 'yaf_vaccess_group'. Scan count 0, logical reads 1220894, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_UserGroup'. Scan count 1, logical reads 709, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_Group'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_AccessMask'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_ForumAccess'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)
Table 'yaf_vaccess_group'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_UserGroup'. Scan count 1, logical reads 709, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_AccessMask'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'yaf_ForumAccess'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)

Answer 1

From what you've shown, it's not the third query that is taking the time, its the part that is actually turning on the check constraints.

When the constraint is turned back on it has to validate that all of the records are good. This means scanning the entire table. Depending on size, it's probably IO bound and beating the crap out of your harddrives.

So, I think you need to make a decision. Either drop the constraints completely or stop flipping them on and off.

Answer 2

The issue is probably caused by the index on the view vaccess_group. Because the view has no padding, adding records will require SQL Server to split the index frequently and it may be that after every third index it fees that an index rebuild is the most optimal way t maintain the index structure/performance.

Try adding some padding to the index. See http://msdn.microsoft.com/en-us/library/ms188783.aspx for info.

CREATE UNIQUE CLUSTERED INDEX [yaf_vaccess_group_UserForum_PK] ON [dbo].[yaf_vaccess_group]
( 
    [UserID] ASC, 
    [ForumID] ASC, 
    [AccessMaskID] ASC, 
    [GroupID] ASC
)
WITH
(
    PAD_INDEX = ON,  -- CHANGE HERE
    FILLFACTOR = 50, -- AND HERE
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY]

BTW, the reason I failed to spot this index in the YAF database build scripts is because, annoyingly, it's not in indexes.sql. For some reason they've put all view indexes into constraints.sql instead!