I started a new job about 4 months back and was surprised to learn that there has NEVER been a SQL Server DBA here, just developers who don't really know what they're doing (like me). I am not a DBA by any means but I've convinced my employer to send me to a DBA course in a few months after noticing really poor performance of our SQL Server instance. Until then, I was hoping someone could help me configure the memory settings on our server. Our server has 56.0 GB of RAM and it is running SQL Server, Analysis Services, and Reporting Services.
Currently the server is sitting at 98% memory usage with SQL Server, SSAS and SSRS using roughly 50GB, 1.5 GB, and 0.5GB respectively.
SSRS is hardly used, only running about 15 or so legacy reports once a day, every day. SSAS is about 1/3 as busy as SQL Server and is definitely not doing work all day like the SQL server instance.
Additionally, the SQL Server has the following memory settings:
Minimum server memory: 0 Maximum server memory: 2147483647
the SSAS Server has:
Memory \ HardMemoryLimit:0 Memory \ LowMemoryLimit: 65 Memory \ TotalMemoryLimit: 80 Memory \ VertiPaqMemoryLimit: 60 (we are using the mulitdimensional model, not tabular)
and the reportserver.config file has:
I have adjusted the
Maximum server memory setting based on the following recommendation I got from somewhere:
To prevent Microsoft SQL Server from consuming too much memory, you can use the following formula to determine the recommended maximum server memory: (1) Reserve 4GB from the first 16GB of RAM and then 1GB from each additional 8GB of RAM for the operating system and other applications. (2) Configure the remaining memory as the maximum server memory allocated for the Microsoft SQL Server buffer pool.
but do i need to possibly lower this with SSAS and SSRS on the server too? Can you recommend values for SSAS and SSRS too? Oh, all instances are the 2012 version.