What is the best practice for implementing a data table with many inserts, deletes and reads?

Question

I am creating a functionality where our wcf services logs all changes that are stored thru them and the changes need to be sent to other systems.

After every service call with changes we store the changes in a table (the changes is serialized). Regulary we have biztalk to pull the changes from the table and delete the one that is pulled.

This means that during high load the amount of inserts and delete are high, and we struggle with that the select times out because it is blocked by the inserts.

I have tried to play with different Isolation levels, but have not found anything that works yet.

We use ado.net and sql server 2005 for this.

What is best practice for implementing a data table with many inserts, deletes and read, when using sql server 2005 and ado.net.

Edited: Our problem in our today solution is that all the continues inserts are stopping all reads from the table. Probably because if a clustred index scan that I see no good way to remove for the moment.

Answer 1

This is something that your DBA should be doing. Are you the DBA? If so then you'll need to look at running some performance tests to see what the bottleneck is. This article should help as an introduction to performance tuning: http://www.devx.com/getHelpOn/Article/8202/

Answer 2

If your application's semantics allow, consider doing inserts into a separate "auxiliary table" and periodically applying them to the "master table" in a single large transaction; deletes can be handled similarly (where you'd now do a delete, do instead an insert into a separate auxiliary table of a record identifying the master table record to delete, and periodically perform a bunch of deletes from the master table in a single big transaction).

Of course, if you do that the master table won't reflect "the very latest state" but "the state as of a few minutes [or whatever time units] ago", which is why I say "if semantics allow". Sometimes it's OK to have many SELECTs rely on "state as of last update" (i.e., what's in the master table) and a few SELECTs that really need to reflect instantly the latest updates can be satisfied (maybe a bit slower) with more complex queries using both the master and auxiliary tables (via UNION, joins or whatever, depending on the details of the semantics you need to implement).

Answer 3

You mention that you've tried different isolation levels, but have you tried snapshot isolation?

Snapshot isolation basically makes a copy of the row before an insert/update, and any query that attempts to select that row gets the old copy until the insert is completed. This means that you'll occasionally end up with slightly older data, but selects won't block while inserts are happening.

The downside of this is that your inserts/updates will take longer, due to the need to take a copy. I'm not sure how much of a penalty it incurs though.

Answer 4

On the DB side:

• Try using the locking hint called READPAST which skips rows that are locked by other transactions. See MSDN for details.
• Sql Server sometimes escalates locks from row level to page or table level if it thinks that it's more economical. You can force keeping the locks at the row level with the hint ROWLOCK.
• Drop any unnecessary indexes because they slow down inserts, updates and deletes and also can cause locking problems on the index data.

On the C# code side:

• Use the best practice of 'acquire resources as late as possible and release them as early as possible'. Open your ADO.NET connection, run your sql command and close your connection before doing any time consuming operations on the results. You don't have to worry about connection pooling, it is done automatically as long as you use the same connection string all the time.

Answer 5

This will probably seem extreme, but have you considered temporarily disabling the index causing that clustered table scan at peak times? You could then rebuild the index at a quite part of the day?

Articles on disabling indexes

TechNet Guidelines for Disabling Indexes

Disable Indexes