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???

  • We'd need to know at least little about the schema to help, and more about the operations. Can you peel out a narrower piece of the problem? – Jon of All Trades Dec 16 '13 at 16:14

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.

| improve this answer | |
  • Unfortunately, it is a requirement to show the latest or best when reading. – DC_AKA_Cremkie Dec 16 '13 at 14:14
  • That wont really be afflicted, though. No change if you plan the concurrency properly. – TomTom Dec 16 '13 at 14:18

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