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:

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 HedgpethMay 10 '11 at 13:48

what is the rationale behind ALLOW_PAGE_LOCKS=ON?
–
Jack Douglas♦May 10 '11 at 13:54

2 Answers
2

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)

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 HedgpethMay 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.