Press "Enter" to skip to content

Category: Performance Tuning

One Problem with Scrollbars in Power BI

Published 2024-07-16 by Kevin Feasel

Chris Webb focuses on the performance aspect of scrollbars on tables:

Concluding my series of blog posts on seemingly harmless things you can do in a Power BI report that can lead to performance problems and the “This visual has exceeded the available resources” error (see also “Calculate(), Filter() and DAX memory usage” and “DAX measures that never return blank“), in this post I’ll show how table visuals with vertical scrollbars that potentially show thousands of rows can be a Bad Thing.

I’d also note the aesthetic problem: the intent of a dashboard is to be glanceable, meaning that a user can gain sufficient understanding of what is happening without needing to click, drag, sort, filter, or otherwise manipulate the dashboard. This means, if you are intending to create a dashboard (versus a report), vertical scrollbars form a second sort of issue: you’re obscuring data that you consider important enough to show to the end user.

Comments closed

Tips to Identify Poorly-Performing Code

Published 2024-07-16 by Kevin Feasel

Rich Benner shares a few tricks:

So, you have a database or block of code. You’ve been told to “make it fast” but you’re not sure where to start. I’ve got you. We’re going to create a process to follow to ensure we can tune effectively and prove that we’ve made things faster. It’s all about having a structure when performance tuning.

Read on for Rich’s recommendations on how to identify poorly-performing code.

Comments closed

Antipattern: DAX Measures Never Returning Blank

Published 2024-07-08 by Kevin Feasel

Chris Webb explains the value of BLANK:

Following on from my earlier post on the Query Memory Limit in Power BI, and as companion to last week’s post on how a DAX antipattern using Calculate() and Filter() can lead to excessive memory consumption by queries (and therefore lead to you hitting the Query Memory Limit), in this post I want to look at the effects of another DAX antipattern on performance and memory usage: measures that can never return a blank value.

Read on to see how much of a difference using DAX to fill a grid with 0’s can make.

Comments closed

Performance Testing Microsoft Fabric Dataflow Gen2

Published 2024-07-05 by Kevin Feasel

Reitse Eskens hammers away:

In my previous blogs, I’ve been hammering Fabric with data from some different angles. Either with the Copy dataflows, notebooks, Pipelines, Data Warehouse SQL scripts or in PowerBI.
This time, I’m going to make the dataflow Gen2 work for it’s money.

Reitse tries the normal mechanism for Dataflows Gen2, but then also tries out a preview feature for fast copy and sees a marked difference.

Comments closed

Postgres Tuning Settings

Published 2024-07-03 by Kevin Feasel

Semab Tariq shares a few tips:

PostgreSQL is a widely used database known for its robust performance and reliability. To get the most out of PostgreSQL, tuning its parameters is crucial.

In this blog, we will explore the various PostgreSQL performance-related parameters and how to tune them effectively. By measuring Transactions Per Second (TPS) before and after tuning, and analyzing the results, we will demonstrate the significant impact of tuning on PostgreSQL performance.

Click through for some of the sorts of settings you might want to review. In Semab’s case, a simple server achieved nearly 30% better throughput after making these changes, so that’s not bad for the level of effort.

Comments closed

Performance Tuning XML Operations in SQL Server

Published 2024-06-24 by Kevin Feasel

Ed Pollack does a bit of tuning:

SQL Server provides a variety of ways to tune XML so that it provides consistent performance, consumes less space, all while ensuring efficient access to critical data.

At its core, the metadata-styled XML format runs counter to the data that SQL Server is optimized to manage. Therefore, additional features were added to SQL Server over time that allowed for XML data to be indexed and compressed.

While these features are critical for managing XML data as it becomes large, it is important to remember what XML is intended for and why it is (loosely) structured as it is. Many data professionals have used shortcuts when XML was small, such as storing and analyzing it in string format, only to be forced to reckon with performance challenges when scanning large strings become agonizingly slow.

Read on for the full article.

Comments closed

Understanding Worktables in SQL Server

Published 2024-06-21 by Kevin Feasel

Steve Stedman takes a peek at tempdb:

A worktable in SQL Server is a temporary structure that the SQL Server Database Engine uses to process certain types of queries. These tables are not explicitly created by users but are generated by SQL Server internally to handle specific operations that cannot be managed directly within memory. Worktables are stored in the tempdb database and are crucial for facilitating complex query execution plans.

Read on for examples of when SQL Server will use worktables and some good ideas when you spot worktables in the wild. They’re not inherently bad, but there are some performance problems you could experience around them.

Comments closed

TempDB Contention and SQL Server 2022

Published 2024-06-19 by Kevin Feasel

Simon Liew shows off a change in SQL Server 2022:

Tempdb often acts as a high-traffic repository in SQL Server, experiencing significant contention. This includes not only temp table usage but also processes such as triggers, worktables for storing intermediate results for spools, cursors, sorts, work files for hash join, and temporary large object (LOB) storage, just to name a few.

A prominent issue is Global Allocation Map (GAM) and Shared Global Allocation Map (SGAM) system page latch contention, which can be particularly problematic under specific high concurrency workloads.

Read on for a demonstration of this.

Comments closed

Comparing Microsoft Fabric Warehouse and Lakehouse Performance

Published 2024-06-03 by Kevin Feasel

Reitse Eskens busts out the stopwatch:

I just can’t seem to stop doing this, checking the limits of Microsoft Fabric. In this instalment I’ll try and find some limits on the data warehouse experience and compare them with the Lakehouse experience. The data warehouse is a bit different compared to the Lakehouse, so I’ll be digging into that one first. Then I’m going to load data into the warehouse with a copy data pipeline followed by some big queries to test performance. The Fabric Capacity App will be used to check out the capacity necessary (or used for that matter).

As usual, I’m using the F2 capacity as it’s the one that should break the easiest. It’s also the cheapest one to run tests against and, as the capacity calculation isn’t dependent on the SKU (Stock Keeping Unit), you can easily translate to find out which capacity SKU will fit the workload. Remember that your workload will differ from the one shown in this blog. These tests are a comparison between the different offerings, something you could do for yourself. These blogs are a bit of a happy place as every option will get a good chance. In your work, your skills (and those of your co-workers) will be a major driver towards an option. Even if this offers the chance to learn something new!

Reitse focuses on ingesting and transforming data and the results were quite interesting.

Comments closed

Diagnosing DirectQuery Connection Limit Performance Problems

Published 2024-05-22 by Kevin Feasel

Chris Webb does a bit of sleuthing:

A few months ago I blogged about the new limits available for the Maximum Connections Per Data Source property in Premium and why the number of connections that a DirectQuery semantic model can open to a data source is so important for report performance. At that time, however, there was no way for you to know whether the performance of your reports was being affected by a lack of available connections. The good news is that, with the announcement this week of the new Execution Metrics event in Log Analytics and Profiler, you can now see when this is happening.

Read on for an illustration of the problem and how you can resolve it.

Comments closed