Hey all,
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.
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.
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).
You could also take a look at the configuration of Sql server FastTrack Datawarehouse at Microsoft. I do not have the link right now, but it is east to find.
