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.


One possible reason is that four concurrent processes generate a more favourable pattern of log flushes - typically meaning that each log flush writes more data than is the case with a single executing process.

To determine if transaction log throughput/flush size is a factor, monitor:

Look for internal limits being reached. In SQL Server 2008 R2, there can be a maximum of 32 outstanding (asynchronous) log flush I/Os per database on 64-bit versions (only 8 on 32-bit). There is also a total size limit on the outstanding IOs of 3840KB.

More information and further reading:

| improve this answer | |

Everything @PaulWhite says, plus...

If you have foreign keys in place, every insert will require a check to be done on each table referenced. It sounds to me like you are, as you're only getting 360ms, which feels slow to me.

Anyway, checking those tables is massively helped by having that data in RAM already, rather than having to load it into disk.

It sounds to me like loading the data into RAM is a significant part of your execution, and that it only needs to happen once.

It could also be effective plan caching, and that your queries need to be compiled the first time, with subsequent calls being able to avoid that phase.

| improve this answer | |
  • Thanks Rob. My performance problem is associated to the high number of tables used during an insert. There are no foreign keys, I removed them for performance reasons and my model and domain requirements allow me to do that. I'm not loading data to RAM, and my inserts are shaped dynamically by incoming requests, which are changing all the time. I'm basically misusing a star/snowflake(ish) schema for OLTP and trying to get away with the best performance I can. – mahonya Feb 22 '16 at 11:40
  • 2
    @mahonya, even though you are not explicitly loading data into RAM, SQL Server must first read the needed index and data pages into buffer cache before performing the insert operation. Concurrent insert threads may have the effect of warming the cache such that one thread incurs the read overhead and the other access the data in cache. – Dan Guzman Feb 22 '16 at 12:27
  • Thanks @DanGuzman - and yes, mahonya , there is a strong chance your cache is being nicely warmed. I'd be checking your waits to see if it's physical I/O causing your bottleneck. – Rob Farley Feb 22 '16 at 13:11
  • Thanks @DanGuzman Agreed, the db index cache speedup is something that I'm used to seeing in postgres I probably misunderstood Rob's input. – mahonya Feb 22 '16 at 14:31

some servers / cpus / os's remember the patterns. like cache.

Since you doing the same thing 4 times, I'm sure there are ways it can cut corners, What I am guessing is that the first way you do it, it thinks of it as one long process (example1) but in the second way is sees the reused code and runs it like cache (example2) or it could be the first process is to big to fit it all in the (ram example3).

example1: 0111110000110111110000111011111000011110111110000

example2: 0111110000|11|0111110000|111|0111110000|1111|0111110000

example3: 0111110000011111000001111100000111110000 example3: loop: 0111110000

I know ubuntu server does this with repeated mysql queries. I can save them in cache, although really the only difference in time is 10-40mms but it adds up. When I was in school there was classes that showed you have to make programs (perl / php) use that cache to be faster.

But, it might depend on the program, what language is it, what is it compiled in or how it was programmed.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.