A Fine Slice Of SQL Server
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 CommentPaul 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.
Leave a CommentErik 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.
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 closedErik 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 closedReitse 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 closedErik Darling continues a series on transaction isolation levels. If you haven’t been watching this series, I’d recommend checking out his channel and catching up, as he’s spent a good amount of time on transaction isolation levels and what, exactly, that means in a relational database management system like SQL Server.
Comments closedForrest McDaniel joins tables together:
Even at a full cartesian join, 49 by 49 rows does not produce 27670.
Usually, when the optimizer applies transformations it updates cardinality estimates, but I ran into a particular construction and transformation where it doesn’t (and I don’t know why).
Read on for the example and a workaround that Forrest found.
Comments closedAndy Brownsword takes a closer look at the big three join operators in SQL Server:
In the previous post I explained Join Operators in SQL Server. Whilst compiling that I dug a little deeper and came across a few interesting points I thought were worth sharing.
Let’s look at behaviour of the operators which may occur under specific conditions. Hopefully you find them as interesting as I did:
Click through for an interesting tidbit about nested loops joins, merge joins, and hash joins.
Comments closedErik looks at a classic performance difference between ROWS and RANGE, as well as what batch mode does to even the score. This is particularly nice because ROWS and RANGE both have their utility and focusing on one versus the other for performance differences can lead to awkward development practices to get around a window spool.
Erik also focuses primarily on batch mode on rowstore, so keep in mind the minimum requirements for it: 131,072 (or 2^17) rows in at least one table in the query, at least one operator that benefits from batch mode (which we’d cover in the window function), at least one input of the batch with 2^17 rows, and where the batch mode cost is lower than the row mode cost.
Comments closed