I have a piece of code that performs inserts into highly denormalized tables. The tables have numbers of columns ranging from ~100 to 300+ This is Sql Server 2008 R2, running on Windows Server 2008

Each insert consists of inserting to a number of tables under the same transaction. Some inserts are batched by NHibernate, but some cannot be, but they are all under the same transaction nonetheless.

When I perform inserts for say 500 times by repeatedly calling a piece of code that performs the insert, I get an average of ~360 ms.

The weird bit is, when I run the test code simultaneously using 4 processes (the same exe run from 4 different command prompts under windows server 2008), the insertion performance per call gets much better. I see bursts that go as fast as 90 ms (almost X4 faster). I'm measuring the insertion time from the code.

Since the 4 processes know nothing about each other, I'm assuming that this has something to do with sql server, but I have absolutely no clue why. I'd like to know why this is happening and if there is any configuration that would allow me to get the same performance when the inserts are not that frequent.

Suggestions regarding sql server monitoring methods to understand what is going on at the db level are equally welcome.

I have a piece of code that performs inserts into highly denormalized tables. The tables have numbers of columns ranging from ~100 to 300+. This is SQL Server 2008 R2, running on Windows Server 2008.

Each insert consists of inserting to a number of tables under the same transaction. Some inserts are batched by NHibernate, but some cannot be, but they are all under the same transaction nonetheless.

When I perform inserts for say 500 times by repeatedly calling a piece of code that performs the insert, I get an average of ~360 ms.

The weird bit is, when I run the test code simultaneously using 4 processes (the same exe run from 4 different command prompts under windows server 2008), the insertion performance per call gets much better. I see bursts that go as fast as 90 ms (almost X4 faster). I'm measuring the insertion time from the code.

Since the 4 processes know nothing about each other, I'm assuming that this has something to do with SQL Server, but I have absolutely no clue why. I'd like to know why this is happening and if there is any configuration that would allow me to get the same performance when the inserts are not that frequent.

Suggestions regarding SQL Server monitoring methods to understand what is going on at the db level are equally welcome.