Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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:

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.

share|improve this answer
    
Wow thanks. That's a good starting point for my reading. –  Preet Sangha Mar 13 '13 at 0:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.