Press "Enter" to skip to content

Category: Administration

Breaking the World with auto_explain

Published 2022-12-21 by Kevin Feasel

Ryan Lambert gets a lot of explanation:

Postgres has a handy module called auto_explain. The auto_explain module lives up to its name: it runs EXPLAIN automatically for you. The intent for this module is to automatically provide information useful for troubleshooting about your slow queries as they happen. This post outlines a pitfall I recently discovered with auto_explain. Luckily for us, it’s an easy thing to avoid.

I discovered this by running CREATE EXTENSION postgis; and watching it run for quite a while before failing with an out of disk space error. That is not my typical experience with a simple CREATE EXTENSION command!

Read on to learn what happened and how you can prevent making a similar mistake.

Comments closed

Checking if Cross-Database Ownership Chaining is On

Published 2022-12-16 by Kevin Feasel

Tom Collins performs a check:

Cross-database ownership chaining is a SQL Server  security feature allowing database users  access to other database objects hosted on the same SQL server instance, in the case where database  users don’t have access granted explicitly

Tom shows us whether it is on as well as how to enable it. I’d recommend not enabling it at all and using module signing instead.

Comments closed

Identifying Rows in sp_wait_for_database_copy)_sync

Published 2022-12-14 by Kevin Feasel

Jose Manuel Jurado Diaz troubleshoots a problem:

As you could see in this public documentation Auto-failover groups overview & best practices – Azure SQL Database | Microsoft Learn about sp_wait_for_database_copy_sync “sp_wait_for_database_copy_sync prevents data loss after geo-failover for specific transactions, but does not guarantee full synchronization for read access. The delay caused by a sp_wait_for_database_copy_sync procedure call can be significant and depends on the size of the not yet transmitted transaction log on the primary at the time of the call.”

Our customer asked about several scenarios to understand this behaviour and also, verify if there is possible to identify the rows that have not been synced. For this, I developed a POC to test it:

Read on to see what you’d need to do.

Comments closed

Finding “Stolen” CPU on SQL Server

Published 2022-12-14 by Kevin Feasel

Ajay Dwiveldi has a public service announcement for us:

The above panel is showing any SQLServer that needs DBA attention. But there is this server highlighted in the above screenshot showing a significant gap b/w OS CPU and SQL CPU. Any gap of over 15-20% b/w OS & SQL CPU consumption is something that should be investigated. This was an indicator that there is some other non sqlservr process that is consuming high CPU on the host.

Friends don’t let friends run anything other than SQL Server on a SQL Server machine.

Comments closed

Disabling Inline Database Log Flushes

Published 2022-12-13 by Kevin Feasel

Bob Dorr talks about a trace flag:

SQL Server optimizes database log file flush requests, performing these flush requests inline on the active worker. Certain patterns of log record activity may encounter increased spinlock contention while performing the log cache block flush activities.

Trace flag -T8904 (startup only trace flag) disables inline log flush, limiting the contention possibility from many workers to the subset of background LogWriter workers.  When the trace flag is enabled, the worker adding log records mark the log cache block to be flushed and a background LogWriter worker performs the flush activity.

Read on to learn more, including a call-out to Curated SQL favorite Lonny Niederstadt.

Comments closed

Enterprise-Level Backups in MySQL

Published 2022-12-05 by Kevin Feasel

Lukas Vileikis continues a series on backups with MySQL:

MySQL Enterprise Backup is a known tool for everyone inside of the world of MySQL, no matter if people dealing with the tool are enterprise (business) minded or not. The tool can be considered the flagship of MySQL’s enterprise-level offerings: it comes as a part of its Enterprise tier and costs thousands to attain: is it worth your money, and perhaps more importantly, your time? We will figure that out in this blog.

Read on for Lukas’s thoughts.

Comments closed

Where Extended Events Go by Default

Published 2022-12-01 by Kevin Feasel

Tom Zika is curious:

Have you ever wondered where the .xel file is saved when you create a new Extended Event session and don’t specify the full path (just the file name)?

Like so: [image removed because you should go to Tom’s site and see it, ed.]

Well, so did I and here’s what I’ve found out.

Click through to learn where these files end up if you don’t specify anything.

Comments closed

Just Enough Administration and Granting Access to SQL Server

Published 2022-11-28 by Kevin Feasel

Andrew Pruski tries out a tool:

We’ve all been there as DBAs…people requesting access to the servers that we look after to be able to view certain things.

I’ve always got, well, twitchy with giving access to servers tbh…but what if we could completely restrict what users could do via powershell?

Enter Just Enough Administration. With JEA we can grant remote access via powershell sessions to servers and limit what users can do.

Click through to see how it works.

Comments closed

Backup Options for MySQL

Published 2022-11-21 by Kevin Feasel

Lukas Vileikis continues a series on backing up MySQL. Part 2 involves Percona XtraBackup:

As already stated above, Percona XtraBackup is one of the primary offerings for MySQL & Percona database administrators developed by Percona. The tool is an open-source backup utility that does not lock our databases during the backup processes it performs. Percona says that their tool can provide automatic verification of backups that have been taken, offer fast dumping and restore times, and above all, it’s supported by their award-winning consulting services helping us make sure that our data and its backups are in safe hands by day and by night.

Part 3 covers mysqlpump:

mysqlpump is a backup utility that is used via the command-line interface. The tool is very similar to mysqldump in that it provides us with the ability to take logical backups, but also different at the same time – the goal of mysqlpump is to be an extendable, parallel-supporting replacement of mysqldump. In their blog from 2015, MySQL team said that one of the primary aims of introducing mysqlpump was not be forced to implement legacy functionality that is provided by mysqldump.

Read on to see how both of these work.

Comments closed