Press "Enter" to skip to content

Category: Query Tuning

SQL Server 2019 and Row Mode Memory Grant Feedback

Published 2025-01-23 by Kevin Feasel

Yvonne Vanslageren explains a solid feature in SQL Server 2019:

When SQL Server runs a query, it needs memory for operations like sorting and joining data. It also relies on memory during query compilation to hold intermediate plans while the Query Optimizer finds the best execution strategy. In parallel processing scenarios, the memory requirement grows even further.

SQL Server manages this by pre-allocating memory for each query through the SQL Server Operating System (SQLOS). This process ensures that no single query can monopolize the server’s memory.

Read on to learn more about memory grants, problems you can run into with memory grants, and one way SQL Server 2019 has improved to reduce the risk of bad memory grant estimates.

Leave a Comment

Working with DBCC OPTIMIZER_WHATIF

Published 2025-01-15 by Kevin Feasel

Yvonne Vanslageren shows off a very uncommon DBCC command:

DBCC OPTIMIZER_WHATIF is a powerful diagnostic command in SQL Server that allows database administrators and developers to simulate various hardware configurations. By doing so, it shows how the query optimizer would behave under different CPU and memory allocations—without requiring you to physically change server hardware. This makes it an invaluable tool for performance tuning, troubleshooting, and strategic planning in SQL Server environments.

This obviously won’t actually make things faster, but will help you answer questions like “would more CPU cores improve this query or do I need to dump more RAM into the server?”

Leave a Comment

PostgreSQL and Indexing on EXTRACT()

Published 2024-12-30 by Kevin Feasel

Henrietta Dombrovskaya troubleshoots a performance problem:

It’s Christmas time and relatively quiet in my day job, so let’s make it story time again! One more tale from the trenches: how wrong you can go with one table and one index?

Several weeks ago, a user asked me why one of the queries had an “inconsistent performance.” According to the user, “Sometimes it takes three minutes, sometimes thirty, or just never finishes.” After taking a look at the query, I could tell that the actual problem was not the 30+ minutes, but 3 minutes – when you have a several hundred million row table and your select yields just over a thousand rows, it’s a classical “short query,” so you should be able to get results in milliseconds.

Read on for the problem, as well as how Henrietta was able to coerce the PostgreSQL optimizer into choosing the correct path.

Comments closed

Fractional Path Performance Issues in Postgres Partitioned Tables

Published 2024-12-16 by Kevin Feasel

Andrei Lepikhov digs into an interesting finding:

While the user notices the positive aspects of technology, a developer, usually encountering limitations, shortcomings or bugs, watches the product from a completely different perspective. The same stuff happened at this time: after the publication of the comparative testing results, where Join-Order-Benchmark queries were passed on a database with and without partitions, I couldn’t push away the feeling that I had missed something. In my mind, Postgres should build a worse plan with partitions than without them. And this should not be just a bug but a technological limitation. After a second thought, I found a weak spot – queries with limits.

Read on to see what Andrei came up with.

Comments closed

Number of Rows Read in SQL Server Execution Plans

Published 2024-12-11 by Kevin Feasel

Rob Farley cashed in all of his chips and got something nice out of it:

I had written to Santa (okay, the product group at Microsoft) to ask for this. It wasn’t quite “If I’m on the nice list, what I’d like is a unicorn train set”, but more like “If it’s not too much trouble, this thing here would be amazing…”

The thing was the ability to see the “number of rows read” by an Index Seek operation in a SQL plan containing the “Actuals” (post-execution).

Read on for information about why this is so useful.

Comments closed

SQL Server Execution Plan Analysis Features

Published 2024-12-11 by Kevin Feasel

Hugo Kornelis looks back at some nice additions to SQL Server:

It’s December. The last month of the year, and hence also the last T-SQL Tuesday of the year. Edition 181 is hosted by Kevin Chant. His chosen topic for this episode is to have us talk about which SQL Server announcement excited us the most.

This posed a bit of a problem for me. The only truthful answer here is that I have never been as excited about an announced new feature as when the Query Store was announced. But I also realized that I don’t have much to write about the Query Store, except how awesome it is. Okay, I could go on a full explanation, but many others do that much better, and have already done so. So that doesn’t leave much for me.

And that’s why I’ll also include my second favorite announcement.

Read on for a little bit around Query Store, followed up with coverage of several interesting additions to SQL Server over the years.

Comments closed

Alternatives to Long IN() Lists in SQL Server

Published 2024-12-05 by Kevin Feasel

David Fowler still has a list:

This post comes off the back of my last, where I looked at issues caused by explicitly declaring a large number of values in an IN clause. The query processor ran out of internal resources and could not produce a query plan – When You’ve Managed to Confuse SQL With a Crazy IN Clause.

My suggestion was to put the values into a temp table and carry out a semi-join.

That got me thinking, which of the two methods would perform better.

I’m going to look at the two methods for different amounts of values and have a look at how they get on in terms of reads and total time.

Read on for the answer. If I’m surprised by anything in it, it’s that the threshold where temp tables out-perform the IN() clause is so high.

Comments closed

The Difficulty of Deletion in PostgreSQL

Published 2024-11-25 by Kevin Feasel

Radim Marek takes us through the process of deleting rows:

Your database is ticking along nicely – until a simple DELETE brings it to its knees. What went wrong? While we tend to focus on optimizing SELECT and INSERT operations, we often overlook the hidden complexities of DELETE. Yet, removing unnecessary data is just as critical. Outdated or irrelevant data can bloat your database, degrade performance, and make maintenance a nightmare. Worse, retaining some types of data without valid justification might even lead to compliance issues.

Read on to learn about the process, some challenges, and a common pattern for resolving these challenges. The solution is pretty similar in SQL Server as well: batching delete operations, ideally with a supporting index.

Comments closed

Filtered Indexes in PostgreSQL

Published 2024-10-29 by Kevin Feasel

Hubert Lubaczewski digs into a performance issue:

So, we have some databases on PostgreSQL 14 (yes, I know, we should upgrade, but it is LONG project to upgrade, so it’s not really on the table now).

Last week someone noticed problems with website. These were tracked to queries getting killed because of statement_timeout of 3 minutes.

The query was relatively simple (most of the identifiers were obfuscated, but tried to keep them somewhat memorable):

Click through for the story, analysis of the problem, and how creating a filtered index worked in this case. Filtered indexes are a beautiful thing when the optimizer knows how to make use of them.

Comments closed