Three questions hope you DBA masters of the universe can answer:
- I know it is best practice to set SQL Server's max memory. So if a server with 44 GB RAM has SQL Server installed we should set the SQL Server max memory to approx 37GB.
But what should we set if SQL Analysis Service and SQL Reporting Services are installed on the same server as SQL Server? Is it then better to set SQL Server's max memory to 22GB RAM so Analysis can take the other 22GB RAM?
- On a server running SQL Server with 44 GB RAM what is best practice for setting the page file size? I read on Brent Ozar (http://www.brentozar.com/archive/2014/06/sql-server-setup-checklist-free-ebook-download/) the following:
SQL Server doest not need a giant page file. if you're installing other applications on the server (which we don't recommend-- SQL Server should be isolated). you may need a larger page file. If SQL Server is the only major service running on the box, we typically create a 2GB size page file on the system drive.
Microsoft says we should set page file system managed to 1.5x the RAM which matches the advice given by Remus (see -> http://rusanu.com/2009/11/22/system-pagefile-size-on-machines-with-large-ram/).
So on our server with 44GB RAM we have SQL Server installed next to SQL Analysis Service and SQL Reporting Services. What size should we set the page file?
- For SharePoint the SQL Server that is part of SharePoint's farm and where SharePoint stores it's databases, the max degree of parallelism should be set on 1. Now that we have a server that is not part of the SharePoint farm (call it sql2) on this server we have SQL Server, SQL Analysis and Reporting installed. SharePoint does not store its databases on this server, but we do have reporting and analysis databases/cubes created that SharePoint reads and writes to.
Brent Ozar says:
Rule of thumb: set this to the number of physical cores in a single NUMA node (processor) sockets on your hardware or less
What should we configure on this SQL Server instance for max degree of parallelism when it has 4 VCPU's?
Hope you guys give not only me but more sql admins a bit more insight in the best practices than the jungle on the interwebs.