0

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:

<MemorySafetyMargin>80</MemorySafetyMargin>
<MemoryThreshold>90</MemoryThreshold>

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.

Thank-you.

0

we are not using the mulitdimensional model, not tabular

Do you mean NOT multi-dimensional ONLY TABULAR?

If so:
It's not best practice to install SSAS and SQL Server together on one server as the SSAS (Tabular) VertiPaq Engine works totally different than SQL Engine and it's (VertiPaq) is more memory intensive unless the models configured as DirectQuery mode. You could may have different VM for each of them if the license covered physical server cores

If not so:
I don't think that is practical as you must select either option TABULAR/MULTI-DIMENSIONAL during SSAS Installation. if you mean, there is no use of either SSAS options, you could simply uninstall it from the server

Yes, it's recommended to configure maximum server memory setting when there are multiple instances on the same server (other applications on database server not recommended), depending on the instance workload you could set max server memory setting, leaving minimum ~4GB to OS. I have seen ~16GB memory (for SSRS dedicated server) sufficient to handle around 150 users requests

Consider following settings are same as maximum server memory in SQL Server

  1. In SSRS: WorkingSetMaximum
  2. In SSAS: TotalMemoryLimit

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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