Press "Enter" to skip to content

Category: Performance Tuning

Optimizing Power BI Semantic Models via Column Splitting

Published 2026-11-03 by Kevin Feasel

Phil Seamark provides some advice:

Column splitting is an idea worth testing when you are looking to reduce the size of a large semantic model to potentially reduce the SKU required to host the model. The good news is, the technique can also benefit the time to load data into a model and speed up your DAX queries.

Instead of storing each complete value in one column, split the column vertically and store it in smaller pieces. Then use a DAX measure to combine the pieces back together. Those pieces often repeat more frequently, which can help Power BI compress them.

What’s interesting is how similar this approach is to financial systems from back in the day, where all money was stored as two separate integers. In that case, it was for the sake of precision and correctness rather than performance, but it’s nice to see these techniques still kicking.

Leave a Comment

Lakehouse Design and Direct Lake Performance

Published 2026-11-02 by Kevin Feasel

Nikola Ilic shares some numbers:

When a Direct Lake report is slow, people open the semantic model first. They check relationships, rewrite DAX, look at the capacity metrics. In my experience, the cause usually sits one layer down, in how the Delta tables under the model were written.

That sounds like a bold claim, so I wanted to test it properly. I took one dataset of 500 million rows and wrote it to seven Delta tables. Each table differs from a baseline in exactly one way: the number of files, the row group size, V-Order, or partitioning.

The performance matters more for cold data, though when you’re dealing with a data lake, that risk goes up considerably versus a relational database platform where we try to keep as much data in cache as possible.

Leave a Comment

Load Testing Azure SQL GP and BC

Published 2026-10-30 by Kevin Feasel

Reitse Eskens continues a series on load testing. First up is Azure SQL Database General Purpose edition:

My previous post on load testing Azure SQL covered the Premium Edition; today I’m moving to the General Purpose Tier.

The GP tier comes in two flavours: regular and serverless. Serverless has only one hardware tier: Gen5. With provisioned, you can also select memory-optimised or confidential compute. In earlier tests, I never found significant differences between serverless and provisioned, so I skipped testing both SKUs. Given the nature of these tests, going memory-optimised didn’t seem to make much sense either. Also, I’ve never seen any other deployment than the regular Gen5 General Purpose database.

And then there’s Business Critical:

My previous post on load testing Azure SQL covered the General Purpose Edition, let’s move it up to the Business Critical Tier

The Business Critical tier comes in two flavours: General and confidential compute. For my testing, I’ve stuck with the General SKU. I haven’t seen confidential compute with customers, and it has very specific use cases: you want to protect your threads on the processor itself. A lot happens behind the scenes to make that work, and it costs more. There are scenarios where this makes sense, but not in my work. For example, using 8 cores for regular business-critical workloads costs about 3,650 euros each month. Confidential Compute will cost about 6,000 euros each month. So yeah, you need a use case for that.

Click through for results on each.

Leave a Comment

Reviewing Optimized Locking Results

Published 2026-10-28 by Kevin Feasel

Louis Tochon wants to know if optimized locking actually ran:

In the first case, SQL Server escalated the row locks to an X lock on the whole table. In the second, there is no escalation anymore, only one X lock on the transaction ID (XACT), next to the intent lock (IX) on the table (the IS lock in both results is on the small function I used to run this query).

That is nice in a demo, but on a real server nobody is looking at sys.dm_tran_locks at the right moment. I wanted to know how a DBA can check what optimized locking does once it is enabled. SQL Server 2025 has a few Extended Events for that, and I found some of their fields easy to misread and one of them reports LAQ as enabled on a database where it never ran once.

Click through to learn more.

Leave a Comment

Entry Points and Graph Neighborhoods with DiskANN

Published 2026-10-21 by Kevin Feasel

Mala Mahadevan tries something out:

I was experimenting with vector search in SQL Server 2025 using approximately 3.8 million Hacker News articles that that I used for my talk on SQL Server-based vector search feature at EightKB. I generated two sets of embeddings from the same articles: one stored as FP32 (32-bit floating point) and the other as FP16 (16-bit floating point). Both sets contained the same articles and used DiskANN vector indexes.I used a query as below to find articles similar to a selected example.

What Mala found was that FP16 queries could sometimes take longer and require more reads than FP32 queries, which doesn’t make sense on its face. Click through to see two experiments she devised to come up with the answer.

Leave a Comment

When ApproximateDistinctCount is Slower than DistinctCount in DAX

Published 2026-10-16 by Kevin Feasel

Chris Webb explains the paradox:

As a follow-on from last week’s post about the new ApproximateDistinctCount function in DAX, I want to share something I found out from my recent testing for this function: ApproximateDistinctCount is sometimes slower than DistinctCount. This sounds counter-intuitive, right? After all ApproximateDistinctCount is a performance optimisation for the sometimes slow, expensive DistinctCount function. But it turns out that there are certain engine optimisations for DistinctCount that can’t be replicated for ApproximateDistinctCount which mean that for low-cardinality columns using DistinctCount can be faster.

Click through for an example.

Comments closed

A Statistical Error

Published 2026-10-14 by Kevin Feasel

Hugo Kornelis tells a story:

Anyway, the load process at this customer had multiple steps. Truncate and then fill staging tables with the current data from various source systems. Use the data in those staging tables to update the first of the data vault tables. And then use a join between the same staging table plus the just updated data vault table, with a filter on the current date, to update all other data vault tables.

With some of the tables having more than 500 million rows and over 50 columns, this process needed some time. But that was not a problem. The load finished before the offices reopened. Usually. But not always. Sometimes, it would run a few hours longer. And nobody knew why the load would take extra time at those seemingly random moments.

Read on for the full story. It’s a sobering reminder that each environment is different, and something that worked really well in one environment may fare poorly elsewhere.

Comments closed

Clustering in Fabric Data Warehouse

Published 2026-09-25 by Kevin Feasel

Louis Davidson does some exploring:

Clustering is one of the data terms that could not have been created by a database design person. Why? Because it has multiple meanings. In PostgreSQL it means the same thing as an instance in SQL Server. In server architecture it means connecting multiple servers together to form a “single” unit of some form to protect against failure. Then it means an index that determines the physical storage of a table in some manner. This usage of the word means the former.

The weirdest part of using a Fabric Data Warehouse when you first try to look at a query is the lack of indexes. So (currently as of writing this is late 2026), you only have this one tool to affect your performance. The tool allows you to order the data physically in your files so query processing can be reduced when searching the physical file.

Read on for an introduction of how it works, with the caveat that Louis is about two pages ahead of most people in the book.

Comments closed

Compiling Power BI Calculation Groups

Published 2026-09-03 by Kevin Feasel

Phil Seamark looks at an optimization:

Power BI now avoids compiling calculation items that a query has already filtered out. The time saved is before the first storage engine event. It does not make the scans themselves faster.

Will I benefit? If your query filters a calculation group down to a subset of its items, it may compile faster. A single filtered group can benefit, although often only by a little. The largest gains are in models where calculation groups reference one another, because the engine used to expand combinations the query never needed. If your query has no calculation groups, or uses every item in them, there is nothing to prune.

Click through to see what the hubbub is all about and if it might affect you.

Comments closed

Choosing COUNTROWS() over DISTINCTCOUNT() in DAX

Published 2026-08-10 by Kevin Feasel

Phil Seamark provides some guidance:

Counting transactions is one of the most common things a retail model is asked to do. How many transactions did we do in March. How many in New Zealand. How many involved a T-shirt.

The obvious measure is the one everybody writes:

Transactions = DISTINCTCOUNT ( FactTransaction[TransactionID] )


It is correct, and on a large fact table it is one of the most expensive things you can ask the storage engine to do.

Read on for a better alternative, and more importantly, when it’s a better alternative.

Comments closed