A few years ago I wrote a blog on tuning SQL Server memory to work with ConfigMgr and SQL Server.
Since it was written specifically for SQL Server 2005 and Server 2005, it is time to update this article with current products and information.
Most commonly, SQL Server installations are now being run on 64-bit versions of Windows Server. That means that AWE memory management is a thing of the past.
With that in mind, the earlier blog is still relevant, if the max memory usage of SQL Server is not capped, SQL tends to capture all available memory. If other processes; such as the Operating System or ConfigMgr need additional memory, SQL should yield memory for those applications. In reality, I’ve found that SQL does not yield memory fast enough to prevent noticeable performance degradation of ConfigMgr.
A few tips:
- For a quick test of available server memory, open task manager, click the performance tab, Resource Monitor and check for available physical memory. If the server is using the page file, everything slows down!
- Learn the use of the tools (listed below) to troubleshoot and monitor performance of SQL Server.
- Best practice dictates that you download and apply the latest supported Service Pack and/or Cumulative Updates for SQL Server, after testing in a lab (of course :).
Finally, remember that you are running an application (SQL) on Windows Server, investigate potential performance issues that may be occurring on Windows Server as well.
A few articles that are worth reading: