I’ve never been a fan of a GUI for DBA work. Give me a hand crafted script or a list of DMVs and I’m a happy camper.
Perfmon is probably my best friend. It’s always there and happy to tell me the truth. It seldom falters and has helped me get to root cause on even the toughest of performance issue. But what about PaaS?
What is PaaS?
PaaS is short for Platform as a Service, which really doesn’t sound like an epic game changer. I’d say it actually sounds benign and in some ways it is and others it’s not.
Think of driving car. You get in, set your drink in the cup holder, turn the key and start driving. Driving means watching the “speed limit” and looking out for other users of the road. Those aren’t the only responsibilities though. As a driver you “should” be doing a safety check EVERY time just prior to driving. How many of you actually do that?? I’m guessing 5%. Feel free to vote here: https://twitter.com/SQLSME/status/940351438143844353
Relating this back to the DBA: As a DBA, you have your day to day tasks which may include checking backups, indexing, ensuring configuration is correct and optimal, monitoring for blocking and performance issues and much more.
With me so far?
Now let’s think about self driving cars. What would you do as a driver? Read the news paper? Sleep? Play PUBG on the way to work? So many options right? If you’ve ever owned a Tesla or read about owning one, you’ll know that you still need to watch the road and take over when necessary; but, that’s today’s tech. Let’s say that the newest tech comes out tomorrow and you don’t have do do a thing. You could literally sleep if you felt the urge. So what’s there to do? Would the car know if there were nails or a bulge in the tire? Would the car know that the wiper blades were poor? The point is that you still have responsibilities.
Back to the DBA. If the database is hosted in PaaS what does that mean for the DBA? Is a DBA still needed? The short answer is yes. The longer answer is that the DBA has more free time to do other tasks. This is because the PaaS provider (Microsoft, Amazon, Google, …) configures the server and the database instance. As for the database, there are limited settings to tweak but out of the box they are probably appropriate.
Don’t get me wrong, you still may need to figure out the best configuration; just not in the same context as what you once thought. For instance, how many DTU’s does the database require? Do I need to move my database to a different SKU?
Monitoring Performance in Azure SQL PaaS
Since the operating system is walled off in a PaaS environment, tools like Perfmon and Resmon aren’t available. So how do we monitor? There are a few different DMV’s I’d like to mention in this post. and I’ll be covering these and others in this series.
- sys.dm_db_resource_stats – db level hardware stats (1 hour history)
- sys.resource_stats – host level hardware stats (14 day history)
- sys.dm_db_wait_stats – SQL wait statistics
Over the next few posts I’m going to detail these important DMVs and a few others that are specific to Azure SQL Database. Catch the next post later this week. We’ll cover the sys.dm_db_resource_stats DMV.