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.
Best Answer
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.