:thinking_face: When most people think about computed columns, they don’t think about cardinality estimates. Heck, I’m not sure most people think about cardinality estimates. At all. Ever. One of the few people who has ever responded to my emails does, and I didn’t even have to threaten him. Do you think about computed columns? If…
Scan The Man There seems to be a perpetual battle waged against the Index Scan. At some point it was declared that scans were inferior to seeks, and all energy should be dedicated to eradicating them. Much like asking why the whole plane isn’t made out of the stuff the black box is made out…
Sharing Is Caring When everything is going well, and queries are behaving responsibly, one need hardly think about memory grants. The problem becomes itself when queries start to over and under estimate their practical needs. Second Hand Emotion Queries ask for memory to do stuff. Memory is a shared resource. What kind of stuff? Well, usually…
Wordy Bird This sentence gets repeated a lot. You know the one. “Functions prevent the use of indexes.” Well, knowing you, it’s probably just your indexes. I’ve seen your indexes. Which Indexes? In the SUPERUSER database (yeah, I know, I’m cheating on Stack Overflow), all the tables have a PK/CX on an Id column, which is…
Monte Carlo Or Bust In Part 3, I showed you how two queries with TOP clauses can produce wildly different plans. To figure some stuff out about why the plans changed, let’s focus on the Nested Loops Join plan. Why? Because when I force the Hash Join plan, the results are no different. I get…
All The Rage We’ve asked how much one column and even one row can change a plan before. The doctor says I’m not allowed to read blog comments anymore on account of my blood pressure, but the AI I wrote in PowerShell assures me they were well-received. So here we go again, ONCE MORE INTO THE FRAY!…
Row Whats? Like it or not, the optimizer has row goals. We’re not going to go terribly in-depth here, because the point is just to make you aware that this is in executions plans, and I’ll be adding it to the FRK in an upcoming release. I am going to give you a round up…
Where have you been all my life? Whenever I’m teaching people about query tuning, two things come up almost without fail: parameter sniffing and tempdb spills. They’re not mutually exclusive — some queries will seemingly always spill to tempdb, whether parameter sniffing is present of not, and some queries experiencing issues with parameter sniffing will never spill…
The revolution will not be documented At the PASS 2017, Pedro Lopes (don’t call him low-pez) from Microsoft mentioned that a new parallelism wait was getting added soon. That wait, if you’re too darn tootin’ lazy to click, is called CXCONSUMER. According to Pedro’s slide, but not the ENTIRELY MISSING DOCUMENTATION, this wait is the…
Estimated Plans Need Not Apply This is, of course, only available in actual plans. All together now: If a query uses one or more user-defined scalar functions (such as T-SQL functions or CLR functions), a significant portion of query execution time may be spent inside those functions, depending on the function definition. This may be not immediately obvious…
As I’ve been building labs for my Mastering Query Tuning class, I’ve been spelunking through data.stackexchange.com. It’s where anybody can write queries against the Stack Overflow databases, and share them with friends. For example, Daniel Vandersluis wrote a query to see how many edits he has:
-- How many edits do I have?
-- Counts how many edits a user has, broken into Question and Answer edits.
-- Also shows how close you are to getting each editor badge.
DECLARE @UserId INT = 26837;
WITH qaedits AS (
SELECT COUNT(*) FROM Posts
WHERE PostTypeId = 1
AND LastEditorUserId = Users.Id
) AS QuestionEdits,
SELECT COUNT(*) FROM Posts
WHERE PostTypeId = 2
AND LastEditorUserId = Users.Id
) AS AnswerEdits
WHERE Id = @UserId
edits AS (
SELECT QuestionEdits, AnswerEdits, QuestionEdits + AnswerEdits AS TotalEdits
SELECT QuestionEdits, AnswerEdits, TotalEdits,
CASE WHEN TotalEdits >= 1 THEN 'Received' ELSE '0%' END AS EditorBadge,
CASE WHEN TotalEdits >= 100
ELSE Cast(TotalEdits AS varchar) + '%'
END AS StrunkAndWhiteBadge,
CASE WHEN TotalEdits >= 600
ELSE Cast(TotalEdits / 6 AS varchar) + '%'
END AS CopyEditorBadge
When I run that on my unindexed copy…
Odds and ends During the precon, we asked how many people were on 2016, how many people were aware of query store, how many people were using it, and how many people were using sp_BlitzQueryStore. About 1/3 of the hands went up at first, then for each successive question, fewer and fewer hands stayed up….
>implying Way back when, I posted about turning the Votes table in the Stack Overflow database into a Partitioned View. While working on related demos recently, I came across something kind of cool. It works for both partitioned tables and views, assuming you’ve done some things right. In this example, both versions of the table…
The Max for the Minimum Paul White (obviously of course as always) has a Great Post, Brent® about Aggregates on partitioned tables Well, I’m not that smart or good looking, so I’ll have to settle for a So-So Post about this. There are actually quite a few similarities between the way a partitioned table and…
Microsoft’s Joe Sack & Pedro Lopes held a forward-looking session for performance tuners at the PASS Summit and dropped some awesome bombshells. Pedro’s Big Deal: there’s a new CXPACKET wait in town: CXCONSUMER. In the past, when queries went parallel, we couldn’t differentiate harmless waits incurred by the consumer thread (coordinator, or teacher from my…
sp_BlitzWho is our open source replacement for sp_who and sp_who2. It has all kinds of really neat-o outputs like how many degrees of parallelism the query is using, how much memory it’s been granted, how long it’s been waiting for memory grants, and much more. If you’re on SQL Server 2016 SP1 or newer, it…
When you look at a query plan, SQL Server shows a tooltip with an Estimated Subtree Cost: A long time ago in a galaxy far, far away, it meant the number of seconds it would take to run on one guy’s Dell desktop. These days, it’s just a set of hard-coded cost estimates around CPU &…
It turns out I can be dumb In a previous blog post about Adaptive Joins, I thought that EXISTS wasn’t supported by the new feature. It turns out that EXISTS is totally 100% supported, as long as your indexes support EXISTS. To show this, I need to show you a query that gets an Adaptive…
File this under… Things probably only I care about. But the Showplan Schema for SQL Server 2017 popped up… well, I caught it last night. I probably could have written this sooner, huh? So what’s new? Well, if I compare the 2016 SP1 schema using KDiff, a few things stand out. Some I’ve blogged about,…
When you change MAXDOP and run RECONFIGURE, SQL Server clears the plan cache. (It’s documented, by the way, as is the rest of the behavior I’m about to show you, but sometimes I like to learn the hard way by running experiments.) Here’s the easiest way to see it – but make sure to only…