Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
What recovery mode is your DW db set to? –  Eric Higgins 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? –  Aaron Bertrand Sep 4 '12 at 19:54
1  
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 Sep 4 '12 at 20:16
1  
@ivanmp Thanks for the clarification. Good luck. –  Mark Storey-Smith 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. –  Aaron Bertrand Sep 4 '12 at 21:18

1 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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