Press "Enter" to skip to content

Category: Query Tuning

Parallel Performance and SSMS Outputs

Published 2025-11-17 by Kevin Feasel

Joe Obbish looks at some execution plans:

Getting back to the query, it doesn’t look that offensive to me. The row mode sort is a parallel top N sort and the overall number of rows to return is low, so each thread can independently sort its rows and return 1000 locally sorted rows to the parent operator. This is about as good as it gets with parallel row mode sorting. This is a row mode only query so the operator times that you see are the sum of that operator’s work and its children. In terms of real work done by the query, the scan clocks in at 1.168 seconds and the sort clocks in at 0.84 seconds. The final accounting at the end by the parent Parallelism (Gather Streams) is misleading at best and an outright LIE at worst. There wasn’t 4 seconds of work done by this query. There was only 2 seconds. 

Joe looks at two separate things in this post: first, a way of trying to optimize OFFSET/FETCH style paging queries; and second, how the gather streams parallel operator can report wrong information.

Leave a Comment

Reading an EXPLAIN Plan in PostgreSQL

Published 2025-11-14 by Kevin Feasel

Andrea Gnemmi reads the plan:

A typical task DBAs and Developers perform is optimizing query performance. The first step, after identifying troublesome queries using a tool like the pg_stat_statements view, is to look at the execution plan to determine what is happening and how to improve.

In PostgreSQL this can be done using EXPLAIN or using third-party tools which use the same process to gather query execution data.

Click through to see what an explain plan looks like in PostgreSQL and ways to visualize those plans.

Leave a Comment

Common ORM Tuning Tips

Published 2025-10-28 by Kevin Feasel

Amy Abel shares some advice:

Recently, I thought a database query in a plan was straightforward. It looked innocent until I noticed strange behavior. The deeper I dug, the more I realized many people might be running into the same issue with ORM queries.

Click through for a toy version of the scenario, as well as two common problems with ORM tuning: blind index acceptance (which, admittedly, is a problem with or without ORMs) and implicit conversion on filters.

Leave a Comment

PostGIS Tuning via pg_stat_statements

Published 2025-10-24 by Kevin Feasel

Paul Ramsey wants to make spatial querying faster:

A reasonable question to ask, if you are managing a system with variable performance is: “what queries on my system are running slowly?”

Fortunately, PostgreSQL includes an extension called “pg_stat_statements” that tracks query performance over time and maintains a list of high cost queries.

This particular post is more about pg_stat_statements in general rather than specific advice for PostGIS, but it does lay out some recommendations for using pg_stat_statements in spatial-heavy environments.

Comments closed

ROW_NUMBER() Filtering Performance

Published 2025-10-22 by Kevin Feasel

Erik Darling answers an office hours question in detail. The question comes down to why a filter on ROW_NUMBER() where the row number is equal to 1 could differ from the same query where row number is less than or equal to 1. Knowing that ROW_NUMBER() starts at 1 and can never be anything other than a natural number, you’d think that SQL Server would treat these exactly the same. But Erik shows an example where the two can differ, and the answer was a good one. I will admit that my pre-video guess was wrong but once he showed the execution plans, things clicked. And, like Erik mentions, this is why it’s so important to dig into the execution plan, because the answers are typically in there somewhere.

1 Comment

Performance of User-Defined Functions in Fabric Warehouses

Published 2025-10-03 by Kevin Feasel

Jared Westover shares some findings:

In Part One, we saw that simple scalar user-defined functions (UDFs) perform as well as inline code in a Fabric warehouse. But with a more complex UDF, does performance change? If it drops, is the code-reuse convenience worth the price?

I’m surprised that the performance profile was so good. I had assumed it would perform like T-SQL user-defined functions—namely, worse in general.

Comments closed

Skewed Data and Skewed Parallelism

Published 2025-09-23 by Kevin Feasel

Erik Darling has a new slide deck. This video focuses on how skewed data can potentially lead to parallelism not distributing the data on different CPU threads effectively. Erik demonstrates how you can identify this issue in practice, though solving the problem will come in a later video. It’s an interesting scenario and stick around for a poke at an argument Pedro Lopes has made.

Comments closed

Performing Data Validation with RegEx in SQL Server 2025

Published 2025-09-15 by Kevin Feasel

Reitse Eskens tries out regular expression support in SQL Server 2025:

One of the new features in SQL Server 2025 is that you can now use regular expressions directly in your T-SQL queries. Now, regular expressions (or RegEx) have never been a syntax that’s easy to read. There are a lot of brackets, dashes and other symbols that make no sense when you first see them. Before delving into how these can be used in SQL Server, a few basics are provided to get you started, along with a link to a website for further learning.

Read on for a quick primer and a bit of pain when it comes to performance.

Comments closed