Patterns/Designs to help with concurrency/performance I'm working on a SQL Server 2008 R2 DB and I'm experiencing some performance/concurrency issues with probably the main table in the DB. Problems like slow reads, deadlocks, poor performance in general. 60% of users will read from this table while 40% will write to it. The problem I'm faced with is that the writing is actually a collection of big updates to about 50-100 rows at a time, doing stuff with re-parenting, updating FK's, updating chunks of HTML, etc. These writes are a problem when multiple users are doing similar updates while others are trying to read. Even without reading, the updates alone are an issue.

Unfortunately, breaking down the data in these tables hasn't helped. For e.g. 100 rows may represent a single entity to the end user similar to a document per say, so each row represents a section of a document, these updates treat the "Document" as a single entity which may include actions like merging section information, adding new sections, deleting old sections, updating content, moving sections around etc.

Are there any designs/patterns that can help with scenarios like these???
One point would be to not have locks on reads. This is what I do generally - ReadCommited as table hint helps (or on connection level). This is not repeatable (no locks in place). Retrieval may be isolated into non-locking transactions this way.

Also then possibly go partially to stored procedures - no round trips. Merge statement etc. - the usual "keep locks super small" approach.