Press "Enter" to skip to content

Month: November 2023

Creating a User in Postgres

Daniel Calbimonte adds a new user two separate ways:

Open PGAdmin and connect to the server where you want to create the new user.

In the Object Browser, expand the Server and go to Login/Group Roles tree, and right-click on the folder. Select Create>Login/Group Role option from the context menu.

In addition to adding a user via PGAdmin, there’s also a script to add users via pgSQL, and that will look a lot more familiar to SQL Server administrators.

Leave a Comment

Fixing a Parallelism Problem Together

Reitse Eskens phones a friend:

In my previous blogpost (Click here to read) I wrote about a query that just wouldn’t go parallel. This sparked some discussion and interest from a few people who were very kind and helpful with their suggestions and even deep dives into the query plans, execution statistics etc.

To make one thing very clear, this blog is 99% their work, only the typing and rephrasing is mine. This also means that mistakes are mine as I’m trying to join the different inputs together into a logical story. So let me introduce you to the heroes, order by first name ascending.

Read on to see what people suggested and the effect that had.

Leave a Comment

Why Batch Mode Sort Spills are Slow

Paul White unravels a mystery:

Batch mode sorting was added to SQL Server in the 2016 release under compatibility level 130. Most of the time, a batch mode sort will be much faster than the row mode equivalent.

This post is about an important exception to this rule, as recently reported by Erik Darling (video).

No doubt you’ll visit both links before reading on, but to summarize, the issue is that batch mode sorts are very slow when they spill—much slower than an equivalent row mode sort.

Read the whole thing. Paul does a great job illuminating us.

Leave a Comment

Comparing Tableau and Power BI

Rob Collie throws down:

On the surface, when comparing Power BI vs Tableau, they appear very similar. Both are business intelligence tools. Both create slick dashboards and offer more than one data visualization tool. In fact, most people can’t distinguish between a dashboard created in Tableau and one created in Power BI.

Each of them has its strengths. However, their core difference lies in their approach to data integration, transformation, and analysis. Now, let’s break this down.

Read on for Rob’s take on the matter.

Leave a Comment

Things to Ignore: SQL Server I/O Affinity

Sean Gallardy recommends you not touch this:

I honestly have no idea how or why people tend to use this configuration option, if you know please drop me a line and let me know or put a comment below, I’m genuinely curious. When I ask people why this is set when I see/find it, I normally get a “well that’s how the last server was” or “I don’t know”. Awesome. I always like to just change random settings for no particular reason. Some days you wake up decide you’re going to go change a bunch of settings on your computer because why not, it’ll be fun.

There probably is a reason, though Sean’s speculation of “so the benchmark scores for SQL Server testing would be higher” is just as likely the cause as anything else. My fallback alternative is “one very large customer threw a lot of money Microsoft’s way to add a setting that works for them but nobody else.” There are a couple of those in the product, too.

Leave a Comment

Differentiating Physical and Logical Reads in SQL Server

Jose 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?

Leave a Comment

Simulating a Bivariate Normal Distribution in R

Steven Sanderson isn’t content with a univariate normal distribution:

Imagine two variables, like height and weight, that exhibit a joint distribution. The bivariate normal distribution captures the relationship between these variables, describing how their values tend to cluster around certain means and how they vary together. It’s like a two-dimensional bell curve, where the peak represents the most likely combination of values for both variables.

Click through to learn a bit more about bivariate normal distributions, including ways to plot one and show its density function.

Leave a Comment

Failure Modes of Sending Pre-Read Materials for Meetings

Alex Velez sends us documents in advance:

This could be a hot take, but I’m not a fan of pre-reads and will respectfully decline most requests to share content before a meeting. 

Before I elaborate on why, let’s start by exploring what pre-reads are, why they often fail, and some more effective alternatives.

I think the viable but difficult alternative is to do what Jeff Bezos did at Amazon: for each meeting, there is a 2-page primer covering all of the relevant context for the meeting. After people are in the room, you distribute the 2-pager and everybody spends 5 minutes reading it first. That serves the intent of the pre-read but there are strict social cues to do the reading, something that does not exist with pre-reads. It also prevents people from going around in circles because they have different subsets of information and don’t realize it.

Of course, this is a challenge to pull off in practice and requires more effort from the standpoint of meeting hosts, but I’m also of the belief that there should be some level of pain involved in scheduling a meeting, as that will cut out many of the “This could have been an e-mail” types of meetings.

Leave a Comment

DAX Time Intelligence with a Fiscal Year Differing from Calendar Year

Olivier Van Steenlandt covers a common case:

Many companies don’t follow the regular Calendar as we know (January 1st – December 31st). They follow their own Financial Calendar (often called Fiscal Calendar) which can start at any time of the year.

Because of this, writing Year-To-Date calculations in DAX for your Tabular Model might seem challenging.

In the step-by-step example, we are working for a company that starts its Financial Year on July 1st.

Read on to see one way to do it. It doesn’t quite solve the problem Olivier brought up, but I’d also make note that having a calendar table with fiscal + calendar year information in it helps remarkably well. It can even handle multiple fiscal year concepts; as an example, a state agency I worked for had a fiscal year on July 1 but the US federal government’s fiscal year begins October 1, so it was just a matter of having StateFiscalYear and FederalFiscalYear columns.

Also, check out Olivier’s new theming, under the Data Cuisine motif.

1 Comment