Press "Enter" to skip to content

Month: January 2024

The Problem with Automatic Plan Correction

Published 2024-01-18 by Kevin Feasel

Kendra Little lays it out:

I’ve written a bit about SQL Server’s Automatic Plan Correction feature before– I have an hour long free course with demos on Automatic Plan Correction here on the site.

Today I’m updating that course with a note: after using Automatic Plan Correction in anger for a good amount of time, I do not recommend enabling the feature. I’ve had it cause too many performance problems, and there are not a ton of options for an administrator when it’s causing those problems.

Kendra is still bullish about the potential of this but has some major issues with the current implementation. Read on to learn more about it.

Comments closed

Handling Orphaned Database Files with dbatools

Published 2024-01-17 by Kevin Feasel

Rod Edwards rounds up the orphans:

This may be an edge case issue, it may not. Or some may not know this is a potentially a thing. For any of the above questions, i’m not sure of the answer. I do know however, that it doesn’t involve morally suspicious fairy tales of any kind, flutes, or pastry products for that matter.

I also know that it’s something that could potentially be robbing disk space across SQL Estates so i’ll talk about it anyway and supply a simple way to fix this in one sweep using the magnificent DBATools.

Rod’s claim is no pastry products, but my counter-argument is that the command probably runs better if someone brings donuts in.

Comments closed

TidyDensity and data.table

Published 2024-01-17 by Kevin Feasel

Steven Sanderson makes use of data.table:

I’m thrilled to announce a major upgrade to the TidyDensity package that’s sure to accelerate your data analysis workflows. We’ve integrated the lightning-fast data.table package for generating tidy distribution data, resulting in a jaw-dropping 30% speed boost.

The data.table package is so much faster than its competition in so many cases, yet I really don’t like its syntax.

Comments closed

NEXT VALUE FOR and ROWCOUNT

Published 2024-01-17 by Kevin Feasel

Jose Manuel Jurado Diaz diagnoses a problem:

In the realm of SQL Server, certain combinations of commands and functions can lead to unexpected conflicts and errors. A notable example is the conflict between the NEXT VALUE FOR function and the ROWCOUNT setting. This article aims to dissect the nature of this error, explaining why it occurs, its implications, and how to effectively capture and analyze it using Extended Events in Azure SQL Database. For example, we got the following error message: Msg 11739, Level 15, State 1, Line 11 – NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET. – NEXT VALUE FOR (Transact-SQL) – SQL Server | Microsoft Learn

Read on for more information, including what the error is telling us and how we can capture cases of that error using extended events.

Comments closed

Granting Users Access to Create Fabric Items

Published 2024-01-17 by Kevin Feasel

Gilbert Quevauvilliers is in a giving mood:

I was recently working with a customer where I was showing them the awesome new features of Microsoft fabric. I then created a workspace and attempted to grant the individual users access to the workspace to create fabric items or workloads.

When the users went into the app workspace with the fabric settings, enabled, the users could not create any workloads.

Read on to see what the problem was and how you can resolve it.

Comments closed

Adding a Service Principal to a Power BI Workspace

Published 2024-01-17 by Kevin Feasel

Marc Lelijveld reads the manual, disagrees with it, and stubbornly sets out to prove it right:

Today, I run into an issue by adding a service principal to a workspace. To my experience so far, this should be straight forward and a matter of adding with the right permissions in the workspace settings. However, for some reason I couldn’t get it to work. In this blog I will elaborate on what caused this issue and which tenant setting influences this.

Click through for the story behind this and the resolution to Marc’s issue.

Comments closed

What ANSI_NULLS Does

Published 2024-01-17 by Kevin Feasel

Chad Callihan answers one of the mysteries of the universe:

Have you ever seen ANSI_NULLS being set to ON or OFF in a script or stored procedure? I’ve seen or heard it mentioned a few times recently and thought it would be worth demonstrating an example of what this setting does.

Read on for the answer, including a truth table. But definitely keep ANSI_NULLS set to ON, as several important bits of functionality in SQL Server expect it that way.

Comments closed

Tuple Syntax in DAX Expressions

Published 2024-01-16 by Kevin Feasel

Marco Russo and Alberto Ferrari build tuples:

The DAX language has a tuple syntax commonly used in table constructors. However, the tuple syntax can also be used whenever you want to describe the combined values for two or more columns – this can be handy when you test the combined values of multiple columns, such as year and month. After reviewing the table constructor syntax, we introduce the tuple syntax and a few examples of where tuples can simplify the DAX code, making it more efficient and easier to read.

Half of the difficulty of working with tuples is pronouncing “tuples.” It’s pronounced “tuples,” not “tuples.”

Comments closed