Press "Enter" to skip to content

Author: Kevin Feasel

The Pain of Residual Predicates

Brent Ozar has a new animation:

If we modify our query a little by selecting all of the columns instead of just Id and Location, then we have to do a Key Lookup, like we talked about in the How to Think Like the Engine class. For each person who lives in Helsinki, we have to look up their row in the clustered index in order to fetch all the columns we need. That’s not really a big deal, though, as long as a relatively limited number of people live in Helsinki. Like I wrote in that post, the index seek + key lookup is essentially two index seeks: one into Helsinki, and then one seek (for each Helsinki resident) on the clustered index, by their Id.

However, let’s add a little more complexity to the query:

Click through for a scenario in which a more selective query can result in worse performance than a less-selective variant.

Comments closed

Further Thoughts on the SSMS SQL Formatter

Chad Callihan takes another look:

I posted a few months ago about the latest SQL Server Management Studio updates, including a preview of their SQL Formatter. SSMS 22.9.0 was released a few weeks ago and called out some SQL Formatter improvements on the release announcement, so I thought it would be worth taking a fresh look to see how things are moving along.

Click through for Chad’s current thoughts. The big challenge with a good SQL formatting tool is that it be adaptable to somewhat complex formatting standards. If the tool can’t do what your team has agreed on, then the tool’s not going to cut it for more than occasional personal use.

2 Comments

Understanding the DiskANN Algorithm

Mala Mahadevan digs into an algorithm:

DiskANN is meant to help with searching a billion-vector dataset from a single machine using SSDs.

Microsoft Research’s original work demonstrated a billion-point index on a workstation with 64 GB of RAM and an SSD, while targeting high recall and low query latency. Before we get to ‘why ssd’ and details of storage, we need to understand the basics of vector search and underlying terminologies.

Click through for a depiction of the paper and quite a few examples of how the mechanisms work.

Comments closed

Dynamically Changing Fabric Data Warehouse SQL Pools

Gilbert Quevauvilliers saves some money:

After reading about the new SQL Pools feature for Warehouses in Fabric, I had an idea, if I could change the SQL Pool configuration based on the expected query load, I could then consume less capacity and have better performance.

https://learn.microsoft.com/en-us/fabric/data-warehouse/custom-sql-pools

Here is an Example I thought of below.

  • When the ETL load is running optimize the SQL pool for writing as typically data is being inserted.
  • After the ETL load and for the rest of the day, almost all queries are read by the Warehouse, so change the SQL pool to be read optimized.

Click through for a Python notebook that does this.

Comments closed

Moving from a Named Instance to a Default Instance

Brian Kelley makes a move:

I have a SQL Server named instance that is used by various resources. There may even be reports and other artifacts that access the named instance which we don’t know about. Having a named instance means in a recovery situation we must have a SQL Server installed as a named instance with the same name. This impairs our recoverability as well as our ability to upgrade SQL Server versions because we must retain that named instance. Is there a path to migrate to a default instance without potentially breaking things?

Click through to see how.

Comments closed

DDL Modifications and Change Data Capture

Erik Darling has a new video:

So I’ve had to deal with this with some clients recently, and the problem with CDC, of course, is that if you change, add, drop columns from CDC tables, or tables that are covered by CDC, rather, the current change capture table does not reflect those changes. You have to do some work to figure it out. What I’ve got in this video is I’m just going to, a script that I can walk through, I can hit F5 on it.

Click through for the script, information on capture instances, and more.

Comments closed

T-SQL Tuesday 201 Round-Up

Jeff Taylor gives us the low-down:

A couple of weeks ago, I asked a simple question with a loaded answer: are temp tables a friend or a foe? The responses did not disappoint. They ran from full-throated defense to a measured “it depends,” and one of you built a lab. That’s what I was looking for.

If there is a consensus, it is this: the reflex is the problem, not the tool. Almost everyone agreed that dumping data into a #temptable out of habit is a mistake. Almost everyone also had a case where a temp table was the right answer, and sometimes the only one. So let’s get into it, in no particular order.

Click through for the cast of characters and what everyone came up with.

Comments closed

INFORMATION_SCHEMA and the Fabric Warehouse

Louis Davidson bangs his head against a wall:

When we decided to use T-SQL and a Fabric Data Warehouse for our ETL, I started thinking about generating the code with the metadata in the system catalog views or the INFORMATION_SCHEMA. Having done this sort of thing before in SQL Server over the years, it seemed really straightforward. And it kind of is, until it isn’t.

In this blog I want to show you a few ways you need to understand how working with metadata and temp tables varies (sometimes wildly) from the comfortable SQL Server environment and language you know very well, and give tips on how to get around these differences.

Click through for some of the fun you can have with a distributed SQL Server-like product.

Comments closed

Building a Mental Model of LLMs

John Mount puts together an idea:

As of now (late 2026) LLM (large language model) technology providers, users, and work products flood the public commons. It therefore makes sense to have even a primitive mechanistic mental model of these technologies. You are forced to have an opinion. Without a mechanism or model one tends to fall into disempowering anthropomorphic language. Some clear thoughts on this can be found here and here.

In this note I would like to try and outline a (very) simplified mental model of LLM mechanics and mechanisms. By “mental model” I mean a cartoon to work through in your mind, not a model of the LLMs as having their own mind. I won’t be teaching the history of LLMs, how to build them, how to use them, or their moral or philosophic implications. I will only try to give a very rough outline how the current (2026) LLMs work.

Click through for an intuitive explanation of how they work.

Comments closed

Waiting for GOdot, SSMS Edition

Tom Zika waits to pass GO:

I deployed a schema change to 30 servers using my deploy-at-low-priority script via SSMS multi-server query. Some of these servers were small with almost no activity, so I expected them to finish within seconds. I opened another multi-server connection to check on progress and none of the small servers showed any sign of having completed. Then all 30 finished at the same time.

Read on to see what happened and why.

Comments closed