What is the best practice for storing image varbinary(max) data in a database table?

Question

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?

Answer 1

You might try changing the id generation so inserts are not contending with each other, or consider setting ALLOW_PAGE_LOCKS = OFF, noting the implications for index maintenance (which are probably only relevant if you are also doing updates)

Answer 2

Since you mentioned FILESTREAM, I expect you are using SQL Server 2008. Instead of guessing what is the bottleneck and how to improve, you should identify it using Extended Events and doing a load test [http://www.datamanipulation.net/sqlquerystress/] on this activity.

http://blogs.technet.com/b/sqlos/archive/2008/07/18/debugging-slow-response-times-in-sql-server-2008.aspx

Once you identify the bottleneck, figuring out the solution will always be easy, correct and right on the money.