question

Blackhawk-17 avatar image
Blackhawk-17 asked

Startup Options to Tune A Data Warehouse

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.

sql-server-2008data-warehouse64-bittrace-flagsstartup-options
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rahul avatar image
Rahul answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered

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).

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.