During Comprehensive Database Performance Health Check, I have recently a very interesting experience with an Azure. In this blog post, we will discuss which disks are preferred for performance – Standard Disks or Premium Disks. Well, honestly my answer here is very simple Premium Disks. If you stop reading this blog post over here, I am totally fine as well.
I have seen quite a lots of customers struggling with their SQL Server performance once they move to Azure Platform. The reasons for the slowness is often from not enough computing power or slower disk.
The common misconception about moving to the cloud is everybody is thinking that they are going to get the most powerful processor with fastest possible RAM and lightning fast disk. Once they reach the capacity of the disk or processing they can easily scale by getting more resources. Actually, the reality is that when you get lower tier of the server, the features you get are lesser but along with that your hardware and processing power is also not enough.
IOPS is one the most important metrics when one has to select the optimal disk for their business application. IOPS is a number of requests that your application is sending to the storage disks in one second. An input/output operation could be read or write, sequential or random.
Now let us compare the IOPS of standard disks and premium disks. Standard Disks IOPS for VM Tier is between 300 to 500 IOPS per disk. In the premium storage disk, once you cross P10 configurations, the IOPS/disk is over 500 and when you reach P50 the IOPS/disk is 7500. Please note that the price of P50 is extremely high.
In the simple words – If you truly want top-notch performance you must upgrade your system to the expensive premium tier.
Reference: Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
I agree with your conclusions ([always] use premium disks) but not with your rationale. Rarely have I seen IOPS truly be the most important IO measure for a database. It’s usually latency and bandwidth. IOPS is a cumulative number, meaning I can take a bunch of P10’s, stripe them using storage spaces, and I’ve got the IOPS I need. Piece of cake. Latency is not that easy. Azure (nor AWS nor GCP) publish latency numbers. A good DBA can calculate the latencies using the DMVs. Let’s just say that the latencies for “spinning rust” in the standard tier are abysmal. Even Premium tier latencies are not quite what you’d get with a traditional SSD on-prem. This is manifest in an application that once migrated to the cloud feels “sluggish” on certain screens. You do some profiling and determine that the app screen is making a thousand “chatty” db calls where the latency accumulates. The difference between 10ms and 60ms latency is inconsequential on a handful of db calls per screen. On 20 calls to load a page…it’s noticeable.
Final thought on latency…choose a VM with a BIG ephemeral D drive on SSD. These drives are NOT in a WASB premium tier storage stamp, they are SSDs local to the hypervisor. Immediately put your tempdb there as well as BPE and any other read-only data. You will need to “warm the cache” with a script since the drives are ephemeral.
Bandwidth is another issue. The bandwidth numbers for all public cloud vendors are published, but hard to find, difficult to measure, and there’s no alert when you’ve been throttled. Again, chatty applications are susceptible.
But after migrating tens of applications to the cloud I can tell you that anecdotally when a customer says the migrated app “feels sluggish” I immediately ask if it’s standard or premium tier. If it’s the former I ask the customer to switch to premium and call me back if that didn’t solve it. I’ve yet to get a call back, and it’s affecting my consulting business.
Whenever you virtualize something you pay an “overhead penalty”. Public cloud vendors don’t overcommit or balloon memory or CPU and the virtualization tax is minimal. Software-defined storage is another matter. All of the layers of abstraction and indirection begin to add up…but not at the expense of IOPS, rather the tax is on the latencies.
–dave
Thank you Dave, I agree with what you said. The best part is your comment really helps original article.
Thanks Dave. Agreed. i have encountered the same scenarios when i started migrating on-prem SQL into AZure VM’s. Premium will do best but it is expensive. We could also leverage Standard Disks with Simple Storage spaces Striping to get good IOPS.