On-prem is where NAV has ran for ages, and where it will still primarily run for a while more. So I test several on-prem configurations with different processors, disks, and memory, to see how these configuration behave in scenarios I described in the original post.
I don’t have too many different hardware configurations to test, but I do have those
- An i7-3770 machine with two OCZ Vertex4 SSD disks and a Western Digital Caviar this or that, and 32 GB of RAM.
- An i7-6700K machine at 4GHz with two Samsung M2 950 SSD disks (at 2.5 GB/s read and 1.5 GB/s write speed), two Samsung EVO Pro 850 (at 500 GB/s read/write), and a Seagate something or other 8TB drive, and 64 GB of RAM.
All in all, I tested these configurations with:
- Data and log being on different SSD disks.
- Data and log being on the same SSD disk.
- Data and log being on the same magnetic HDD drive.
Let’s see the results.
First, let’s see how well these configurations perform in the typical business-logic scenarios. Here’s the chart:
What is obvious right away is that despite different hardware configurations, the performance is roughly the same.
As expected, the M2 disks with data/log separated perform the best, while the old WD Caviar with data and log on the same drive perform the worst, but still, the differences are not so dramatic as one would expect.
Also, the i7-6700K outperforms the older brother, but again – the difference is far from dramatic.
However, let’s not make any conclusions yet, and let’s first take a look at those test scenarios that expose raw SQL performance under conditions for which SQL was designed:
Here, the results are more as expected. Since both physical machines used ample amounts of memory, SQL Server quickly cached all necessary data, so if anything, these tests show that disk speed is less important than fast memory. Old WD HDD struggled with most of tests here, as did the old OCZ Vertex4 (honestly, I expected this guy to show more endurance), but all other configurations showed fairly consistent performance.
Let’s take a peek at the last group of tests, which expose SQL performance under cruel and unusual conditions that it was not designed to endure:
These results show that disk speed matters when it comes to those queries that are very inefficient to both read and cache; those situations when SQL neither can retrieve records efficiently, nor believes it can benefit from much caching.
However, when it comes to table scans, again it becomes obvious that disk layout does not really matter, and the only thing that matters is the amount of memory available.
These results are the least interesting of all. They show what you probably already know: when running NAV on-prem, beef up the memory, get good disks (all those vast MB/s don’t matter as much as memory does), and you are good to go. Once warmed up, SQL Server will cache most data, and most operations will be blazing fast.
However, while these results might not be too exciting, please keep them in mind, because in my next post I’ll show the performance of Azure VM, and then you’ll be able to get comparison between the on-prem and Azure VM performance.
See you in a few.