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
)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?