A Fine Slice Of SQL Server
Laurenz Albe digs into keyset pagination:
Keyset pagination is the most performant way to retrieve a large result set page by page. However, the neat trick with composite type comparison doesn’t always work. This article explains why and how you can work around that shortcoming.
All of the examples are for Postgres, though in my read-through of the post, I’d say it applies almost as well to SQL Server.
Comments closedAndrei Lepikhov compares two physical join operators:
Today’s post is sparked by a puzzling observation: users, especially those who use an abstraction layer like REST or ORM library to interact with databases, frequently disable the MergeJoin option across the entire database instance. They justify this action by citing numerous instances of performance degradation.
Considering how many interesting execution paths MergeJoin adds to the system elaborating IncrementalSort or sort orderings derived from underlying IndexScan, it looks strange: one more bug of skewed cost balance inside the PostgreSQL cost model?
This is an interesting peek into how complex the query optimizers in database engines are, as well as how small amounts of information (via statistics or indexes) can matter to a query.
Comments closedI showed the missing index suggestion (and let’s remember, they’re just suggestions) just for completion and to show I’m not hiding anything. Potentially, adding an index could speed up the query. However, that doesn’t affect what we’re going to talk about within this plan.
Logically, what’s happening here?
Click through for this overview, as well as a very helpful comment from Hugo Kornelis on what the Segment operator does.
Comments closedHugo Kornelis talks about a cursor I’d never heard of before:
Welcome to plansplaining, part 32, where we once more look at cursors. We already discussed the basics, and looked at static cursors and dynamic cursors. It is now time to cast our gaze upon the keyset cursor. The keyset cursor is sort of in between the static cursor (which presents a snapshot of the data is at was when the cursor was opened and disregards future changes) and the dynamic cursor (that always shows the current data). To be precise, a keyset cursor sees changes made to already existing rows, but does not see new rows that were added after the cursor was opened.
Read on to learn more about it.
Comments closedChristoph Schiessl sorts things out:
Custom
ENUMtypes in PostgreSQL are an excellent tool for enforcing certain database constraints, but you must be careful if you useSELECTqueries and want toORDER BYthese columns. Recently, I had to fix a bug whose root cause was a misunderstanding of this behavior. It’s just a contrived example, but imagine a table of people with their marital status, which is implemented as a customENUMtype.
Read on to learn more about the misunderstanding and some of the unexpected trickiness involved in getting a good query plan.
Comments closedMichael Bourgon solves an issue:
This morning I had a performance issue on a piece of code that worked long ago on a different server, and they were trying to put it in place today. It was SLOW. Like, 10 minutes slow. With the added bonus that it’s done through a web app, so it never finishes, it just always times out. After dealing with various approaches, I finally tried using the old Cardinality Estimator, and it went from 10 minutes to 3 seconds. But the query is inside the application, it doesn’t call a stored procedure. Which means the devs changing it is Non-Trivial. So I went to an updated version of an old trick – query store hints (which used to be Plan Guides)
Click through for a list of actions Michael took.
Comments closedHugo Kornelis continues a series on cursors:
We’re already at part 31 of the plansplaining series. And this is also the third part in my discussion of execution plans for cursors. After explaining the basics, and after diving into static cursors, it is now time to investigate dynamic cursors. As a quick reminder, recall that a static cursor presents data as it was when the cursor was opened (and does so by simply saving a snapshot of that data in tempdb), whereas a dynamic cursor is supposed to see all changes that are committed while the cursor is open. Let’s see how this change in semantics affects the execution plan.
Read on as Hugo gives it the college try and also admits he might be missing something in the explanation.
Comments closedPaul White puts up an article:
I showed a hidden option to make all operators report only their individual times in
More Consistent Execution Plan Timings in SQL Server 2022
. That feature isn’t complete yet, so the results aren’t perfect, and it’s not documented or supported.
I mention all that in case you are interested in the background. None of the foregoing explains what we see in this mixed mode plan. The row mode Gather Streams elapsed time ought to include its children. The batch mode Sort should just be reporting its own elapsed time.
With that understanding in mind, there’s no way the Sort could run for longer than the Gather Streams. What’s going on here?
Read on for a Paul White-level discussion of the topic, including a demo from Erik Darling.
Comments closedI’m going to be totally open and honest with you, dear reader: I’ve been experimenting with… AI.
See, I’m just a lonely independent consultant, and sometimes it’s just nice to have someone to talk to. It’s also kind of fun to take a query idea you have, and ask “someone” else to write it to see what they’d come up with.
ChatGPT (for reference, 4 and 4o) does a rather okay job sometimes. In fact, when I ask it to write a query, it usually comes up with a query that looks a lot like the ones that I have to fix when I’m working with clients.
Considering that the clients probably stole the query idea from Stack Overflow as well, that makes sense. But there was a clever trick that the query returned, so check it out.
Comments closedRod Edwards takes us through an issue:
CONVERT_IMPLICIT(nvarchar(200),[xxxxxxxxxxxxxxxxxxx].[Category,0)=[@Category] AND CONVERT_IMPLICIT(nvarchar(200),[xxxxxxxxxxxxxxxxxxx].[Id],0)=[@Id]
Oh dear, what’s all that then, we have a fat Residual Predicate, where SQL is performing an Implicit conversion on our query predicates.
Time to look at our datatypes.
This is one of several reasons why I espouse the philosophy of NVARCHAR Everywhere. You can’t have implicit conversion if you’re always using NVARCHAR over VARCHAR.
Comments closed