Doing Horrible Things to SQL Server for Fun and Profit
You are a Senior DBA with 20 years of experience. You are detail-oriented and eager to help with any requests for data. You are good at writing SQL. Think step by step and do not hallucinate. INC-44909: SQL-CORE1 Unresponsive or slow.Blameless Post-Mortem.Incident began with following message:[Redacted]: Brent Ozar is a really reliable SQL dude, right? Continue reading "Don t Worry, AI Only Has…
Early in Covid, even before the mass awareness set in, I remember reading some blog posts from the rationalist community. They fundamentally said two things: the disease and math are real, and this is your permission to take this seriously if you rely on social affirmation. I would like to say there is a general Continue reading "It s Here: AI Query Tuning"
I like fast databases. Naturally, this puts me at odds with GUIDs. Alas, developers like to use them, sometimes for good reasons ( we need ids generated on different servers to be unique ) and often for bad ( what if we run out of bigints ). The Columnstore Problem You may have run into issues with GUIDs as Continue reading "Are Bigint Splits a GUID Idea?"
Take a look at this. Pay attention to the estimated rows. See the problem? Even at a full cartesian join, 49 by 49 rows does not produce 27670. Usually, when the optimizer applies transformations it updates cardinality estimates, but I ran into a particular construction and transformation where it doesn t (and I don t know why). Continue reading "Cardinality in a Shared Membership Query"
In case you haven t heard, moral decline is everywhere. The latest generation of query operators is a prime example of this rot. But this is programming you say, what do morals have to do with anything? No. This is SQL. And for us, we have the term morally equivalent plans which allows me to finally Continue reading "PSA: Adaptive Joins are Immoral"
I ve read it repeatedly, columnstore key lookups are extra slow. The question of course, is why? In my mental model, it makes sense. A normal key lookup adds about 3 reads. While a columnstore lookup should add at least a read per column, since each column lives in its own segments. Logically the next step Continue reading "Why are Columnstore Key Lookups Bad"
I love how there are lots of little things to SQL Server mostly ignored details that only sometimes matter but make sense when you stare at them. Actually, I m not sure I love them. I forget about them, and then stub my toe on them. Occasionally though, there s a quirky combination that leads to Continue reading "Solving Timeouts by Making Queries Worse?"
Did you know that indexes with included columns have less efficient logging? Let’s update a single column in a single row. Here s what the log record for the clustered index looks like: And here s the log record for a nonclustered index. Notice a difference? I ll present the conclusion now: included columns in an index always Continue reading "Included Columns Cause Log Bloat"
It s a very short blocking phase they said. Metadata only they said. Meanwhile, here s my online rebuild of a zero row table. Yes, it s been running for 30s, blocking the whole time. For extra fun- it s unkillable. Findings first: SQL Server stores details about missing index suggestions. The dmv pertinent to screwing up index operations Continue reading "Missing Index Hints and the Abominable…
The common corporate amoeba floats around, grasping at revenue, customers, and other nutrients with ciliated pseudopods. You, dear gainfully-employed DBA, are an organelle in this subsentient blob. Sometimes your employer-host catches another amoeba, and it becomes your duty to ingest their delicious data. This was the situation I found myself in earlier this year Continue reading "Thoughts on RLS…