What level of SQL-Server lock is suitable for insertion?

Question

I want to make almost 1000 inserts in a table for every second. And also every day I want to query all inserted rows just once. And I want to increase efficiency through multithreading and pooling. But I want to know which level of concurrency control is more suitable for me. The list of options for SQL-Server is located at MSDN .

Thank.

Answer 1

You should be fine with the default isolation level for inserts. Do you have a grouped index? If so, make sure it does not fragment when inserting new lines. Normally, guid would be a poor candidate for a clustered index. In addition, if you have an Enterprise edition, and you can identify the partitions in your table, you can split the table using this column (for example, region or city) and save the table partitions in different file groups. This way you can avoid IO competition. If you select allonce a day, and you want to maintain the insertion speed during the selection without locking too much, you might consider creating a database snapshot (Enterprise Edition again) and selecting it. If you can live with dirty readings, you can add a nolock to your choice.

Answer 2

You may bark the wrong tree. Look at using version line transaction isolation instead of providing locking hints for individual statements.

Many of the people I'm talking to have had good results with READ COMMITTED SNAPSHOT - which can be enabled at the database level and do not require code changes.

, SNAPSHOT , .

, , tempdb , tempdb.