Press "Enter" to skip to content

Month: January 2026

Performance of Regular vs Limited Relationships in DAX

Published 2026-01-02 by Kevin Feasel

Marco Russo and Alberto Ferrari do a performance comparison:

Relationships between different data islands are the most common case of limited relationships. In that scenario, performance depends on multiple factors, most of which are not under the control of a DAX developer. Indeed, when mixing data from different data islands, the DAX formula engine must act as a bridge between them, resulting in complex execution plans. Besides, when two tables reside in different data islands, only limited relationships can connect them. Therefore, a performance comparison would not make sense, as there are no alternative options to link the tables.

However, a model can have limited relationships in the very special case of two tables stored in the same data island and connected by a many-to-many cardinality relationship. By nature, many-to-many cardinality relationships are limited. While they seem like a convenient way to link two tables when the key used to link them together is not unique in both tables, many-to-many cardinality relationships are extremely expensive, and a wise data modeler should limit their use strictly to cases where they are absolutely necessary. In this article, we analyze the differences between regular and limited relationships, focusing solely on performance.

Read on to learn more.

Leave a Comment

The Cost of Heaps in SQL Server

Published 2026-01-02 by Kevin Feasel

Heaps killed Vlad Drumea’s dog and now it’s time to get revenge:

In this post I cover some unpleasant side-effects of using heaps in SQL Server for anything else than staging tables.

In the past couple of months I’ve been running into performance issues caused by the strange decision to have very active tables as heaps.
And, at this point, I’m on a personal vendetta against heaps in SQL Server.
As a result, I’m writing this blog post so that I can refer people to it the next time I’ll have to explain why heaps don’t match their use cases.

I, for one, wholeheartedly support Vlad’s vendetta. There are very rare circumstances in which it makes sense to use a heap in a non-temporary SQL Server table. Otherwise, define that clustered index.

Leave a Comment

Default Frames in Window Functions

Published 2026-01-02 by Kevin Feasel

Hugo Kornelis has gotten into the video game:

Below, you find my first video blog. I talk about the OVER clause, and I hope to convince you why you should always explicitly specify the ROWS or RANGE clause – even though that takes a bit more typing.

Understanding what the default frames look like for your queries is important. And it’s important to note that the default frame is different based on whether you use SUM(x) OVER() or SUM(x) OVER(ORDER BY X). It also doesn’t matter for ranking window functions (ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()) because you’re not allowed to specify a window frame.

Leave a Comment

SQL Server 2025 Regular Expression Guidelines

Published 2026-01-02 by Kevin Feasel

Ben Johnston shares some thoughts:

I started writing this post with the intention of showing performance details and differences between the legacy TSQL functions and the new regex functions, along with basic functionality examples. There is just too much information to put this into a single post, so I’ve split it. This post covers my initial findings and basic guidelines for using regex functions. The next post is an in-depth look at performance. It compares legacy functions and their equivalent regex functions. I’ve already seen posts covering functionality, so that’s why I’m primarily focused on performance. I spend a good portion of my time on performance tuning, so it’s one of the first questions I ask about a new solution, especially one with functionality that could replace some legacy functionality.

This post is high-level advice for each of the functions. The short version is most of the regular expressions won’t make your code faster, so save them for the cases that are extremely difficult or impossible to solve using classic T-SQL techniques. But there are a couple of winners.

Leave a Comment

Implementing Tooltips on Dynamic Measures

Published 2026-01-02 by Kevin Feasel

Teo Lachev shows some information:

Happy New Year with oldies but goodies…especially given that this technique might be hard to find elsewhere! How do you implement a tooltip page that shows only the measure the end user hovers over? Consider this matrix with three measures on columns grouped by product categories on rows. The user wants to see a trend chart for the measure they hover over.

I’m trying to remember if this is possible in Power BI Report Server. I don’t want to slander the product unnecessarily (my necessary slander should be good enough, thank you), so I won’t hazard a guess.

Leave a Comment

Thoughts on Master Data Services Removal

Published 2026-01-02 by Kevin Feasel

Niko Neugebauer is upset:

I have worked with Master Data Services (aka MDS) extensively, especially during the engagement at OH22 around a decade ago, and I my interest in the area and that specific is documented in my blog – with the following examples: What is wrong with MDS (Master Data Services) Database in SQL Server 2019 & Master Data Services in SQL Server 2019.
In my professional experience (at OH22) I even had to dig into the internal code of the MDS and from my experience the Software Engineers at Microsoft at MDS team I have talked to – knew much less about it than I did.

MDS was launched in SQL Server 2008 R2 and the feature was there with minor changes since SQL Server 2016, with the last update being done in SQL Server 2019. Now, in SQL Server 2025 we have a significant change – Master Data Services are dead. Yes, you read it right – not deprecated and will be removed in the future, not deprecated and unsupported, but – removed.

Read on for a paean to a removed product and commentary on the lack of a viable replacement within Microsoft.

Leave a Comment