Press "Enter" to skip to content

Month: June 2024

Execution Plans for Keyset Cursors

Published 2024-06-25 by Kevin Feasel

Hugo Kornelis talks about a cursor I’d never heard of before:

Welcome to plansplaining, part 32, where we once more look at cursors. We already discussed the basics, and looked at static cursors and dynamic cursors. It is now time to cast our gaze upon the keyset cursor. The keyset cursor is sort of in between the static cursor (which presents a snapshot of the data is at was when the cursor was opened and disregards future changes) and the dynamic cursor (that always shows the current data). To be precise, a keyset cursor sees changes made to already existing rows, but does not see new rows that were added after the cursor was opened.

Read on to learn more about it.

Comments closed

Managing the SQL Server Error Log

Published 2024-06-25 by Kevin Feasel

David Seis doesn’t let the error log get out of hand:

SQL Server offers a robust logging mechanism known as the SQL Server Error Log. This log plays a crucial role in troubleshooting, auditing, and ensuring the smooth operation of your SQL Server instances. You can explore the SQL server error log through SQL Server Management Studio in the management folder. There are various tools in the GUI to make navigation easier, as well as stored procedures to enable faster parsing of the data stored.

Read on for a few tips around log management.

Comments closed

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