patternsqlModerate
Insert performance increases under load: Why?
Viewed 0 times
whyinsertincreasesunderperformanceload
Problem
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.
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.
Solution
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:
To determine if transaction log throughput/flush size is a factor, monitor:
- sys.dm_os_wait_stats for
WRITELOGandLOGBUFFERwaits
- 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
Context
StackExchange Database Administrators Q#130028, answer score: 15
Revisions (0)
No revisions yet.