Press "Enter" to skip to content

Curated SQL Posts

Next Token Selection in Language Models

Ivan Palomares Carrascosa explains how three knobs shape the outputs of a language model:

In this article, you will learn how logits, temperature, and top-p sampling work together to control next-token prediction in large language models.

Topics we will cover include:

  • What logits are and how they are produced by a transformer’s final linear layer.
  • How temperature and top-p (nucleus sampling) shape the probability distribution used for token selection.
  • How these three components fit into a sequential pipeline that governs LLM output generation.

Click through for that explanation.

Leave a Comment

Filtered Indexes in SQL Server

Erik Darling has a new video:

Now, you just can’t talk about indexing in SQL Server really without talking about filtered indexes. They are a very, very important thing. Conceptually, they are just not that hard to figure out.

It’s an index with a where clause. It only indexes some of the data. It qualifies for the where clause. I don’t know. Like the benefits of that just seem rather apparent to me.

Benjamin Franklin highly encourages you to watch this video, even though filtered indexes are one of the most frustrating things in SQL Server. There are so many cases where I think they should work, and they actually work in approximately a third of those cases.

Leave a Comment

Tips for a Terabyte-Sized Database

Brent Ozar recommends some actions:

You were minding your own business, and all of a sudden it happened.

You glanced at file sizes one day, and your eyes got big. The numbers got a little large while you weren’t looking. This is a great time to stop and think about a few changes to the way you’re managing this database.

These are some good recommendations on the whole. 1TB isn’t a magic number, but it’s a pretty decent dividing line.

Leave a Comment

Automatic Index Compaction in Azure SQL

Chad Callihan takes a look at a preview feature:

There isn’t one set way to manage indexes. Maybe you use Ola Hallengren scripts. Maybe it’s something you put together yourself. Either way, there might be a big shift coming for SQL Server database administrators and how index management is handled.

Last month, Microsoft announced Automatic Index Compaction, which is in preview for Azure SQL Database, Azure SQL Managed Instance, and SQL Database in Fabric. Instead of utilizing something like Ola Hallengren scripts or your own homegrown setup to monitor and rebuild indexes, the database engine will continuously run in the background and handle indexes for you, hence the “automatic” in the name.

Read on to see how it works, as well as a note around page density and index fragmentation. But Jeff Moden makes a good point in the comments, so check that out.

Leave a Comment

Polymorphic Associations in Postgres

Andrei Lepikhov has multiple types:

Planning such a query efficiently is no easy task — and in my experience, this is confirmed by user reports from the 1C world, since PostgreSQL is currently not rich in LEFT JOIN optimisations. At the same time, the properties of this pattern enable the development of various techniques to improve execution efficiency. I’ve managed to implement several straightforward optimisations of this template. But first, let’s understand what polymorphic references actually are, where they come from, and how common they really are. That’s the gap I’m trying to fill with this post.

Click through for the explanation. This isn’t the easiest problem to solve in the relational world, though I do tend to prefer the subclass/superclass solution, myself.

Leave a Comment

Benchmarking Kafka

Jack Vanlightly continues a series on Dimster. First up is a benchmark of consumer groups versus share groups:

In this first share group benchmarking post, we’re going to use share groups as they are not intended to be used, but for a good reason. Share groups allow you to move past partitions as the unit of parallelism by allowing multiple consumers to read from the same partition, using message queue semantics. We’ll run those kinds of tests in the next post. In this post I just want to understand if the mechanics of how share groups work add any additional overhead compared to consumer groups. So we’ll use share groups as if they were consumer groups (by capping consumer count to partition count).

Objective: Use synthetic tests to measure the overhead of share groups compared to consumer groups in identical conditions.

After that, Jack simulates processing time:

In this post we’re going to simulate processing time in the consumers to make these benchmarks more realistic and show the utility of share groups (namely the ability to parallelize processing beyond the partition count).

We’ll see how the following two configurations play an important role in parallelizing consumption with share groups:

  • max.poll.records (consumer config)
  • group.share.partition.max.record.locks (broker-side config)

And there’s one more post in the series so far:

In the last post we used simulated consumer processing time to reveal how important it is to set an appropriate value for max.poll.records to ensure the consumer parallelism that we expect. With a uniform distribution of messages over partitions, the rule of thumb was a value somewhat lower than:

group.share.partition.max.record.locks / number of consumers per partition

But there’s more to parallel consumption than max.poll.records. The size of producer batches also plays a role when using the default share.acquire.mode (batch_optimized).

Stay tuned for the next post in the series.

Leave a Comment

Five Locking Behaviors to Watch for in PostgreSQL

Shinya Kato has a list:

PostgreSQL uses MVCC (Multi-Version Concurrency Control) for concurrency control: reads never block writes, and writes never block reads.

Its locking system has 8 table-level lock modes and 4 row-level lock modes, and the conflict tables in the documentation tell you exactly which lock modes conflict with which.

In practice, though, once you actually operate PostgreSQL, locks end up conflicting in places you never expected. Queries take far longer than anticipated, and in the worst case you end up with an outage.

Click through for five notes around locking that may not be immediately apparent.

Leave a Comment

PSBlitz 6.1.0 Released

Vlad Drumea has a new version of PSBlitz:

For anyone not familiar with PSBlitz: it’s a PowerShell-based tool that outputs SQL Server health and performance diagnostics data to either Excel or HTML, and saves execution plans and deadlock graphs as .sqlplan and .xdl files.

If you’re familiar with Oracle, this is pretty much my attempt of a SQL Server equivalent to Oracle’s AWR report.

Feel free to check out the blog post announcing PSBlitz 4 years ago, as well as the 3 year anniversary post.

Read on to see what Vlad has added.

Leave a Comment

Storage of Memory-Optimized Columnstore Indexes

Hugo Kornelis joins a pair of technologies:

Time for the next part in my series on storage structures. The previous parts covered on-disk rowstorecolumnstore indexes, and memory-optimized storage. In this part, I will look at the combination of the latter two: memory-optimized columnstore indexes.

Memory-optimized columnstore indexes were introduced in SQL Server 2016. I’ve seen some slick Microsoft marketing sessions in that time that were big on “real-time operational analytics”. A new trend where analytical processing would no longer be done on a stale copy of the data in a separate data warehouse, but directly on the OLTP database. Reports would always be fully current, there would be no more need for an ETL pipeline, and due to the combination of memory-optimized for OLTP workloads and columnstore for analytical processing, everything would always be fast. In theory.

Yeah, this one kinda fizzled out quickly. It was in line with the HTAP craze from about the same time period. And Hugo shows in this post part of why very few companies ever adopted it.

Leave a Comment