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

share|improve this question

closed as off-topic by Mark Storey-Smith, Paul White, RolandoMySQLDBA, bluefeet, Remus Rusanu Dec 16 '13 at 14:45

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – Mark Storey-Smith, bluefeet, Remus Rusanu
If this question can be reworded to fit the rules in the help center, please edit the question.

    
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

1 Answer 1

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.

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