|
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.
(comments are locked)
|
|
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.
(comments are locked)
|
|
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). I like the 1117 TF. Though this is only a last resort measure - right? We are hoping on pre-sizing our files but then... the real world happens.
Mar 18 '10 at 10:53 AM
Blackhawk-17
(comments are locked)
|
|
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.
(comments are locked)
|
