7

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?

5
  • Why are inserts blocking reads? What version of SQL Server are you using? Or do you mean inserts are timing out? May 10, 2011 at 13:38
  • Inserts are blocking other inserts. The error I'm getting is: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. I'm using SQL Server 2008. May 10, 2011 at 13:48
  • what is the rationale behind ALLOW_PAGE_LOCKS=ON? May 10, 2011 at 13:54
  • There is no rationale. May 10, 2011 at 14:00
  • Maybe it was auto-generated for you? (ALLOW_PAGE_LOCKS=ON) Apr 4, 2012 at 14:57

2 Answers 2

4

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)

1
  • I couldn't find any literature that would help me understand why ALLOW_PAGE_LOCKS = OFF would help me with insert contention. I want to take an evidence-based approach to solving this problem, but I don't know enough to do that. Any suggestions on where to go? May 11, 2011 at 21:24
4

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.

1
  • Excellent advice, Sankar. Thanks for the reminder. May 11, 2011 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.