SQL Performance Tips for MEMCM – Part 1

Tips for optimizing SQL Server performance for MEMCM, we’ll start with the Windows Server platform. This is the first in a series of performance tips.

Windows Server

Since SQL Server is installed on Windows Server, and in many cases co-located with MEMCM, it is important to spend the time to configure Windows Server for optimal performance.

Recommended Windows Server/SQL platforms

Use the latest supported Windows Server version, and the latest supported SQL Server version. Keep both platforms up to date with latest security updates and bug fixes. For more information: Supported SQL Server versions – Configuration Manager | Microsoft Docs

Performance Power Setting – check control panel setting, in the past I’ve always recommended High Performance over Power Saver. Windows Server 2022 brings a “balanced” mode, which might be indicated for smaller site servers. For more information: Overview about power and performance tuning for the Windows Server | Microsoft Docs

RAM – make sure you have enough. SQL Server uses memory to cache data and will attempt to keep as much data as possible in memory. Accessing data from memory will always be faster than disk.  For configuration, setting minimum and maximum values, be aware of maximum support memory specifications by edition see the following information: Memory Management Architecture Guide – SQL Server | Microsoft Docs

Monitor page file utilization – objective is to minimize page swapping to disk, which will be inherently slower that accessing memory.

Disk layout

The physical (or virtual) layout of the disk is important. Some general recommendations:

• Install the OS on its own drive.
• All drives used for SQL Data or Log files should be formatted as 64KB NTFS.
• Create a dedicated drive for TempDB files
• Larger environments may need a dedicated drive for the Windows Server page file
• IOPS – have sufficient IOPS to support the activity. Generally speaking, SSD will provide the best performance.

For more information: SQL Server Design Considerations | Microsoft Docs

Recommended hardware

Good article which includes general starting points for Disk I/O performance, available memory and CPU recommendations.  Recommended hardware – Configuration Manager | Microsoft Docs

Next in the series will cover SQL Server configuration settings for performance.