100 Things I Hate About Views: Undeclared Data Types in Columns
Views let you do dumb things by accident in SQL Server. Then they make you have to think way too hard to fix them.
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.
AI will eliminate DBA Jobs faster than you think
I listened to “Surviving the A.I. Endgame” this weekend and realized: I’ve become one of the believers that AI advances are very likely to completely change tech and knowledge roles as we know them over the next 10 years. This is going to dramatically shrink the workforce across MANY roles (and many of those impacted will be outside of the tech sector). It isn’t that people won’t be needed anymore, but far fewer people will be needed.
How to Stop SSDT / Database Projects / SQLPackage from Modifying Database Options
SQL Server’s free state-based version control tooling was introduced under the “Data Dude” brand, then became known as “SQL Server Data Tools” (SSDT). Its extension for the (now dying) Azure Data Studio IDE is called “SQL Database Projects.” If you need to find documentation, you often need to know to search for specific component names like SQLPackage.exe, which is a command line utility used to deploy SSDT Projects AKA SQL Database Projects.
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.” How approximate is that 5-10 milliseconds, you might wonder?
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.
What Is the CPU Wait in Datadog SQL Server Monitoring? How does it Compare to Waiting on CPU?
I use Datadog on a regular basis, and I’m a pretty huge fan. The monitoring pack for SQL Server (and its PAAS variants) is still pretty rudimentary, but it evolves regularly. That’s NOT what I’m a fan of, though. What makes me a raving fan is the flexibility of Datadog’s notebooks and dashboards, combined with the ability to create all sorts of custom metrics and monitors. There are always things in SQL Server monitoring packs that I have strong opinions about.
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. This is true whether you are using a cheaper service tier or the more expensive support tier formerly known as “Premiere Support.” Either way, I’ve found the same issues: as the person requesting support, I must know a whole lot about the root cause of my problem and how to solve it, or my request will be dismissed with misinformation. I need to have data and metrics that back up my claims in order to get the ticket escalated to someone who can help, and I will need to provide those receipts three or four times. Once something is escalated to the Product Group, I may get a helpful response, but it will generally take a while. If I’m not engaged directly with the Product Group and the answer is being relayed through a lower support tier, it often won’t make much sense.
These issues don’t happen due to bad work ethics or personal failings of support workers. These are good humans, who are trying their best! The problem is worse, because it’s systemic.
SQL Server page compression: should you worry about CPU usage increasing on on inserts, updates, and deletes?
Every time I share a recommendation to use data compression in SQL Server to reduce physical IO and keep frequently accessed data pages in memory, I hear the same concern from multiple people: won’t this increase CPU usage for inserts, updates, and deletes? DBAs have been trained to ask this question by many trainings and a lot of online content – I used to mention this as a tradeoff to think about, myself– but I’ve found this is simply the wrong question to ask.
Free 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.