Press "Enter" to skip to content

Day: February 4, 2026

Implementing the OPTICS Clustering Algorithm in SQL Server

Published 2026-02-04 by Kevin Feasel

Sebastiao Pereira implements an algorithm:

Ordering points to identify the clustering structure (OPTICS) is an algorithm for finding density-based clusters very similar do DBSCAN. However, OPTICS handles it more effectively in the case of a cluster with varying densities gaining deeper insights exploring the hierarchical structure of your data. This algorithm is generally more computationally intensive.

Is it possible to have the OPTICS clustering algorithm implemented in SQL Server without using an external solution?

Click through for that implementation.

Leave a Comment

The Basics of Transaction Logging

Published 2026-02-04 by Kevin Feasel

Paul Randal republishes an older post that is no longer available:

All through my career as a data professional, both inside Microsoft and as a consultant, I’ve found that one of the most misunderstood parts of SQL Server is the transaction log. Lack of knowledge of how the transaction log works and needs to be managed, or just simple misconceptions, can lead to all kinds of production problems, including:

  • The transaction log growing out-of-control and potentially running out of space
  • Performance issues from repeated shrinking of the transaction log
  • Performance issues from a problem known as VLF fragmentation, which I discussed in this post
  • The inability to recover to a desired point in time using transaction log backups
  • The inability to perform a tail-log backup during disaster recovery (see here for an explanation of tail-log backups)
  • Various issues around failovers and restore performance

With this post I’m starting an occasional series (now here on my SQLskills blog) on the transaction log and how it works and should be managed, and I’ll touch on all the problems above over its course. In this post I’ll explain what logging is and why it’s required.

Read on for that explanation.

Leave a Comment

NOWAIT Hints and Annoyances with Query Store Hints and Plan Guides

Published 2026-02-04 by Kevin Feasel

Erik Darling performs a rather late Airing of Grievances:

In this video, I delve into some of the frustrations and annoyances associated with query store hints and plan guides in SQL Server. I explore how these tools can sometimes hinder rather than help, particularly when trying to override certain behaviors or improve performance. For instance, I demonstrate the quirks of using a `NO_WAIT` hint in a transactional context and highlight why Query Store’s inability to support table hints is such a significant limitation. Additionally, I discuss the cumbersome nature of plan guides, especially their requirement for maintaining semantic affecting hints that might be detrimental to query performance. These issues underscore the need for more robust and flexible tools within SQL Server to better meet the diverse needs of database administrators and developers.

Click through for the video.

Leave a Comment

The Challenge of Many-to-Many Relationships in Power BI

Published 2026-02-04 by Kevin Feasel

Ben Richardson explains a common anti-pattern in Power BI semantic models:

Relationships sit at the heart of literally everything you do in Power BI.

Before you make measures, visuals and reports, relationships are established to define how your data fits together. Their job is simple on the surface – but vital: describe how each table is connected.

If you can design these relationships well, everything else will run much smoother.

Across any data domain, strong models rely on clear Grain, correct Cardinality, and a Star Schema built with well-defined Fact and Dimension tables.

Read on to understand how many-to-many relationships stress this understanding in Power BI an different techniques for dealing with those sorts of relationships.

Leave a Comment

SQL vs Azure Permissions

Published 2026-02-04 by Kevin Feasel

Rebecca Lewis continues a series on how Azure permissions and SQL Server (or Azure SQL Database) permissions are not the same thing:

Welcome to cloud permissions, where ‘Contributor’ doesn’t mean you can contribute and ‘Reader’ doesn’t mean you can read.

In my last post, I explained the management plane vs data plane split. This post is the promised follow-up for the minimum permission combinations for common DBA tasks. aka, what you need, how to verify it, and how to fix it when it fails.

I’m the guy pushing up my no-longer-existent glasses and saying “Well, actually…” to the first sentence, though stylistically, it’s a good one. But getting past the first sentence, there are some nice breakdowns of what it takes to do what you need to do on a cloud-hosted database.

Leave a Comment

Changing SQL Server on Linux Editions

Published 2026-02-04 by Kevin Feasel

Vlad Drumea swaps the edition:

In this post I cover the steps required to change (downgrade or upgrade) the edition of a SQL Server instance running on Linux.

In my previous post I’ve went through the steps of installing SQL Server 2025 on Ubuntu 24.04 LTS.
While the process is pretty straight-forward, there might be cases where someone can accidentally specify the wrong edition and only notice afterwards.

Luckily, the edition can be changed with just a few commands.

It’d be neat if it worked the same way for Windows.

Leave a Comment

Excel Pivot Table Performance Improvement Connecting to Semantic Models

Published 2026-02-04 by Kevin Feasel

Chris Webb has some good news:

Some good news: an important optimisation has rolled out for Excel PivotTables connected to Power BI semantic models! Back in 2019 I wrote about a very common problem affecting the MDX generated by PivotTables connected to Analysis Services where, when subtotals are turned off and grand totals are turned on, the query nevertheless returns the subtotal values. This led to extremely slow, expensive MDX queries being run and a lot of complaints. The nice people on the Excel team have now fixed this problem and PivotTables connected to Power BI semantic models generate MDX queries that only return the values needed by the PivotTable.

Read on to see it in action. What’s wild is that I actually get to use the “MDX” tag here. I had to go back and check the four MDX entries prior to this, and Chris is responsible for three of them. That scans.

Leave a Comment