I want to make almost 1000 inserts in a table in each second. And also each day I want to query all inserted rows just once at altogether. And I want to improve efficiency by multi-threading and connection-pooling. But i want to know which level of concurrency control is more suitable for me. The list of options for SQL-Server are in MSDN Site.
Thank you.
You might be barking up the wrong tree. Have a look into using row-versioning transaction isolation instead of supplying lock hints for individual statements.
A lot of people I talk to have had good results through the use of READ COMMITTED SNAPSHOT - which can be enabled at the database level and requires no code change.
I can say that SNAPSHOT has served me well in the past, but it does require code change.
And a word of warning, be sure that your tempdb throughput is good, as row-versioning increases the load on tempdb significantly.
You should be OK with default isolation level for inserts. Do you have clustered index? If so, ensure that it doesn't fragment as you insert new rows. Typically guid would be a bad candidate for clustered index. Also if you have Enterprise edition and you are able to identify partitions in your table you might to partition the table using this column (for example region or city) and store partitions of the table on different filegroups. This way you might avoid IO contention. If you select all data once a day and you would like to maintain inserts speed during the select without too much locking, you might consider creating database snapshot (again Enterprise Edition) and select from it. If you can live with dirty reads you might add with(nolock) hint to your select.