Occasional INSERTs taking 100 times longer than normal

Question

I have an audit table with millions of rows. I indexed it and can live with the performance of the queries, but now I notice that the inserts are not always quick.

Under load, on average every 10th insert takes 500ms, although it's a bit random and the lengthy inserts seem to come in clusters of 3 like this (1st col = millisecs):

Dur SQL
  2 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  2 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  2 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
532 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
299 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
586 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  2 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  1 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  1 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  5 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  4 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  3 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  1 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  5 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  1 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  4 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  3 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  1 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
453 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
597 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
452 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  2 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)
  2 INSERT INTO modification (forecastId, userId, epochTime, description, auxText, auxDate) VALUES (?,?,?,?,?,?)

What could the problem be?

This is the sequel (sorry couldn't stop myself) to Need to improve performance query a table with millions of rows - SQL Server

This is the DDL:

CREATE TABLE [dbo].[modification] (
  [id] [int] IDENTITY,
  [userId] [int] NOT NULL,
  [epochTime] [bigint] NOT NULL,
  [forecastId] [int] NOT NULL,
  [description] [char](12) NOT NULL,
  [auxText] [text] NULL,
  [auxDate] [date] NULL
);


ALTER TABLE [dbo].[modification] ADD CONSTRAINT PK_mod2 PRIMARY KEY(ID);

ALTER TABLE [dbo].[modification]  WITH CHECK
ADD CONSTRAINT [FK_modification_forecastId] FOREIGN KEY([forecastId])
REFERENCES [dbo].[forecast] ([id]);

ALTER TABLE [dbo].[modification]  WITH CHECK
ADD CONSTRAINT [FK_mod_userId] FOREIGN KEY([userId])
REFERENCES [dbo].[user] ([id]);

CREATE NONCLUSTERED INDEX IX_modification_last_save 
   ON [dbo].[modification] (userId, forecastId, description, epochTime);

And I have tried to reduce locking with the NO LOCK clause on the queries:

SELECT name, epochTime, auxDate 
FROM modification WITH (NOLOCK) 
JOIN [user] ON [user].id = modification.userId 
WHERE forecastId = ? AND description = ? 
ORDER BY epochTime DESC

Update

I dropped the index above, and it made no difference to the timings.

I disabled the foreign keys, and the bottleneck disappeared. I then worked out it was just because of the forecastId foreign key constraint. It's indexed just like the userId FK.

The only difference is on the actual parent tables, where the forecast table has an extra clause FILLFACTOR in the index creation which user doesn't have:

ALTER TABLE [dbo].[forecast] ADD  CONSTRAINT [PK_forecast] 
    PRIMARY KEY CLUSTERED 
    (
        [id] ASC
    ) WITH (
        PAD_INDEX = OFF, 
        STATISTICS_NORECOMPUTE = OFF, 
        SORT_IN_TEMPDB = OFF,  
        IGNORE_DUP_KEY = OFF, 
        ONLINE = OFF,  
        ALLOW_ROW_LOCKS = ON, 
        ALLOW_PAGE_LOCKS = ON, 
        FILLFACTOR = 95
     ) ON [PRIMARY]

Could that FILLFACTOR be the problem?

It could also be that it's rebuilding indexes when you do the inserts. Have you tried disabling the indexes on the table and running the inserts again to compare the results? — Siyual, May 16 '17 at 15:16
File growth is another contender. Is instant file initialization on? Try sizing your datafile appropriately before inserting. Make sure you *don't* use percentage-based sizing for files, this is Satan's tool. Use a fixed size that can be allocated quickly (say 8 MB for starters). — Jeroen Mostert, May 16 '17 at 15:28
Again I would recommend you stop using the TEXT datatype. And NO LOCK may help reduce some locking but it has a lot of extra baggage with it. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ — Sean Lange, May 16 '17 at 15:32
Is that TEXT column really slowing it down? It's a legacy db and I don't want to make changes unless it helps. — Adam, May 16 '17 at 15:36
Jeroen, are you talking about `FILLFACTOR` on the primary key index? — Adam, May 16 '17 at 15:55
Do all your target foreign keys have indexes on them (to make quick look-ups)? Just curious. That's best practice, but it's usually for delete performance (since SQL Server has to make sure the row can be deleted, it has to look up in the other table to ensure a row does NOT exist before the delete can succeed, otherwise it throws an FK constraint error). Without an index, it has to do a table scan. Just FYI. — pmbAustin, May 16 '17 at 16:04
@Adam: no, not at all. `FILLFACTOR` almost certainly isn't an issue unless you've set it to something other than the default. I'm talking about [your database files and their growth](https://www.simple-talk.com/sql/database-administration/managing-data-growth-in-sql-server/) and [instant file initialization](https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization). — Jeroen Mostert, May 16 '17 at 17:21

Occasional INSERTs taking 100 times longer than normal

0 Answers0