Press "Enter" to skip to content

Curated SQL Posts

Power BI Query Memory Limit

Published 2024-06-25 by Kevin Feasel

Chris Webb continues a series:

Continuing my series on Power BI memory errors (see part 1, part 2 and part 3), in this post I’ll look at the query memory limit which controls the amount of memory that an individual query can consume. This is a subject which I’ve talked about in a few blog posts in the past: hitting this limit is one of the reasons you’ll see the “This visual has exceeded the available resources” error which I blogged about here and and here. There’s also some official documentation here which is pretty good.

Read on to learn more about what settings you have available for it and a few more tips.

Comments closed

A SQL Saturday Survey

Published 2024-06-25 by Kevin Feasel

Steve Jones works with Steve Rezhener:

Steve Rezhener put together a survey for what topics you’d like to learn about. A few others, including myself, gave him feedback and he’s published this for people to use. It was intended for SQL Saturday organizers and speakers, but it can work well for anyone producing information. I’ve created some shortlinks at SQL Saturday that you can use to take the survey and see the results.

I’m promoting this because when I initially got the e-mail for it, I thought it was spam. With enough responses, this may help organizers focus in on specific topics for events, at least to some extent.

Comments closed

Explaining Power BI and Fabric Capacity Pricing

Published 2024-06-25 by Kevin Feasel

Marc Lelijveld breaks out the green eyeshade:

P-SKUs, A-SKUs, EM-SKUs and now we also have F-SKUs… all these different capacities that are out there today each have their own specifics. Lately, I’ve been in a lot of conversations around Fabric capacities. There seems to be some unclarity around what you pay for in the end and how it compares to Power BI Premium capacities. Therefore, I thought, maybe this is the right time to write it down – besides the Microsoft documentation that is already out there.

In this blog I will elaborate on differences in purchasing, billing and buying the capacities. I will not deep dive in capacity metrics or how capacity units are consumed.

There’s a lot of good information in the article, especially if you’re looking to price out Microsoft Fabric in your organization.

Comments closed

adutil Now Available for RHEL 9 and Ubuntu 22.04

Published 2024-06-25 by Kevin Feasel

Amit Khandelwal has good news for us:

We’re thrilled to share that adutil, the Active Directory Utility for SQL Server, is now officially supported on RHEL 9 and Ubuntu 22.04. If you’ve been waiting for this, your patience has paid off! Let’s dive into the details.

I had released a video on Active Directory in SQL Server on Linux that included a workaround to get adutil going on Ubuntu 22.04. I’m glad that part of the video is now obsolete.

Comments closed

OLE Automation Security

Published 2024-06-24 by Kevin Feasel

Jeff Iannucci talks about OLE Automation:

It’s rare to see OLE Automation procedures enabled on a SQL Server instance. Most folks aren’t using these specially system procedure because they didn’t have a need to use them, have a compliance requirement that prohibits using them, or they tried using them and had adverse results. As the Microsoft documentation on OLE Automation stored procedures notes:

“Don’t directly or indirectly call Automation procedures from any SQL Server common language runtime (CLR) objects. Doing so can cause SQL Server to crash unexpectedly.”

But if this setting is enabled in any of your SQL Server instances, you need to consider it similarly to the xp_cmdshell setting. By that I mean: this is probably not a problem, but you should try to figure out why the setting is enabled, and how its associated system stored procedures are being used.

I find this perfectly reasonable. There’s a lot of fear around xp_cmdshell, when in practice, it doesn’t affect security at all unless you completely mess things up and start granting rights to non-sysadmins.

Comments closed

Performance Tuning XML Operations in SQL Server

Published 2024-06-24 by Kevin Feasel

Ed Pollack does a bit of tuning:

SQL Server provides a variety of ways to tune XML so that it provides consistent performance, consumes less space, all while ensuring efficient access to critical data.

At its core, the metadata-styled XML format runs counter to the data that SQL Server is optimized to manage. Therefore, additional features were added to SQL Server over time that allowed for XML data to be indexed and compressed.

While these features are critical for managing XML data as it becomes large, it is important to remember what XML is intended for and why it is (loosely) structured as it is. Many data professionals have used shortcuts when XML was small, such as storing and analyzing it in string format, only to be forced to reckon with performance challenges when scanning large strings become agonizingly slow.

Read on for the full article.

Comments closed

Enumerations and Ordering in Postgres

Published 2024-06-24 by Kevin Feasel

Christoph Schiessl sorts things out:

Custom ENUM types in PostgreSQL are an excellent tool for enforcing certain database constraints, but you must be careful if you use SELECT queries and want to ORDER BY these columns. Recently, I had to fix a bug whose root cause was a misunderstanding of this behavior. It’s just a contrived example, but imagine a table of people with their marital status, which is implemented as a custom ENUM type.

Read on to learn more about the misunderstanding and some of the unexpected trickiness involved in getting a good query plan.

Comments closed

Parallel Index Builds in SQL Server

Published 2024-06-24 by Kevin Feasel

Paul White delves into history:

SQL Server doesn’t support parallel modifications to a b-tree index.

That might sound surprising. After all, you can certainly write to the same b-tree index from multiple sessions concurrently. For example, two sessions can happily write alternating odd and even numbers to the same integer b-tree index. So long as both sessions execute on different schedulers and take row locks, there will be no blocking and you’ll get true concurrency.

No, what I mean is: A single session can’t write to a b-tree index using more than one thread. No parallel plan modifications of a b-tree index, in other words. It’s a bit like the lack of parallel backward ordered scans. There’s no reason it couldn’t be implemented, but it hasn’t been so far.

Click through for a link to the full article. Or click the link I just added, your choice.

Comments closed

Power BI Theme Color Choices

Published 2024-06-24 by Kevin Feasel

Meagan Longoria explains some of what you get with themes in Power BI:

Power BI reports have a theme that specifies the default colors, fonts, and visual styles. In Power BI Desktop, you can choose to use a built-in theme, start with a built-in theme and customize it, or create your own theme.

Creating your own theme involves specifying formatting options in a JSON file and importing it into your report. This post will focus on the theme colors, but there are lots of other options that can be specified in a theme, including structural colors, fonts, and page and visual formatting options.

Read on to learn more about the three primary sets of colors you can specify.

Comments closed