We're building out a new DW. 64 Bit SQL Server 2008/Windows Server 2008, 48 Cores, 512 GB of RAM, yadda-yadda.
What startup options and trace flags (if any) should be used to tune performance?
We already intend on -E for large extent allocation and are thinking about trace flag 834 for Large Page Support but are wondering if there are others that are commononly used.
Answer by Rahul ·
Try T1118. With -T1118 turned ON, almost all new page allocations are performed from a GAM(Global Allocation Map) page that allocates eight (8) pages (1 extent) at a time to a object as opposed to a single page from an extent for the first eight (8) pages of an object, without the trace flag. This will reduce contention on Tempdb system database and improve performance.
Answer by sp_lock ·
You could try using ...
o T1117 so that all the files in a group will grow evenly (if using multiple).
o If you are using bulk loading you make wish to consider 1224 to help prevent lock escalations (** Make sure that memory is spec'd well).