A Fine Slice Of SQL Server
Erik Darling has some doozies today:
Many of our Tables have 15 plus guid s from other tables, but not declared as Foreign Key. Plus Foreigne Keys. When I add a Index on for every Foreign Key plus Indexes for declared foreign keys we end up with tables with 30 plus indexes for fks only. We don’t have write performance issues. would you index every fk for every possible join?
Click through for the answer to this and four other questions.
Leave a CommentErik Darling is answering questions again:
My company (using SQL Server 2019 Standard) has an algorithm that keys addresses into a varchar(40) has a cross-reference database that assigns an identity property to each new value, allowing us to post the numeric in our datasets. Production has to search their generated string keys in this database’s table to get the integer key in return. We have ensured proper string data typing on lookups and have unique compressed indexes on the string values. What would your next tuning step be if this was not getting the performance you needed?
There’s a good set of questions this time, so click through for Erik’s answers.
Leave a CommentAndrei Lepikhov speeds up a query:
Occasionally, you may come across queries featuring complex filters similar to the following:
SELECT * FROM table WHERE date > min_date AND date < now() - interval '1 day' AND value IN Subplan AND id = 42';And in practice, it happens that a simple rearrangement of the order of conditions in such an expression allows for speeding up (sometimes quite notably) the query execution time. Why?
Read on for the answer. In a perfect world, SQL is a 4th generation language and the order of operations should make zero difference for query performance. In practice, as Andrei shows, this is a challenge for the developers of the relational databases we use.
Leave a CommentErik Darling answers your questions…for some reason:
What are your top three books on performance tuning that you would enthusiastically recommend to someone about to embark on this journey?
Click through for this answer, as well as several others.
Leave a CommentKendra Little gets the smorgasbord:
Statistics in SQL Server are simple in theory: they help the optimizer estimate how many rows a query might return.
In practice? Things get weird fast. Especially when you start filtering on multiple columns, or wondering why the optimizer thinks millions of rows are coming back when you know it’s more like a few hundred thousand.
In this post, I’ll walk through examples using single-column, multi-column, and filtered statistics—and show where estimates go off the rails, when they get back on track, and why that doesn’t always mean you need to update everything with FULLSCAN.
Read on for a review of the three types of statistics. Admittedly, I’ve never had much luck with filtered statistics improving the performance of queries. If I were to speculate, I’d say that they’re good for a very specific type of problem that maybe I just don’t run into that often.
Comments closedDennes Torres takes a peek at some views:
You may have already discovered the 4 special views the lakehouse has in the queryinsights schema to track query performance. I made a video about the lakehouse special tables, but since then, they evolved a lot:
- queryinsights.exec_requests_history
- queryinsights.exec_sessions_history
- queryinsights.frequently_run_queries
- queryinsights.long_running_queries
Let’s discover what these tables have to offer for us to analyze the lakehouse performance.
Click through to see what each one of these holds.
Comments closedRyan Booz continues a series on pgNow:
In that first article, I shared how pgNow can be a lifesaver when you need immediate performance insights, highlighting features like query tuning and current activity monitoring. The tool’s ability to take periodic snapshots of query activity and spotlight active sessions has already been a significant help for early users.
Today, I wanted to look at another area of information that pgNow can help you explore during times of performance degradation or even as part of a regular database maintenance and hygiene: the Indexing tab.
Click through to see what’s in the feature and to get a free copy of the preview for pgNow.
Comments closedAndy Brownsword knows that sometimes, the only winning move is not to play:
Last time out we discussed blocking transformations, what they are, the impact of them, and touched on how to deal with them. In this post we’re going a step further to tackle one of them head on.
Here we’ll demonstrate the impact of blocking caused by the Sort transformation, and look at two options for solving this and slashing execution time.
Sorts aren’t the only blocking transformation that you should push back down to your source (if possible), but it is the most common example.
Comments closedErik Darling answers a set of user questions:
You have said that table variables, CTEs, Change Tracking, and Azure Managed Instances all suck. Do you have a full list of “features” to avoid?
Click through for a video of Erik answering questions around deadlocks, terrible things, UTF-8, and more. And I like the nuance behind Erik’s answer of this particular question. It’s easy to say “this thing is awful” and be done with it, but often times, the answer is more of “In this particular circumstance, don’t use this thing because of reasons X, Y, and Z; instead, use this thing.” That’s a rather different answer.
Comments closedMiles Cole groups things together:
If there anything that data engineers agree about, it’s that table compaction is important. Often one of the first big lessons that folks will learn early on is that not compacting tables can present serious performance issues: you’ve gotten your lakehouse pilot approved and it’s been running for a couple months in production and you find that both reads and writes are increasingly getting slower and slower while your data volumes have not increased drastically. Guess what, you almost surely have a “small file problem”.
What engineers won’t always sing the same tune on is how and when to perform table compaction.
Read on for a dive into the power of compaction (converting a large number of small files into a small number of large files) and plenty of tips along the way.
Comments closed