Press "Enter" to skip to content

Category: Query Tuning

Combining DISTINCT and UNION

Published 2025-05-27 by Kevin Feasel

Louis Davidson gives it the college try:

When I was perusing my LinkedIn feed the other day, I came across this thread about using SELECT *. In one of the replies, Aaron Cutshall noted that: “Another real performance killer is SELECT DISTINCT especially when combined with UNION. I have a whole list of commonly used hidden performance killers!”

To which started my brain thinking… What does happen when you use these together? And when you use UNION on a set with non-distinct rows, what happens. So for the next few hours I started writing.

Read on for Louis’s findings.

Leave a Comment

Optional Parameter Plan Optimization in SQL Server 2025

Published 2025-05-23 by Kevin Feasel

Brent Ozar is down with OPP(O):

SQL Server 2025 improved PSPO to handle multiple predicates that might have parameter sensitivity, and that’s great! I love it when Microsoft ships a v1 feature, and then gradually iterates over to make it better. Adaptive Memory Grants were a similar investment that got improved over time, and today they’re fantastic.

SQL Server 2025 introduces another feature to mitigate parameter sniffing problems: Optional Parameter Plan Optimization (OPPO). It ain’t perfect today – in fact, it’s pretty doggone limited, like PSPO was when it first shipped, but I have hopes that SQL Server vNext will make it actually usable. Let’s discuss what we’ve got today first.

Okay, I really had to stretch the truth to make my lead-in work, but I’m too proud of it to change anything. Click through to see where OPPO is today. Even with just one optional parameter working well, there is still a class of stored procedures that this can help: the “get by one ID, or get me all of them” type.

Leave a Comment

Digging into Non-Idempotent Subqueries via CTE in PostgreSQL

Published 2025-05-05 by Kevin Feasel

Shayon Mukherjee continues pulling on a thread:

A few days ago, I wrote about a surprising planner behavior with CTEs, DELETE, and LIMIT in PostgreSQL, a piece I hastily put together on a bus ride. That post clearly only scratched the surface of a deeper issue that I’ve since spent way too many hours exploring. So here are some more formed thoughts and findings.

Click through for a deeper dive into the topic, including some key takeaways.

Comments closed

A Performance Comparison of Temp Tables and Table Variables

Published 2025-04-28 by Kevin Feasel

Mehdi Ghapanvari compares two ways of holding temporary data:

Do you ever wonder why your query is slow when you use a table variable in a join operation? If you are interested in why this happens and how to improve query performance in such a scenario, keep reading to learn more.

Click through for the full article. This focuses entirely on classic table variables and not memory-optimized table variables, the latter of which can significantly improve performance in specific circumstances.

Comments closed

Reviewing Two Explain Plans in PostgreSQL

Published 2025-04-17 by Kevin Feasel

Semab Tariq talks query tuning:

Performance optimization in a production database is crucial, but over-optimizing can complicate things without real improvements.

In this blog post, I’ll share two very basic EXPLAIN ANALYZE outputs from a production system. A user asked us to help optimize these plans (I’ve changed the table and column names for privacy).

We will look at how to spot slow parts of a query, improve performance the right way, and avoid unnecessary tuning.

One of the things you eventually learn as a performance tuner is that sometimes, it’s best not to try to optimize a particular query. This may seem a bit contradictory–who doesn’t want to go faster? But there are costs to actions, and spending a long time tuning an ad hoc query that somebody ran one time and probably won’t run again isn’t worth it. Ultimately, know how to tune, but also when to tune and what will give you the biggest marginal benefit.

Comments closed

Comparing INSERT INTO and SELECT * INTO

Published 2025-04-17 by Kevin Feasel

Haripriya Naidu runs an experiment:

Instead of looking at which option is faster, you may want to look at which option is better suited for a given context. Let’s take a look at 2 common options to insert data and analyze them.

INSERT INTO TARGETTABLE
SELECT * FROM SOURCETABLE

OR

SELECT * INTO TARGETTABLE 
FROM SOURCETABLE

Click through for a comparison of the two, not just for which is faster but also the pros and cons of each approach.

Comments closed

Finding Why a SQL Server Plan Failed to Go Parallel

Published 2025-03-25 by Kevin Feasel

Grant Fritchey looks for an answer:

Let’s face it, most of the time, you probably don’t want your SQL Server execution plans to go parallel. After all, that’s why, instead of adjusting the Cost Threshold for Parallelism (as you should have), you’ve set the Max Degree of Parallelism to 1 (I see you out there). However, some of you recognize that, in fact, some queries need to run in Parallel.

Yet, sometimes, a query you think should run in parallel doesn’t. Can you tell why a plan didn’t go parallel?

Read on to learn how.

Comments closed

RegEx Performance in Azure SQL DB

Published 2025-03-20 by Kevin Feasel

Brent Ozar breaks the bad news:

Regular expressions are a way of doing complex string searches. They can be really useful, but they have a reputation: they’re hard to write, hard to read, and they’re even harder to troubleshoot. Once you master ’em, though, they come in handy for very specific situations.

This post isn’t about their complexity, though. This post is about Azure SQL DB & SQL Server 2025’s regex performance.

Brent’s testing hurts, because I want to use regular expressions, and based on what he’s seen so far, we’re probably still better off using CLR-based regex in SQLSharp.

Comments closed