A Fine Slice Of SQL Server
Deborah Melkin takes another look:
When I originally posted about the estimated cost = 100, I didn’t expect there to be a follow up. But then Magnus Ahlkvist (LI), a fellow member of #TeamConstraints, had a great comment that was worth following up on:
Read on for Magnus’s question, some inspiration from Erik Darling, and a whole lot more.
Comments closedI’ve written a bit about SQL Server’s Automatic Plan Correction feature before– I have an hour long free course with demos on Automatic Plan Correction here on the site.
Today I’m updating that course with a note: after using Automatic Plan Correction in anger for a good amount of time, I do not recommend enabling the feature. I’ve had it cause too many performance problems, and there are not a ton of options for an administrator when it’s causing those problems.
Kendra is still bullish about the potential of this but has some major issues with the current implementation. Read on to learn more about it.
Comments closedKey lookups represent a choice made by the optimizer between:
- Clustered index scan
- Nonclustered index seek/scan + Clustered index lookup
Lookups can be quite useful to avoid creating quite wide nonclustered index, but the optimizer is heavily biased against them. It does not like random I/O.
Read on for Erik’s thoughts on the matter and how you may be able to improve the performance of a query by removing a key lookup—or sometimes, how you may safely ignore a key lookup because hey, it’s a 1 millisecond difference on a query that runs once a day.
Comments closedLukas Eder talks about implicit join elimination:
One of jOOQ’s key features so far has always been to render pretty much exactly the SQL that users expect, without any surprises – unless some emulation is required to make a query work, of course. This means that while join elimination is a powerful feature of many RDBMS, it isn’t part of jOOQ’s feature set, so far.
As Lukas mentions, many relational database products already do this–SQL Server is an example of one product that does. But not all of them do, so it’s nice to have that option available in the data access layer.
Comments closedAaron Bertrand tries out a new feature:
In a sentence: Instead of locking individual rows and pages for the life of the transaction, a single lock is held at the transaction level, and row and lock pages are taken and released as needed.
This is made possible by previous investments in Accelerated Database Recovery and its persistent version store. A modification can evaluate the predicate against the latest committed version, bypassing the need for a lock until it is ready to update (this is called lock after qualification, or LAQ). There’s a lot more to it than that, and I’m not going to dive deep today, but the result is simple: long-running transactions will lead to fewer lock escalations and will do a lot less standing in the way of the rest of your workload. Locks held for shorter periods of time will naturally help reduce blocking, update conflicts, and deadlocks. And with fewer locks being held at any given time, this will help improve concurrency and reduce overall lock memory.
Read on to learn more about how it works and Aaron’s initial thoughts on the feature.
Comments closedHugo Kornelis looks at a pair of useful window functions:
LAG and LEAD were introduced in SQL Server 2012. They require an OVER clause, but it can only specify PARTITION BY and ORDER BY. No ROWS / RANGE specification for a window frame. Which makes them the stand out as unusual in this series.
By default, they return a value from the last row before the current row, or from the first row after the current row, based on the specified sort order and while observing the specified partition boundaries. But there are two optional parameters, an offset to specify that you want, for instance, the third-last row or the second-next row. And the default parameter specifies a value to be used instead of NULL when the indicated row falls outside of the partition.
Click through to see what the plans look like, as well as how very welcome though potentially performance-impacting changes in SQL Server 2022 have affected this.
Comments closedErik Darling hits on a bugbear of mine:
A common-enough practice to limit search results is to write paginated queries. You may give users the ability to specify how many rows per page they want to see, or you may have a set number of rows per page.
But the end goal is to allow users to very quickly get a smaller number of rows returned to them. Almost no one needs to get many thousands of rows back, unless they’re planning on exporting the data.
Pagination is such a common activity that I wish there were a way to say, “Here is my data. Hang onto it in memory and quickly retrieve a subset of rows upon request” without doing all kinds of shenanigans on my end. Something like a data snapshot that remains in memory as long as the session is active, until the service restarts, until there is memory pressure, or until the caller manually evicts the data. That would make OFFSET and FETCH really useful instead of barely usable for most pagination scenarios because you wouldn’t need to re-run the entire query for every offset/fetch page.
There are ways to make pagination efficient, but the efficient ones aren’t easy or intuitive.
Comments closedErik Darling goes back to the well on a popular and important topic:
One of the most fun parts of my job is telling people that parameter sniffing is a good thing. They’re often shocked, because the phrase has such ominous connotations to developers.
The parameters! They get sniffed! Performance! Something! Stuff!
Parameter sniffing does not mean the sky is falling. Like I’ve said before, if it were such a bad idea, it would have been abandoned.
Read on for more thoughts on the topic.
Comments closedI probably overuse temp tables. I’m the first one to admit it. But often when I choose to use one, it’s a choice between:
- Spending a long time trying to business understand logic for a company I don’t work for
- Tinkering with query syntax and using really weird tricks and hints to get different plan shapes
- Waiting a long time to create or tweak indexes on huge tables (usually on “dev” hardware)
Erik provides a good example of where a temp table fits well, contrasting it to a common table expression that fares poorly. My bias tends to come in the opposite direction from Erik’s: I’m susceptible to playing query golf and getting everything I need back in one optimized call. But part of craftsmanship is knowing how and when to use each tool.
Comments closedBoris Novikov talks about an uncommon topic:
In this article we discuss one type of query transformation that most optimizers do not use. Because of this, it can be beneficial for you to rewrite a query to help the optimizer order operations in a way that can be beneficial.
An analytical query is supposed to produce some kind of summary generalizing properties of huge amounts of data but at the same time should be compact and easy for humans to understand. In terms of the SQL query language this means that any analytical query extracts and combines large number of rows and then uses aggregate functions with or even without
GROUP BYclause. More specifically, we consider queries that contain manyJOINoperations followed by aggregation. Usually, queries are written in this way and, surprisingly, the optimizers choose the best order of joins but leave the aggregation as the last step.
Read on for more information, including a minor lamentation that the various relational database optimizers tend not to perform this kind of operation. In SQL Server, I have an example of this pre-aggregation using the APPLY operator (with demo code here) and a simple but realistic example of how drastic the savings can be.
Comments closed