asked
Tweaking SQL Server for OLTP or BI databases
Need help in understanding this. I totally understand the ida of keeping operational DBs with heavy user-specific I/O on a separate server from batch-populated DW/BI databases, and that these would need a server of their on. Are there particular tweaks (apart from parallelism level - high for DW, low for OLTP) to activate during respective server installations? SQL Server 2012 Enterprise edition. Thanks in advance.
It probably depends, but I would suspect a lot more ad hoc queries on a BI system, so Optimize for Ad Hoc would be more naturally turned on there (although I'd probably enable that on OLTP systems too). Where possible, I'd suggest making the BI databases read only. That changes some of how locking works and speeds things up. You're usually going to see more partitioning in a BI system because of the larger, usually historical, levels of data. Obviously the cost threshold for parallelism will be lower. That's about it off the top of my head.
15 People are following this question.
