I have a table that stores images that range in size between 16-100 KB each. Since the images are so small, I've taken Microsoft's advice and not used the FILESTREAM data type. The table is constructed simply:
CREATE TABLE Screenshot( Id bigint NOT NULL, Data varbinary(max) NOT NULL, CONSTRAINT PK_Screenshot 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]
The table is heavily inserted into (2 million records in the past week) and rarely selected. The key is using a hilo algorithm, so for the most part new rows are added at the end.
I've been having problems when a lot of processes try to insert into this table because of locking and contention. Queries are timing out from waiting for locks.
Should I migrate this table to its own file group and drive? How can I improve the insert performance and decrease contention in this type of situation?