Welcome back to another edition of our SQL Server blog series. Today, we will be discussing an important topic that often arises for database administrators – memory usage in SQL Server. If you have experienced issues with running out of memory and temporary fixes like restarting the service only provide temporary relief, then this article is for you.

The Issue

Imagine having SQL Server database engine installed on a system that also hosts other services such as Analysis Services, Reporting Services, and Integration Services. You constantly find yourself running out of memory, and restarting the service seems to be the only solution. However, the problem keeps recurring.

The Solution

The key to resolving this issue is to always set the maximum memory options for your SQL Server-related services. By setting a hard maximum, you prevent the services from consuming excessive memory and causing unexpected performance issues in your environment. This becomes especially crucial when running multiple services on the same server.

Let’s take a look at the different SQL Server services and how they utilize memory by default:

Database Engine (SQL Server Service)

By default, the Max Server Memory (MB) setting is set to 2147483647. This means that SQL Server can essentially take up all the physical memory on the server for use by the SQL Server buffer pool. However, it’s important to note that this setting only applies to the buffer pool and not the entire SQL Server instance.

It is recommended to set this value to 80% of the physical memory on a server that only has the database engine running. If the server is sharing resources with other services, a smaller percentage should be used. Systems with larger amounts of memory can increase this percentage accordingly.

Analysis Services (SSAS)

Analysis Services has its own memory configuration settings. By default, the LowMemoryLimit is set to take 65% of the physical memory. This value controls the minimum memory that Analysis Services will free up before it starts using memory exclusively for itself. The TotalMemoryLimit and HardMemoryLimit settings are also important to configure, with the HardMemoryLimit being the threshold at which SSAS will start denying requests due to memory pressure.

Reporting Services (SSRS)

Configuring memory settings for Reporting Services is a bit more complex. It requires modifying an XML configuration file (rsreportserver.config). By default, this file is located at C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesReportServer. However, the path may vary depending on the SQL Server version and installation details.

While SSRS typically has benign memory usage, heavy utilization in environments with multiple services may require tweaking these settings. For best practices regarding Reporting Services configurations, we recommend referring to the whitepaper from the SQLCAT team on Scale-Out Deployments for Reporting Services Best Practices.

Integration Services (SSIS)

Unlike other services, the memory usage of Integration Services cannot be configured at the service level. Instead, optimization needs to occur at the package level. However, having the SSIS service installed alongside the database engine service is generally not problematic as long as all other services are configured optimally.

For more information on SSIS design and performance tuning, you can refer to the SQLCAT blog or watch a free webinar from Pragmatic Works on SSIS Performance Tuning.

Conclusion

While best practices suggest segregating services onto their own servers for optimal performance, it’s important to consider what makes sense for your specific environment. Remember, best practices are not always one size fits all. However, one best practice that applies universally is to avoid auto-shrinking your databases. Trust us, it’s for the kittens.

By following the recommendations outlined in this article, you can optimize memory usage in your SQL Server environment and avoid running into memory-related issues. Remember to always set the maximum memory options for your SQL Server-related services and configure them according to your specific requirements.