Press "Enter" to skip to content

Category: Administration

Postgres Internals: Database Clusters, Databases, and Tables

Published 2024-03-18 by Kevin Feasel

Semab Tariq begins a new series:

A database cluster is a collection of multiple databases managed by a single PostgreSQL server. It can be referred to as a data/base directory.

A database is a collection of database objects. Whereas a database object is a data structure used to store objects such as tables, views, indexes, extensions, Sequences functions, etc. In simple words, anything that we can create or store within a database is a database object

Read on to learn more about how Postgres lays out database files and tablespaces.

Comments closed

Postgres and NUMA

Published 2024-03-14 by Kevin Feasel

Annie Ghazali follows up on a Chris Travers webinar:

Q1. At what point we need to focus on ensuring huge_pages in PostgreSQL?

There are a couple of factors here. The first is, that if you’re able to show that you have multiple NUMA domains, it will almost always be a win performance-wise. But it becomes critical at the point where you start seeing that the checkpointer is running at 100 percent CPU load, and none of your queries are running at 100 percent CPU load, especially if you don’t have a lot of IO weight. That’s a really good indication that you’ve hit a point where it’s now a heavy bottleneck, and that’s a point where it’s starting to become something where you’re going to see a very large win out of it. 

Read on to see this full answer, as well as answers to questions around why you might not want to disable NUMA support and what NUMA does to swap space recommendations.

Comments closed

Proper Logging of SQL Agent Job Outputs

Published 2024-03-12 by Kevin Feasel

Aaron Bertrand hits on an annoyance of mine:

If you haven’t migrated your workloads to a managed database platform yet, you’re probably still relying on SQL Server Agent for various maintenance and other scheduled tasks. Most of the time, these processes just work. But when it’s time to troubleshoot, it can be cumbersome to get to the root of some problems. In this post, I’ll share some ideas to help you minimize the level of annoyance and tedium when you have to figure out what went wrong with the execution of a job.

Recently, I was investigating a job that ran a stored procedure against multiple databases in sequence, and ultimately failed. Here was the output of the job step as seen in Management Studio’s View Job History dialog:

Read on for a few tips to help with SQL Agent job logging.

Comments closed

Dropping Extra tempdb Files

Published 2024-03-12 by Kevin Feasel

Vlad Drumea doesn’t want to restart SQL Server:

This is a brief post containing a script you can use to delete extra tempdb data files and avoid the “file not empty” (Msg 5042) error.

I’ve occasionally ran into situations where an instance is configured with more than the recommended number of tempdb data files.

If you’re dealing with more than a couple of extra files it can get annoying, especially on a busy instance.

Read on for the script and how you can use it on busy and not-so-busy instances.

Comments closed

Reading Azure SQL Audit Logs from Azure Storage

Published 2024-03-08 by Kevin Feasel

Matt Changchien covers a strange scenario:

When you read an Azure SQL Database audit log from Azure Storage using sys.fn_get_audit_file, you might encounter a situation where the audit log appears non-empty, but the query still returns an empty result. This discrepancy can be puzzling, especially when the official documentation doesn’t explicitly mention any limitations or requirements for the sys.fn_get_audit_file system function.

In this post, I will shed light on these limitations and demonstrate them to provide clarity.

Read on to see when this might happen and what you can do about it.

Comments closed

Restoring a Tablespace using Barman on Windows

Published 2024-03-08 by Kevin Feasel

Semab Tariq restores a database:

I recently had the opportunity to contribute to a customer project, where the objective was to establish a system for PostgreSQL full backups and seamless restoration. Considering Barman’s successful functionality on Linux, we decided to explore its compatibility with Windows. Secondly, no other tool claims to work on Windows to take backups and perform a restore

From official documentation it is mentioned that: 
Backup of a PostgreSQL server on Windows is possible, but it is still experimental because it is not yet part of our continuous integration system.

Click through for the walkthrough.

Comments closed

MySQL: INTO OUTFILE and INTO DUMPFILE

Published 2024-03-08 by Kevin Feasel

Chad Callihan makes a comparison:

I haven’t had a MySQL post for awhile, so it’s time to add some variety to the blog.

There are a couple of different ways to export data with a SELECT query in MySQL: INTO OUTFILE and INTO DUMPFILE. Let’s use the MySQL Sakila sample database and walk through some examples to compare these two options.

Read on to see when you might want to use each of these.

Comments closed

Microsoft Fabric Governance & Administration: Tenant Settings

Published 2024-03-06 by Kevin Feasel

Nicky van Vroenhoven has a pair of posts on Microsoft Fabric administration, specifically around tenant settings. First up is a post on APIs:

Obviously, to use the Get Tenant Settings API you need to have at least Tenant.Read.All permissions, or have the Fabric Administrator role (or higher) in Azure.

There are a few use cases I see for getting these settings exported with this API:

  • Documentation purposes when you have multiple Fabric Administrators
  • Distributing tenant settings to users, and explaining why we (as a team of Administrators/within the Center of Excellence) made certain choices
  • Get notified of the changes in the tenant settings, without having to use Microsoft Defender or M365 Security & Compliance center like mentioned here

Nicky has a follow-up post on visual cues in the Tenant Settings page:

Today I want to talk about a new little addition Microsoft made to the Fabric Admin portal.

This change has actually been here for quite a while now, but I still think it’s worth mentioning because (1) I really like it, and (2) it’s also an important change that the community, and MVP’s in specific, has been requesting for quite some time.

Radhakrishnan Srinivasan and (members of) his team added visual cues to the Admin portal of Fabric.

Check out both posts for good information.

Comments closed

Measuring Write Speeds in SQL Server

Published 2024-03-05 by Kevin Feasel

Vlad Drumea performs a test:

In this post I cover a script I’ve put together for measuring storage write speeds in SQL Server, namely against database data files.

This is meant to help get an idea of how the underlying storage performs when SQL Server is writing 1GB of data to a database.

At this point, you might be asking yourself: “Why not use CrystalDiskMark instead?”.
The answer is simple: you might not always be able to install/run additional software in an environment. Even more so if you work with external customers or you’re a consultant. It’s a lot simpler to ask a customer to run a script and send you the output, than it is to ask them to install and run some 3rd party software.

Click through for the script, what it does, and how to run it, as well as a note on limitations and example based on three drives.

Comments closed