In-memory SQL Server log file

Question

I have a SQL Server instance that is used solely for the purpose of reporting and also for preparing data to be loaded into Analysis Services. Its data comes from another SQL Server instance which is the production instance.

As this database can be rebuilt at any time from the production OLTP database, we've been thinking about putting its log file in memory (using something like RAMDisk).

I've done just that and, to my surprise, we haven't gained much performance; it's become only about 2x better.

Given that I'm no longer writing the log to disk, why is the performance gain this low? The CPU is not reaching anywhere near 100%. Where is the bottleneck now?

Note: We have also tested successfully with minimally logged operations using the trace flag 610, but for the purpose of this question please just ignore this.

• Eric Higgins: What recovery mode is your DW db set to?
• Aaron Bertrand: If you're still writing the data to disk, why do you expect more than 2X performance improvement?
• Mark Storey-Smith: I must be misreading this... if this is a reporting database that you don't write too, why would there be any performance gain from optimising the transaction log?
• Mark Storey-Smith: @ivanmp Thanks for the clarification. Good luck.
• Aaron Bertrand: And do you think the steep spikes in writes are free, and that eliminating log contention will make a massive data load happen on a regular I/O subsystem perform instantaneously? I'll echo what @MarkStorey-Smith said: good luck.

Answer 1

You haven't indicated the methodology you're loading your DW database with, but you may be seeing quite a bit of logging due to IDENTITY values per this blog.