3

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.

12
  • 1
    What recovery mode is your DW db set to? Sep 4 '12 at 19:35
  • 1
    If you're still writing the data to disk, why do you expect more than 2X performance improvement? Sep 4 '12 at 19:54
  • 2
    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? Sep 4 '12 at 20:16
  • 2
    @ivanmp Thanks for the clarification. Good luck. Sep 4 '12 at 21:01
  • 2
    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. Sep 4 '12 at 21:18
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.

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.