Configure persistent memory (PMEM) for SQL Server on Windows

Artikel
02/28/2023

This article describes how to configure the persistent memory (PMEM) for SQL Server 2016 (13.x) and above on Windows.

Overview

SQL Server 2019 (15.x) has several in-memory database features that rely on persistent memory. This document covers the steps required to configure persistent memory for SQL Server on Windows.

Note

The term enlightenment was introduced to convey the concept of working with a persistent memory aware file system. Direct access (DAX) extensions to the NTFS file system provide the ability to memory map files from kernel space to user space. When a file is memory mapped into user space the application can issue load/store instructions directly to the memory mapped file, bypassing the kernel I/O stack completely. This is considered an "enlightened" file access method. As of Windows Server 2022, this enlightenment functionality is available on both Windows and Linux platforms.

Configure the devices

Create namespaces for PMEM devices

In Windows, use the ipmctl utility to configure the PMEM disks (referred to as namespaces in Linux). You can find Intel® Optane™ specific instructions here. Details on supported PMEM hardware on different Windows versions are at Understand and deploy persistent memory. PMEM disks should be interleaved across PMEM NVDIMMs and can provide different types of user-space access to memory regions on the device. For more information on interleaved sets in Windows, see Understand and deploy persistent memory.

PMEM disks

Use PowerShell to examine PMEM disks

#Get information about all physical disks
Get-PhysicalDisk

#Review logical configuration of PMEM disks
Get-PmemDisk

#Get information about PMEM devices
Get-PmemPhysicalDevice

#Get information about unused PMEM regions
Get-PmemUnusedRegion

BTT and DAX

By default, New-PmemDisk will use the desired FSDax mode. Atomicity is set to the default of None rather than BlockTranslationTable. From a support perspective, BTT must be enabled for the transaction log, to mimic required sector mode semantics. Although use of BTT with NTFS is generally recommended, BTT is not recommended when using large pages, such as required for DAX.

Get-PmemUnusedRegion | New-PmemDisk -Atomicity None

Formatting the NTFS volume(s)


#Initialize PMEM Disk(s)
Get-PmemDisk | Initialize-Disk -PartitionStyle GPT

#Create New Partition(s) and Format the Volume(s) with DAX Mode
Get-PmemDisk[0] | `
New-Partition `
    -UseMaximumSize `
    -AssignDriveLetter `
    -Offset 2097152 `
    -Alignment 2097152 | `
Format-Volume `
    -FileSystem NTFS `
    -IsDAX:$True `
    -AllocationUnitSize 2097152

File alignment and offset

Check partition offset(s)

Get-Partition | Select-Object DiskNumber, DriveLetter, IsDAX, Offset, Size, PartitionNumber | fl

Check the file alignment of a particular file using fsutil. Our file size must be a modulo of 2 MB.

fsutil dax queryFileAlignment A:\AdventureWorks2022_A.mdf

Replacing PMEM

Reprovision PMEM disks

Whenever a PMEM module is replaced, it needs to be reprovisioned.

Note

Removing a PMEM disk will result in the loss of data on that disk.

# Remove all PMEM disks
Get-PmemDisk | Remove-PmemDisk -Confirm:$false

Erase PMEM modules

To permanently erase data from PMEM modules, use the Initialize-PmemPhysicalDevice PowerShell cmdlet.

# Reinitialize all PMEM disks
Get-PmemPhysicalDevice | Initialize-PmemPhysicalDevice -Confirm:$false

Dela via