Share This Post

SQL Server memory and performance tuning for ConfigMgr

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:

How to reduce paging of buffer pool memory in the 64-bit version of SQL Server

http://support.microsoft.com/kb/918483

Tools for Performance Monitoring and Tuning

http://msdn.microsoft.com/en-us/library/ms179428.aspx

Tuning options for SQL Server 2005 and SQL Server 2008 when running in high performance workloads

http://support.microsoft.com/kb/920093

Performance Tuning Guidelines for Windows Server 2008 R2

http://msdn.microsoft.com/en-us/windows/hardware/gg463392.aspx

Share This Post

Specializing in System Center Products: Configuration Manager 2007, Operations Manager 2007, Desired Configuration Management, SMS 2003, software distribution, ITMU, SUS & WSUS. Database products and solutions: Microsoft SQL Server 2005 & 2008, Business Intelligence Development Studio, Analysis Server, Reporting Services, Integration Services. Microsoft Windows Server 2003 & 2008, SharePoint, PerformancePoint, Active Directory, DNS, Group Policy, Security, VBScript, ASP, Visual Basic, WMI, HTML and XML.

Leave a Reply