Home > Workload Solutions > SQL Server > Guides > Microsoft SQL 2019 on Intel Optane Persistent Memory (PMem) Using Dell PowerEdge Servers > App Direct Mode
For one use case, we configured PMem in App Direct (persistent storage) Mode, which keeps data and log files for our test database on the Optane volume. On the baseline server, we kept data and log files for the test database on NVMe volumes.
The following two figures show the storage layout of both the baseline and Optane servers:
To conduct our study, we used the industry-standard HammerDB benchmarking tool to stress-test both the baseline and App Direct server storage. We used the TPC-H Decision Support System (DSS) workload with a scale factor of 1000.
Multiple settings can be modified and configured at different layers of the deployment. For detailed steps to configure these parameters, see Setting up and configuring the solution deployment.
The following table lists the parameter settings:
Parameter settings | Options | |
PMEM | Performance Profile | BW Optimized | Latency Optimized | Balanced |
Namespace mode | FSDAX | DEVDAX | |
Namespace mapping | Mem | Dev | |
Namespace Sector size | 512 | 4096 | |
File System | File System | XFS | EXT4 |
File System Reflink | 0 | 1 | |
File System Allocation Unit | 4k | 2m | |
SQL Server | Memory | 128 GB | 256 GB | 512 GB |
Trace flags | 834| 1810 | 3979 | |
MAX DOP | Default| 12 | |
HBP ENABLED | ON | OFF | |
Memory Optimized tempdb metadata | ON | OFF | |
Dataset Indices | rowstore | columnstore | |
tempdb location | On NVMe | On DAX |
Our study shows an improvement in performance for both single stream and multistream executions of the TPC-H workload. The performance improves when we include rowstore and columnstore indexes in the comparison.