A Fine Slice Of SQL Server
Andrea Gnemmi looks at a pair of extensions:
We all know the importance of monitoring our RDBMS to ensure the performance and availability. Are there any tools that provide functionality to better to monitor PostgreSQL databases? In this article we will explore pg_stat_activity and pg_locks.
Click through for an overview of these two extensions and some of the functionality available in them.
Comments closedMarc Lelijveld provides a public service announcement:
When working with Notebooks in Microsoft Fabric, exporting and reusing them across environments or tenants might seem like a harmless, even convenient, task. Whether you’re sharing a template with a colleague, moving assets between workspaces, or contributing to the community — the last thing you’d expect is to accidentally include data along with your code.
But that’s exactly what can happen.
For people who have worked with Jupyter notebooks in the past, this is a fairly obvious result. But if you aren’t familiar with the platform, that idea may seem weird. Marc does provide some options for exporting notebook contents, and you can also clear the cell contents before exporting.
Comments closedUmair Shahid preps for an update:
When multiple transactions need to modify the same rows, ensuring data consistency can become tricky. A single wrong approach to locking can lead to suboptimal performance or even bring your application to a standstill as numerous transactions block one another. One tool in PostgreSQL’s arsenal to handle concurrency is SELECT FOR UPDATE. It allows you to lock specific rows before updating them, preventing other transactions from modifying those rows until your transaction completes.
In this blog, we will dive deep into SELECT FOR UPDATE in PostgreSQL. We will explore how it helps in reducing contention, avoiding deadlocks, and ultimately boosting performance when dealing with highly concurrent applications.
Click through to understand how this works and also some notes on when to use it and when not to use it.
Comments closedRichard Swinbank deals with a totally-not-deprecated platform:
The underlying problem here appears to be that the Spark connector is simply not supported in v3.4. There’s very little I can find to officially confirm or deny this, but an answer to this question on Microsoft Q&A backs this up. The answer also suggests a few options, including:
- downgrade to Spark 3.3 – this isn’t an option because it’s end-of-life
- migrate to Fabric – long term this is good idea, but it’s not a quick fix for this problem.
- use alternative data access methods, e.g. using the Azure Data Lake Storage Gen2 connector.
In this article, I take a look at option (3).
Click through for Richard’s workaround.
Comments closedChad Callihan deals with non-existent data:
Handling NULL values can be a challenge and may lead to unexpected query results when mixed in with non-NULL values. Thankfully, there are a few SQL Server functions devoted to handling NULL values. Let’s look at two of them. The first, NULLIF, will help you to return a NULL value. The second, ISNULL, will help you with an alternative to a NULL value if you need another value in its place.
For the longest time, I strongly preferred COALESCE() over ISNULL() because of how it can handle multiple scenarios, as well as it being ANSI SQL syntax. But it turns out that, if you do only have two things to compare, ISNULL() is a little bit faster.
Kevin Chant keeps us up to date:
Microsoft Fabric Variable libraries were announced during the keynote at the Microsoft Fabric community conference. Which caused some excitement.
Variable libraries are an upcoming preview item that will enable developers to manage configurations within a workspace. Reducing the need for custom development work after deployments.
You will be able to achieve this by creating a Variable library in each workspace. From there, configure the individual variables for that workspace. Improving your CI/CD experience.
Click through to see more about that, as well as several other interesting announcements.
Comments closedFederico Campoli deals with a lot of RAM:
Nowadays it’s not uncommon to deal with machines with hundreds of GB of RAM.
Abundant memory can give PostgreSQL a massive performance boost. However things work slightly different than you may expect.
Let’s find out!
Click through for a dive into how PostgreSQL handles memory management.
Comments closedReza Rad does a bit of a match:
Have you ever wanted to match two tables together but not on exact matches, but also on a threshold of similarity? if your answer to this question is yes, then this feature is built for you. Let’s explore in details how the fuzzy matching works in Power BI. To learn more about Power BI, read Power BI from Rookie to Rock Star.
As always, Reza has a video and a blog post for us, explaining how the fuzzy matching process works and some of the knobs you can control. In the comments, Reza even digs up the specific algorithm that Microsoft uses for fuzzy matching.
Comments closedMarco Russo and Alberto Ferrari show that tracking weekdays is like an onion:
Computing time intelligence calculations in DAX is rather simple. However, as soon as the requirements are not trivial, the complexity of formulas skyrockets, and it is necessary to have a very good understanding of several details about DAX to obtain a good formula. In this article, we show a simple requirement: the need to maintain a filter on weekdays while computing time intelligence. As you are about to read, it will require several complex steps despite being a simple requirement; but let us start by clarifying what we want to obtain and what a filter-preserving column is.
Click through for the full article.
Comments closedThere is a SET command in SQL Server that changes how much data is returned from some fields. This short post shows what I learned about the SET TEXTSIZE command.
I’m pretty sure that, if I’ve ever used this command before, it was by accident. It’s kind of a neat concept in how it can limit the result size of larger blocks of text, but I think it’s too limiting to be a good solution save for some very specific circumstances.
Comments closed