Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Why are inserts blocking reads? What version of SQL Server are you using? Or do you mean inserts are timing out? –  Jack Douglas May 10 '11 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. –  Michael Hedgpeth May 10 '11 at 13:48
    
what is the rationale behind ALLOW_PAGE_LOCKS=ON? –  Jack Douglas May 10 '11 at 13:54
    
There is no rationale. –  Michael Hedgpeth May 10 '11 at 14:00
    
Maybe it was auto-generated for you? (ALLOW_PAGE_LOCKS=ON) –  Andrei Rinea Apr 4 '12 at 14:57

2 Answers 2

up vote 3 down vote accepted

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)

share|improve this answer
    
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? –  Michael Hedgpeth May 11 '11 at 21:24

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.

share|improve this answer
    
Excellent advice, Sankar. Thanks for the reminder. –  Michael Hedgpeth May 11 '11 at 14:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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