Accelerating SQL Server 2016 peformance with Persistent Memory in Windows Server 2016

Play Accelerating SQL Server 2016 peformance with Persistent Memory in Windows Server 2016
Sign in to queue

Description

In this episode of Data Exposed, Scott welcomes Lindsey Allen, SQL Server Partner Group PM, and Tobias Klima, Windows Storage PM, to the show to discuss how persistent memory in Windows Server 2016 increases the performance of SQL Server 2016. Building on the video with Bob Ward and Bob Dorr in which they discuss some of the performance improvements to SQL Server 2016, Lindsey and Tobias begin by sharing the storage technology evolution over the past decade and how persistent memory plays a pivotal role in that evolution regarding capacity, latency, and IOPS. Storage class memory using nonvolatile memory.

From there, at the 6:43 mark Lindsey shares how SQL Server takes advantage of these technology improvements to significantly increase the performance of SQL Server 2016. Lindsey spends several quality minutes showing us an example illustrating how persistent memory performance is much better through NTFS DirectAccess (DAX) volumes over standard HDD and SDD storage. Quick a significance!

At the 15:00 minute mark we jump into a demo to show the impact persistent memory has on CPU utilization and IO. With IO being the bottleneck, CPU utilization isn't where it should be in many environments, so Lindsey runs through a couple of examples to illustrate how persistent memory removes the IO bottleneck and takes advantage of CPU. Great demo!

Embed

Download

Download this episode

The Discussion

  • User profile image
    chap sarun

    use internet explore videos YouTube picture sky

  • User profile image
    tragic_hip

    how does this work with sql server running in a VM? Does Hyper-v of server 2016 allow us to provision a disk with this hardware in a way that sqlserver can recognize?

  • User profile image
    Ekrem Onsoy

    Dear Lindsey, the Delayed Durability feature is not there for quite a long time and especially since SQL Server 2008. It came with SQL Server 2014 and it's actually quite new when considering most of the clients in the wild has not upgraded their servers to this version yet.

  • User profile image
    Bruce Wellington

    Lindsey/Tobias, great video and demo (21 minute video posted August 11, 2016)

    I've a question about your demo on accelerating SQL Server 2016 with Persistent Memory). The first run was using a single NVMe; was that run using block storage mode or was it DAX? On the second run, the audio indicated it was using DAX on an NVMe; is that correct? I thought you might have used DAX with an NVDIMM-N as the primary discussion for the first 15 minutes of the August 11 video was on NVDIMM-Ns.

    Thanks,

    Bruce Wellington
    407-252-8393 cell

  • User profile image
    ToKlima

    Hi Bruce, the first run stored the log on a regular NVMe SSD. NVMe is a block protocol, so it was a regular block-based storage device. The second run also stored the log on an NVMe SSD, but the log buffers were stored on the NVDIMM-N device. The NVDIMM-N device was formatted with a NTFS DAX volume, meaning SQL Server could memory-map a file and get direct access to the NVDIMM-N address ranges, thus allow the use of memory instructions (load/store). Using DAX on an NVMe device does not work, because it would imply using a byte-addressable interface through a block interface, which doesn't work.

  • User profile image
    ToKlima

    @tragic_hip: If you would like to use persistent memory in a VM, the only option today is to create a VHD or VHDX file on the NVDIMM and then hand that file to the VM. It is not possible at this time in Hyper-V (WS 2016) to directly map an NVDIMM-N into the VM.

Add Your 2 Cents