Strategies for organising SQL Server with large amount of RAM

Question

We now have a new server for our DB and amongst other things we have 128GB of RAM available (previously I had 16GB) . I know SQL Server is very good at managing it's resources, but I was wondering if there are any special settings or strategies that I should employ in either the server/db settings or processing code (stored procs/indexes etc) to ensure that SS takes best advantage of the available ram.

The DB is about 70GB and it's a non transactional db (it's a data warehouse). So basically large WRITE followed by massive READ is the normal flow of things.

Answer 1

Even though you have memory to spare at the moment (70GB database vs 128GB server) I would still configure the maximum server memory as per latest guidance.

...reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.

That equates to 110GB on a 128GB server. Reasoning being that the data warehouse will likely continue to grow and could eventually grab more than you want it too, so put the correct limit in now. It will get forgotten about otherwise.

When your data volume gets beyond the 110GB limit, pay closer attention to the servers free memory. If you consistently have additional GB to spare you may consider raising the limit by a few GB.

Other than that your only other fine tuning options are with trace flags, which should be treated with the caution they deserve. Test, test and test some more.

The three candidates I can think of that would make sense are:

• TF834 - Large page allocations
• TF2301 - Query Processor Modelling Extensions (typically good for OLAP, potentially problematic for OLTP)
• TF4199 - Query Optimiser Changes

As has been mentioned in an answer previously, Thomas Kejser has referred to TF834 as SQL Servers only "go faster" switch. TF2301 enables optimisations which are beneficial to DW type queries. TF4199 requires reading the linked document, so do.