Glenn Berry

Using Intel Optane Storage for SQL Server

SQL Sentry Essentials
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Register to Download

Featured Author

Itzik is a T-SQL trainer, a co-founder of SolidQ, and blogs about T-SQL fundamentals and query tuning.

Itzik’s Posts

Intel Optane SSD DC P4800X Basics

The Intel Optane SSD DC P4800X Series was introduced in Q1 of 2017, initially with a 375GB capacity and later with a 750GB capacity being released later in 2017. Intel has also recently released a 1.5TB version of this drive that is still hard to get in the retail channel. All of these drives use a PCIe NVMe 3.0 x4 interface, and they come in two different form factors, the first being a half-height half-length (HHHL) add-in card (AIC) that goes in a PCIe expansion slot, and the second being a U.2 15mm form factor for 2.5" drives that connect via PCIe 3.0.

Intel's 3D XPoint storage technology is fundamentally different from traditional NAND flash. The SSD DC P4800X's high performance at low queue depths makes it a much easier drive to get very high real-world performance out of, especially for SQL Server usage. Intel Optane SSDs have roughly 10X lower latency and 5-8X better throughput at low queue depths compared to the fastest PCIe NVMe NAND-based SSDs. They also have higher write endurance than "write-intensive" enterprise NAND flash storage, and their performance does not deteriorate as they are close to being full. They have consistent read response times under a very heavy write workload, and unlike traditional NAND flash drives, there is no loss of performance with smaller capacity drives compared to larger capacity drives from the same product line. For low-latency workloads, there is currently nothing that comes close to the Intel Optane SSD DC P4800X.

Intel Optane SSD DC P4800X Specifications

Intel rates the Optane SSD DC P4800X Series at 2500 MB/s for sequential reads, 2200 MB/s for sequential writes, and 550,000 IOPS for both reads and writes. Their rated latency is 10 µs (microseconds) for both reads and writes. For comparison's sake, Intel rates the current generation 3D-NAND, triple-level cell (TLC) SSD DC P4600 Series at 3200 MB/s for sequential reads, 1575 MB/s for sequential writes, and 610,000 IOPS for reads and 196,650 IOPS for writes. Their rated latency is 85 µs for reads and 15 µs for writes.

Current pricing is about $1200.00 for the 375GB size, and about $2500.00 for the 750GB size. The 1.5TB size is still difficult to get in the retail channel, but the pricing should be about the same per GB as the smaller capacity drives. In most cases, you will want to get two identical drives and then create a software RAID 1 array using Windows Storage Spaces in order to get redundancy without any loss of performance.

You should make sure to download and install the Intel Datacenter NVMe Microsoft Windows Drivers for Intel SSDs rather than using the generic Microsoft NVMe driver. You will also want to download and install the Intel SSD Firmware Update Tool to make sure you have the latest firmware installed on each Optane drive.

Primary SQL Server Usage Scenarios

These performance characteristics make these Intel Optane cards extremely well-suited for many intense tempdb workloads, particularly heavy-OLTP workloads and situations where you are using read-committed snapshot isolation (RCSI) on your user databases (which puts the resulting version store workload on tempdb).

I have had great results using these Intel Optane SSD DC P4800X drives for SQL Server instances with extremely demanding tempdb workloads. Several recent clients have moved their tempdb database files from some other type of storage to a logical drive that was backed by a couple of Intel Optane SSD DC P4800X PCIe NVMe storage cards (in a software RAID 1 array). A classic use case is moving tempdb from shared storage on a SAN with a traditional fail-over cluster instance to local storage on each node in the cluster, which you can do with SQL Server 2012 and newer. Another use case is simply moving your tempdb files from their current location to the new logical drive that maps to your RAID 1 Optane array on a stand-alone server instance, regardless of what version of SQL Server you are using.

These Optane drives are relatively inexpensive and offer the fastest currently available type of traditional block mode storage, bar none. They are transparent to SQL Server and will work in any system that supports PCIe 3.0 x4 slots as HHHL add-in cards or U.2 connected drives. They also will work with legacy versions of SQL Server (as long as your OS and hardware support it). It is pretty common to see high file-level write latency on tempdb data files from the sys.dm_io_virtual_file_stats DMV, so simply moving your tempdb data files to Optane storage is one way to directly address that issue, that might be quicker and much easier than conventional workload tuning.

If you are seeing allocation contention issues in tempdb (which usually has very little to do with the underlying performance of the drive(s) where tempdb is located), then you should follow the guidance from Microsoft's Pam Lahoud in her blog post TEMPDB – Files and Trace Flags and Updates, Oh My! Having extremely fast storage for tempdb will indirectly help with allocation contention issues in tempdb, but it is very important that you follow current Microsoft guidance regarding tempdb configuration, trace flags, and SQL Server patching to more directly address allocation contention issues in tempdb.

Another possible use for Intel Optane storage is as a home for your user database transaction log file(s), especially if you have multiple user databases that have their transaction log file located on the same logical drive or if you are see high WRITELOG waits on your instance. It is a possible alternative to using the SQL Server delayed durability property (which requires SQL Server 2014 and has a risk of data loss) or using the SQL Server persisted log buffer feature (which requires SQL Server 2016 SP1 and NV-DIMMs for hosting the 20MB "tail of the log" transaction log file).

I also have a couple of clients (with smaller databases) that simply moved ALL of their user database data and log files, along with their tempdb files to Optane storage with excellent results.

CrystalDiskMark Test Results

Intel makes a high-end consumer version of the Optane SSD DC P4800X, which is the Intel Optane 900P. They also have a slightly newer and faster Intel Optane 905P. I have a 480GB Intel Optane 900P PCIe card in my main AMD Ryzen Threadripper 2950X workstation, along with an older NAND-based 400GB Intel SSD 750 PCIe card. A quick CrystalDiskMark test gives the results shown in Figures 1 and 2. The most relevant test is the 4K transfer with a QD of 1 with 1 thread of execution, as shown in the bottom row.

Figure 1: 480GB Intel Optane 900P Results

Figure 2: 400GB Intel SSD 750 PCIe NVMe Results

If you are seeing high storage latency or random I/O bottlenecks with your workload, you may be able to get a very noticeable performance improvement simply by moving the relevant SQL Server files from where they are now to Intel Optane storage. This can be done for a relatively small amount of money, and it will work with any version of SQL Server. This may help you get some extra lifespan from an existing legacy server until you are able to upgrade.