Three Reasons RDS SQL Server Is Better Than Azure SQL Managed Instance

While every managed database service has high points and low points, there are three things that make RDS for SQL Server shine in comparison to Azure SQL Managed Instance: options for a higher memory:vCPU ratio, a well documented API that works beautifully with python, and fast and effective customer support that isn’t painful to use.

It’s just three things, but they make a huge difference. And these are three things that Microsoft can, and SHOULD, really fix for their cloud database services.

Continue reading

All Eyes on the Wrong Problem: How Mitigations Distract from Real Performance Pain

The biggest lesson I’ve learned from helping folks manage data in Azure is this: if you’ve got a truly terrible problem you’d rather people didn’t notice, a great way to hide it is by educating your support staff and users about something bad but not AS terrible— something with a small mitigation—and constantly refocusing them on that.

The user base— and even your own support staff— will think that anyone who talks about the bigger issue just doesn’t understand how to fix the “known” problem.

This is the story of Azure General Purpose storage for Azure SQL Managed Instance and Azure SQL Database.

Continue reading

What the Decline of SQL Server Quality Means for Developers and DBAs

‘Is it just me, or is SQL Server quality slipping?’

I asked myself that question for couple/few years until I faced up to it: SQL Server is well into a period where Microsoft investment is waning, and Microsoft regularly isn’t able to deliver the features they promise.

Continue reading

Buyer Beware: Azure SQL Managed Instance Storage Is Regularly as Slow as 60 Seconds

What are your stories of unbelievably bad performance from cloud vendors? I’ll go first. For years, Azure SQL Managed Instance’s General Purpose Tier has documented ‘approximate’ storage latency as being “5-10 ms.” This week they added a footnote: “This is an average range. Although the vast majority of IO request durations will fall under the top of the range, outliers which exceed the range are possible.”

Continue reading

Query Hash Values Are Meaningless in SQL Server: They May Be Reset to Be the Same Value as the Query Plan Hash

This is the worst bug I’ve found in SQL Server to date. Previously, my top find was SQL Server Online Index Rebuild sometimes happens offline without warning. This one has taken top slot because it makes my life more difficult on a daily basis.

Background: SQL Server generates a query_hash for each query. This is stored in sys.query_store_query and it’s one of the primary ways you can identify what a query is across different Query Stores, or even the same Query Store over time, as surrogate query_id values get reset if Query Store is cleared or data ages on. The query_hash is a ‘Zobrist hash over the shape of the individual query, based on the bound (input) logical query tree. Query hints aren’t included as part of the hash.’ (Source)

Continue reading

How to Survive Opening a Microsoft Support Ticket for SQL Server or Azure SQL

Asking Microsoft for support for SQL Server or Azure SQL is a lousy experience these days, whether you’re using a cheaper service tier or the more expensive support tier formerly known as “Premiere Support.” You need to know a lot about the root cause of your problem and how to solve it, or your request will be dismissed with misinformation. You’ll need data and metrics to back up your claims to get the ticket escalated, and you’ll need to provide those receipts multiple times. Once escalated to the Product Group, you may get a helpful response, but it takes a while. If the answer is relayed through a lower support tier, it often won’t make much sense.

These issues aren’t due to bad work ethics or personal failings of support workers. These are good humans trying their best. The problem is worse because it’s systemic.

Continue reading

Script to Automate Unforcing Failed Forced Plans in Query Store (SQL Server)

tldr; I’ve published a script to loop through all databases on an instance, identify if there are any query plans in a problematic ‘failed" forced state (which can hurt query performance), and un-force them if found. Get the dbo.dba_QueryStoreUnforceFailed stored procedure on GitHub.

This script is designed to work on SQL Server on-prem, in a VM, or in Azure SQL Managed Instance or SQL Server RDS. Since the script is instance-level and loops through all databases, this isn’t really designed for Azure SQL Database – and you don’t get a SQL Agent there anyway, so you probably want to change this around for that use case. The script is shared under the MIT license, feel free to contribute code and/or adapt away for your own uses.

Continue reading