Press "Enter" to skip to content

Category: Performance Tuning

Measuring Power BI Dataset Memory and CPU Utilization

Published 2023-05-01 by Kevin Feasel

Chris Webb checks resource utilization:

This post is a follow-up to my recent post on identifying CPU and memory-intensive Power Query queries in Power BI. In that post I pointed out that Profiler and Log Analytics now gives you information on the CPU and memory used by an individual Power Query query when importing data into Power BI. What I didn’t notice when I wrote that post is that there is also now information available in Profiler and Log Analytics that tells you about peak memory and CPU usage across all Power Query queries for a single refresh in the Power BI Service, as well as memory usage for the refresh as a whole.

Click through for a demonstration using Profiler.

Comments closed

Which Power Query Operations Are Most Resource-Intensive?

Published 2023-04-03 by Kevin Feasel

Chris Webb answers a question:

Last year I wrote a post about a change in the Power BI Service that meant the CPU Time associated with Power Query transformations was added to the total shown for a dataset refresh operation in Profiler traces and Log Analytics:

https://blog.crossjoin.co.uk/2022/07/03/measuring-power-query-cpu-usage-during-power-bi-dataset-refresh/

This was useful, but it didn’t tell you directly how much CPU Time was used by Power Query and it didn’t tell you which tables or partitions in a refresh were using the most CPU. It also didn’t tell you anything about Power Query memory usage. The good news that recently there has been another change that solves these problems.

Click through for the solution.

Comments closed

Troubleshooting and Fixing Grafana Performance Problems

Published 2023-03-27 by Kevin Feasel

Cameron Kerr tracks down dashboard problems:

Is your Prometheus performance causing your Grafana experience to suffer? Could you explain which of your tens of Grafana dashboards or users are causing performance problems in Prometheus? Let me show you how we unlocked insight into the performance of Grafana and Prometheus, allowing us to answer import service management questions such as:

  • Which dashboards are actually being used?
  • Who is actually using the dashboards?
  • Who has been issuing poorly-performing queries?
  • And which dashboard activity is causing my Prometheus instance(s) to perform poorly?
  • Is it slow because someone has a dashboard showing a year’s worth of data that auto-refreshes every 30 seconds?

Click through for a thorough article on finding performance bottlenecks in a platform which typically helps you find performance bottlenecks in other platforms.

2 Comments

Tips for Scaling Cassandra Clusters

Published 2023-03-21 by Kevin Feasel

Mario Tavares wants more zoom:

When the use case aligns with the architectural limitations, Cassandra excels at storing and accessing datasets up to petabytes in volume, delivering impressive throughput. As the data or workload volume grows, we expand the cluster linearly, ensuring consistent performance.

However, even when we adhere to the documentation and best practices and create an effective data model, we might encounter underperforming nodes or unexpected challenges with throughput scaling after a cluster expansion—and it’s not always clear what causes the imbalance. Linear scalability relies on the assumption that workload and data are evenly distributed across all nodes in a cluster, and the cluster capacity relates directly to the number of nodes. Sometimes, these conditions aren’t met, affecting linear scalability. So, we strive for scalability and balance and are willing to fulfill the necessary conditions.

Read on for a few common performance issues and what you can do about them.

Comments closed

Tips for Using a Data Lakehouse

Published 2023-03-20 by Kevin Feasel

James Serra shares some advice:

As I mentioned in my Data Mesh, Data Fabric, Data Lakehouse presentation, the data lakehouse architecture, where you use a data lake with delta lake as a software layer and skip using a relational data warehouse, is becoming more and more popular. For some customers, I will recommend “Use a data lake until you can’t”. What I mean by this is to take the following steps when building a new data architecture in Azure with Azure Synapse Analytics:

Click through for six notes.

Comments closed

Text Search Performance Optimization in Power BI

Published 2023-03-06 by Kevin Feasel

Chris Webb provides advice about a relatively new feature:

In the blog post for the October 2022 release of Power BI Desktop there’s a brief description of a new optimisation in Power BI for filters that perform searches in text columns. In this blog post I’m going to share more details about this optimisation and how you can make sure your reports can benefit from it.

Read on to learn a bit more about how it works, as well as a few tips on ensuring that you’re able to take full advantage of this optimization.

Comments closed

Diagnosing a Resource Semaphore Wait Issue

Published 2023-02-27 by Kevin Feasel

Jose Manuel Jurado Diaz finds excessive resource semaphore waits:

Today, we got a service request that our customer reported that they query are taking too much for their execution. The main wait stats found was RESOURCE_SEMAPHORE and I would like to share with you my lessons learned here. 

We executed this query to find out the queries and check the resource semaphore wait type. 

Click through for the queries and diagnosis.

Comments closed

Matrix Multiplication in R with DuckDB and SQLite

Published 2023-02-21 by Kevin Feasel

Karsten Weinert compares two databases:

On my laptop with 16 GB RAM, I would like to perform a matrix-vector multiplication with a sparse matrix of around 10 million columns and 2500 rows. The matrix has approximately only 2% non-zero entries, but this are still 500 million numbers and the column/row information, too large to work comfortably in-memory.

A while ago, I tried using sqlite for this task. It kind of worked, but was too slow to be useful. This weekend, I revisited the problem and tried using duckdb.

Read on for the results. I’ve heard enough positives about DuckDB over the past few weeks that it makes me want to try it out. H/T R-Bloggers.

Comments closed

Degree of Parallelism Feedback

Published 2023-02-15 by Kevin Feasel

Deborah Melkin looks at a nice feature in SQL Server 2022:

A couple of months ago, I wrote about my initial thoughts to SQL Server 2022. I think a lot of what I wrote still holds true. It feels like it’s building on the functionality from previous versions, especially when it comes to Intelligent Query Processing and its feedback capabilities.

The feature I’m most curious to find out more about is the Degree of Parallelism Feedback. (You can read more about it here.) One of the main reasons for this is I know very little about the settings.

Read on for Deb’s thoughts and a situation where it would have helped.

Comments closed