Press "Enter" to skip to content

Month: February 2024

An Overview of Join Elimination

Published 2024-02-02 by Kevin Feasel

Chad Callihan doesn’t need solo rows; he needs team players:

SQL Server can be pretty smart when it comes to avoiding work it’s not required to do. One instance of this is the concept of join elimination. Join elimination occurs when queries involving joins may only need to check one table instead of two (or more) based on foreign key constraints.

Let’s take a look at join elimination in action.

Read on for one example of join elimination and one more reason why those foreign key constraints are so useful.

Comments closed

SQL Server Setup Config Files and Per-Version Maintenance

Published 2024-02-02 by Kevin Feasel

Aaron Bertrand diagnoses a problem:

We came across a new error during SQL Server setup that returned zero useful search results:

The setting ‘COMMFABRICPORT’ specified is not recognized.

I did not find too many mentions of this argument at all, never mind in that specific phrase – though now that I wrote this, it might start returning this post. Most mentions came from probably a single sample copy of ConfigurationFile.ini offered on a blog post from the ~2017 timeframe.

Read on to learn more about this, as well as short-term and long-term fixes for managing your installation config files.

Comments closed

Parallelism Options in Powershell

Published 2024-02-02 by Kevin Feasel

Rod Edwards enumerates the list:

This is one that I should have learnt way earlier than I actually did if i’m being completely honest. One of my colleagues wrote a cool function for multithreading, and although this isn’t it, it did made me look into how it all worked.

Some of the techniques can be quite complicated, especially for a DBA who’s not as experienced with Powershell yet (and a ForEach loop just works right?) so I thought i’d post about the simplier methods that can be employed, and tweaks to get most of the benefits, without the complexity.

So, lets jump straight into it.

Rod does focus on Powershell jobs and thread jobs, so check out how those work. A while back, I had a comment here talking about the various options though Rod has definitely put a lot more effort into this task.

Comments closed

The State of CI/CD in Power BI

Published 2024-02-02 by Kevin Feasel

Paul Turley violates Betteridge’s Law of Headlines:

The answer – finally – is a resounding YES! It has been a long road and the path to implementing Continuous Integration & Delivery (CI/CD) in all of its forms for Power BI is still a journey, but the capabilities are now a reality. This broad category includes a range of features and capabilities related to managing project files with version control and the ability to share and collaborate with other development team members. Additionally, we also have the ability to assign individual components of a solution to different team members who then check-out files and develop features, check them back in and merge changes into a feature branch. In enterprise-class projects, having these capabilities enables true Development Operations or DevOps: a process for managing iterative development, testing and deployment with automated builds, using a central process management tool. Implementing full-fledged DevOps is a big lift for most Business Intelligence professionals but the core concept of code version control is essential for any durable project.

Read on for Paul’s thoughts on the importance of source control and how you can pull this off.

Comments closed

Extracting the Month from a Date with R

Published 2024-02-01 by Kevin Feasel

Steven Sanderson asks what month it is:

Greetings fellow R enthusiasts! Today, we’re diving into a fundamental task: extracting the month from a date in R. Whether you’re new to R or a seasoned pro, understanding how to manipulate dates is essential. We’ll explore two popular methods: using base R and the powerful lubridate package. So, let’s roll up our sleeves and get started!

Read on for several examples across two solution spaces.

Comments closed

Index Counts and Write Speeds

Published 2024-02-01 by Kevin Feasel

Erik Darling tells the truth:

Before I go on about how adding an index can fix your blocking and deadlocking problems, let me warn you about how indexes can cause you problems.

Without fail, whenever I suggest adding indexes to clients, someone gets real worried about write speeds.

Like, the server can be burning down, spurting blood everywhere, and cursing our mothers in ancient Aramaic, but no, let’s stop and worry about write speeds going up by a few milliseconds.

Fully agreed: the marginal cost of an index is rarely noticeable and the marginal benefit of a well-designed index serving an existing customer base is significantly higher than its marginal cost. However, when I click that plus sign next to the Indexes folder and SSMS starts spinning because it needs to enumerate the dozens and dozens of indexes on that table, it’s a bad sign. Especially when 90% of them have “_dta_” in the name.

Click through for more thoughts on indexing, scans, and transactions.

Comments closed

Fixing the Power BI Error “Something Is Wrong with One or More Fields”

Published 2024-02-01 by Kevin Feasel

Kurt Buhler broke it and now needs to buy it:

The “grey box of death” in Power BI is unfortunately a common sight for anyone creating reports. When you see this grey error box, it means that the visual can’t obtain a valid result. If that error says “Something is wrong with one or more fields”, then there’s several possible reasons for this:

Click through for those reasons, as well as how to fix the problem. Kurt shows this both in video form and written out, so check it out.

Comments closed

New Features in Azure SQL MI Instance Pools

Published 2024-02-01 by Kevin Feasel

Djordje Marinkovic shows off what’s new:

When migrating small SQL Server instances to Azure it is often the case that a single SQL Managed Instance turns out to be overkill in terms of size and, consequently, cost. The oversizing problem can happen whenever very small instances are required, for example when an ISV company builds a multi-tenant app requiring a small SQL MI instance for each customer. In such cases the smallest size (4-vCores) for a single SQL MI can still turn out to be too large and too expensive for the given use case. This is where SQL MI pools (“instance pools”) deliver great value.

Click through for more information on instance pools, as well as new features for instance pools.

Comments closed