Different users in SQL Server can help in performance

We have one table having continuous inserts from 3 windows services in SQL Server 2008 with same SQL user. This makes table heavily loaded and retrieval operation and IO becomes slow.

We have decided to split this table in to two i.e. one for latest data and another for history data. I have one question here is, whether I gain performance benefit if I create one separate user for each windows service, so total 3 user in our case, for insert operation. I think there will be 3 session here i.e. separate session for each user and that mig开发者_JAVA百科ht improve performance.

Am I right?

3 sessions don't necessarily require 3 different user id's. Each of the three data loading processes could establish a session using the same credentials, and you'd still have 3 sessions.

However, you may now run into contention, where each process locks the other out, which may result in slower overall performance. This could be avoided by configuring row-level locking on the table space, which itself will usually cost a slight performance hit.

You might still get better performance by batching your inserts into groups of say 5, 10 or 25 records before committing the operation. The downside to this approach is that on exception when you have to rollback and re-do, it takes longer because the unit of work is bigger.

I don't think there will be any performance increase by using different users. There will still be three different database sessions.

Depending on your setup and database, I think you may alliviate IO load with one or several of the following tips:

• Batch inserts. Build a single service that can batch inserts. Less insert operations is way better (IO wise) than many insert operations.
• Depending on your scenario, you may gain performance by lowering transaction isolation level for reads and inserts to that table.
• Minimize the number of indexes on that table. Inserts to tables with indexes are more expensive.
• Make sure the tables are stored on a disk that is fast enough for the IO throughput you need. Make sure the disk is not being used by other services.

I hope that helps.