Press "Enter" to skip to content

Curated SQL Posts

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

SELECT FOR UPDATE in Postgres

Published 2024-06-24 by Kevin Feasel

Semab Tariq notes some syntax to assist with performing updates in a MVCC world:

In critical environments like banking, healthcare, and online retail, ensuring safe data modifications is crucial to prevent data corruption and maintain system integrity. PostgreSQL offers a robust solution for this with its row-level locking mechanism, which ensures that the data being modified is protected from concurrent changes. One key feature of PostgreSQL is the SELECT FOR UPDATE clause, which locks the selected rows against concurrent updates. In this blog, we will explore how to implement the SELECT FOR UPDATE clause in PostgreSQL and discuss its real-world use cases.

Read on to learn more about how it works.

Comments closed

Practical healthyR.ts Examples

Published 2024-06-21 by Kevin Feasel

Steven Sanderson provides some examples:

Today I am going to go over some quick yet practical examples of ways that you can use the healthyR.ts package. This package is designed to help you analyze time series data in a more efficient and effective manner.

Let’s just jump right into it!

Read on for a few common time series activities, such as testing for stationarity, extracting tends from noise, and performing lagged correlation.

Comments closed

Performing a Ping Sweep in Powershell

Published 2024-06-21 by Kevin Feasel

Vlad Drumea goes poking around:

This is a brief post containing a a piece of code that I use to do a ping sweep and resolve host names in PowerShell on a /24 subnet.

A /24 subnet refers to the last octet (segment of numbers) in an IP, and it ranges from 1 to 254.
This means that if you provide 100.100.100 to the $FirstThreeOctets variable, you’ll end up pinging every IP between 100.100.100.1 and 100.100.100.254.

This is where I say “Hey, go check out nmap.” I also say “Hey, don’t install nmap on your work machine unless you have explicit approval, so that you don’t get an unexpected visit from security.” Which is something I saw once and decided that wouldn’t be the life for me. But seriously, nmap is an extremely powerful network discovery tool.

Comments closed

Understanding Worktables in SQL Server

Published 2024-06-21 by Kevin Feasel

Steve Stedman takes a peek at tempdb:

A worktable in SQL Server is a temporary structure that the SQL Server Database Engine uses to process certain types of queries. These tables are not explicitly created by users but are generated by SQL Server internally to handle specific operations that cannot be managed directly within memory. Worktables are stored in the tempdb database and are crucial for facilitating complex query execution plans.

Read on for examples of when SQL Server will use worktables and some good ideas when you spot worktables in the wild. They’re not inherently bad, but there are some performance problems you could experience around them.

Comments closed

Don’t Enable TRUSTWORTHY on SQL Server

Published 2024-06-21 by Kevin Feasel

Jeff Iannucci shares good advice:

If you have ever used our free tool to check SQL Server security, you may have seen the check for the “TRUSTWORTHY database owned by sysadmin” show up as one of the highest of priority items, requiring action. When we started reviewing the security permissions and configurations for our clients’ instances, we didn’t expect to find it very often since TRUSTWORTHY database setting is off by default.

Unfortunately, this has been discovered with some frequency, and when combined with a few other common practices, it presents a tremendous vulnerability to escalate privileges for both authorized users and hackers.

Read on to learn more about this. And to supplement, I will once again link Solomon Rutzky’s outstanding guide on the topic.

Comments closed