A Fine Slice Of SQL Server
I don’t have anything against the use of calculated columns in Power BI semantic models in general but you do need to be careful using them with DirectQuery mode. In particular when you have a DirectQuery connection to another Power BI semantic model – also known as a composite model on a Power BI semantic model – it’s very easy to cause serious performance problems with calculated columns. Let’s see a simple example of why this is.
Read on for Chris’s example.
Comments closedDany Hoter performs some troubleshooting:
A customer approached me and complained that a page in a PBI report was taking a very long time to refresh. (> 100 seconds)
An investigation showed that one chart was sending 273 queries to the ADX cluster.
Was it a coincidence that the chart showed a distinct count of customers in each one of 273 points of sale?
Spoiler: no, no it wasn’t.
Comments closedChris Webb has some ‘splainin to do:
Following on from my previous post showing how you can visualise the job graph for a Power BI Import mode semantic model refresh, I this post I will look at how you can interpret what the job graph tells you – specifically, explaining the concepts of blocking and waiting. As always, simple examples are the best way of doing this.
Click through for the explanation using a job graph.
Comments closedAndy Brownsword shares a warning:
A point of sale system being rolled out across hundreds of physical locations. Transaction data collected each night to be batch processed into a warehouse for usual types of analysis. Our integration preference was SSIS internally. A solution was deployed in preparation.
Rolling out of the new system started with a handful of locations which steadily increased as confidence grew. On the back of this the data hitting our solution was increasing too. With a trickle of data early on there were no issues as expected. A small volume of data from a small number of stores. The process flew. We left it doing it’s thing.
Read on to see the story take a darker turn and the importance of planning for scale.
Comments closedDany Hoter gives us a reason to minimize use of many-to-many relationships in Power BI:
I must admit that in the last two years I’ve told many Power BI/Kusto customers not to worry about relationships that are created as M:M.
I was pretty sure that with Direct Query, such relationships are fine,
Indeed, the generated queries looked fine and performed as expected.
I recently became aware that the number of queries generated for some visuals e.g. Matrix and tables can be affected by the type of relationships between the participating tables.
Read on for a description of why you shouldn’t load your Power BI semantic models with many-to-many relationships, especially once Kusto is involved.
Comments closedJose Manuel Jurado Diaz explains a concept:
In the realm of Azure SQL Database, query performance is a paramount concern for database administrators and developers alike. A critical aspect of this performance is understanding how SQL Server interacts with data, particularly through physical and logical reads. This article delves into these two fundamental concepts, providing insights into their impact on database performance and a practical lab to observe these metrics in action.
Read on for the difference, as well as a demonstration. With slow disks and insufficient RAM, it’s really important to know this difference. But as you have more RAM and move to formats like NVMe for storage, I’d argue that it’s less of an issue. The additional RAM, in particular, is important because the idea is that data access frequently will remain in the buffer pool for longer, so you’re more likely to see logical reads in action. Of course, poor indexing and bad decisions can ruin that idea, so don’t do that, okay?
Comments closedSalman Ahmed explains how shared buffers work in Postgres:
PostgreSQL is known for its robustness and performance right out of the box. However, different applications and different scales of data demand fine-tuning of various parameters to achieve optimum performance. One such significant parameter is shared_buffers, which, when configured correctly, can notably enhance the performance of your PostgreSQL database.
Read on to see what shared buffers are, why they are so important to Postgres, and how to figure out the right value to use.
Comments closedPostgreSQL, by default, is configured to run everywhere with minimum resource utilization. To achieve maximum performance under specific scenarios, PostgreSQL’s parameters can be tuned to enhance performance. One such parameter that can impact performance in PostgreSQL is work_mem.
In this blog we will discuss how work_mem can be used to optimize performance in PostgreSQL.
Click through for that discussion.
Comments closedAll aboard the Data Express! Let’s imagine our database as this massive train station. The trains are packed with information – from passengers’ details to the schedules. Every time you want to know when the next train to DevOps Land is, you have to ask the station master (the database). If too many folks keep asking the same question, the station master will get tired, slowing down the whole operation. So, what do we do? Enter: Caching!
Read on for different caching mechanisms in several major relational databases, various reasons for external caches (like Redis and memcached) to exist, and four patterns for external caching. I’ve found that database people tend not to care much about external caches, leaving that to application developers. But there can be good reasons to store high-read, low-write data in caches, reducing some of the strain on those expensive database servers.
Comments closedAaron Bertrand starts the count:
Query performance can fluctuate over time, and it is not necessarily due to a change to the query itself (or to the application code that calls it). Users often ask why a query suddenly got slower even though they haven’t published any changes to the application and the underlying data hasn’t changed drastically. This article points out some other reasons – and there are many – that a query might be slower today than it was 10 minutes ago, two weeks ago, or last summer.
Read on for a bulleted list of reasons. Of course, it would be extremely challenging to create a comprehensive list—for example, in the Same Plan section, in addition to there being more data, changes in the statistical distribution of data can cause performance profiles to change over time. But this is a really good starting point.
Comments closed