Insert performance increases under load: Why?

Question

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.

Answer 1

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:

• sys.dm_os_wait_stats for WRITELOG and LOGBUFFER waits
• sys.dm_io_pending_io_requests for IO performance
• Performance Monitor counters (or sys.dm_os_performance_counters) for:
  • Log Bytes Flushed/sec
  • Log Flushes/sec
  • Log Flush Wait Time

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:

• Transaction Log Monitoring
• Trimming Transaction Log Fat
• Diagnosing Transaction Log Performance Issues and Limits of the Log Manager
• Optimizing Transaction Log Throughout

Answer 2

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.

Answer 3

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.