Max Degrees of Parallelism (MAXDOP) tells the database engine, "If you decide to parallelize a query, go parallel with this many worker threads."
(It's a little more complex than that - there is also a coordinating thread, plus a single plan might have multiple parallel zones that each consume MAXDOP worker threads, but for the sake of this blog post, let's keep it simple.)
tl;dr: if you use Azure SQL DB Hyperscale's auto-scaling, it's probably not cutting your bill down because like SQL Server, Hyperscale doesn't automatically relinquish memory, and it'll be up to you to manually manage your own memory if you wanna cut your bill.
Now for the long story.
Or perhaps another way to phrase that is, Microsoft is up to something with parallelism. I don't know how long it's been this way - could even be since the launch of Azure SQL DB Serverless - but I just now noticed it while helping a client with a slow query. When you view an…
I got an interesting request for consulting, and I'm going to paraphrase it: We were using Azure SQL DB with automatic index tuning enabled for months. Things were going great, but... we just deployed a new version of our code. Our deployment tool made the database schema match our source control, which... dropped the indexes…
On August 30, Azure's Australia East data center had a big problem, affecting customers like Bank of Queensland and Jetstar. Here's the timeline: 30 August 2023 @ 08:41 – Voltage sag occurred on utility power line 30 August 2023 @ 08:43 – Five chillers failed to restart 30 August 2023 @ 10:30 – Storage and SQL…
Let's get you some answers.
Is Azure SQL DB PaaS, IaaS, or SaaS?
Azure SQL DB is platform-as-a-service (PaaS). Microsoft manages most common database tasks for you, and they act as your DBA.
This also means that like a DBA, Microsoft will tell you "no" if you try to implement something that they don't support. There's no negotiations: the answer is just no.
Who is Azure SQL DB for?
Azure SQL DB is a good fit for new applications.
Let's finish up Research Paper Week with something we're all going to need to read over the next year or two. I know, it says Azure SQL DB, but you boxed-product folks will be interested in this one too: Constant Time Recovery in Azure SQL DB by Panagiotis Antonopoulos, Peter Byrne, Wayne Chen, Cristian Diaconu, Raghavendra Thallam Kodandaramaih, Hanuma Kodavalla, Prashanth Purnananda, Adrian-Leonard Radu, Chaitanya Sreenivas Ravella, and Girish Mittur Venkataramanappa (2019).
Before I give you the link to the next research paper in this week's series, I wanna give you a few questions to think about: If you had to build something to automatically add indexes to SQL Server databases, where would you start? What data would you use to make your decisions? How would you…
If you've got a database that doesn't need to be on all the time, Azure SQL DB Serverless is in public preview now. It makes a lot of sense for those development databases you have to keep around, but don't really want to pay for.
If your database doesn't get any queries for 6 hours, it'll pause billing, and automatically resume again when your queries kick in. Here's how to set it up in the portal:
No no, April Fool's was yesterday.
Hear me out.
All it really means is, "Click Execute again, and you will be fine."
If I had a dollar for every time I saw this error message in SQL Server Management Studio:
As you pay more for Business Critical Azure SQL DB servers, they're supposed to get more storage throughput. The documentation on this is kinda hard to read, but boiling it down, for every core you add to a Gen5 server, you're supposed to get 2,500 IOPs. That should scale linearly: insert speed should go up evenly with each added core.
Azure SQL DB's Automatic Tuning will create and drop indexes based on your workloads. It's easy to enable - just go into your database in the Azure portal, Automatic Tuning, and then turn "on" for create and drop index:
Let's track what it does, and when. I set up Kendra Little's DDL trigger to log index changes, which produces a nice table showing who changed what indexes, when, and how:
A client asked, "How quickly could we spin up a full copy of our database in the new Azure SQL DB Hyperscale?" Their database size wasn't too far off from the 340GB Stack Overflow database, so I decided to migrate that to Hyperscale to see how the experience went. Hyperscale is Microsoft's intriguing competitor to Amazon Aurora. Hyperscale…
