Press "Enter" to skip to content

Author: Kevin Feasel

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

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

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

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

25 Years of SQL Server Central

Published 2026-02-03 by Kevin Feasel

Steve Jones has a retrospective:

The oldest article we have on the site is Tame Those Strings! Part 4 – Numeric Conversions, by me. It’s dated 2001-04-18, though I think that’s a date we picked when we converted all the content from one database to another. The founders agreed sometime during Feb 2001 to jointly run SQL Server Central. Since we each owned the copyright of our articles from another site, we migrated several articles to build up our content library. This was back when Andy, Brian, and I all had full-time jobs and managed the site during breaks, nights, and weekends.

That was 25 years ago.

I think there are a lot of DBAs who cut their teeth on SQL Server with the help of SSC. I know I was one of them, having been a SQL Server Central e-mail subscriber for a very long time. Not 25 years, but 15+, at least.

It was one of the best pieces of advice I received from a poker buddy who was a database architect: first thing in the morning, spend about 20 minutes reading articles. It’s time that nobody else will come in to bother you, and you can focus on learning and self-growth without needing to follow the ebbs and flows of the day.

That blended quite well with some good advice from Sean McCown to set aside an hour a day for learning and experimenting.

Leave a Comment

Row Own-Goals

Published 2026-02-03 by Kevin Feasel

Hugo Kornelis didn’t come up with quite as good of a title:

In part 1 of this mini-series, I explained what a rowgoal is and how it works to optimize a query with a TOP or FETCH expression. Part 2 then showed a few less obvious other cases where the optimizer might introduce rowgoals. In all cases so far, those rowgoals were beneficial. They helped the optimizer come up with the best execution plan for the number of rows requested.

Click through for the video.

Leave a Comment