Press "Enter" to skip to content

Category: Administration

Automating Azure SQL DB Maintenance Tasks

Published 2023-06-09 by Kevin Feasel

Tracy Boggiano reminds us that we still need to administer Azure SQL DB databases:

I’ve been using Azure SQL Database for quite some while and have set up it in many various ways to run Ola’s Index Optimize and Statistics Updates on them.  All of these have seemed way too complicated probably because I was setting them up once, not again for several more months or a year.  Well with my new job, I have over 20 subscriptions with various Azure SQL Servers in them so it was time to streamline at least knowing what I was doing.  No matter what I googled on the Internet I never did find one source that walked me step by step on each thing I needed to know to set this up.  So hopefully this will cover everything.

Click through for the step-by-step process.

Comments closed

Updating Database Mail Settings via SP

Published 2023-06-08 by Kevin Feasel

Chad Callihan doesn’t have time for the UI:

If you need to make changes to multiple servers, you may want to avoid the GUI approach and all of the clicks that come with it. In that case, msdb contains a stored procedure called sysmail_update_account_sp that might be a more efficient approach. Let’s take a quick look at sysmail_update_account_sp and what it can do for you.

Read on to see how the procedure works and what you can do.

Comments closed

Killing Multiple YARN Applications at Once

Published 2023-06-07 by Kevin Feasel

The Big Data in Real World team doesn’t have time to mess around:

If you work with Apache Hadoop, you may find yourself needing to kill multiple YARN applications at once. While you can kill them one by one using the yarn application -kill command, this can be a tedious and time-consuming process. Fortunately, there is a faster way to kill multiple YARN applications at once using the yarn application command in combination with awk.

Click through to see how. I will say, though, remembering some of these sed+grep+awk solutions I’ve written in the past makes me happy that Powershell is object-based…

Comments closed

Three-Node Postgres HD Cluster with pg_cirrus

Published 2023-06-07 by Kevin Feasel

Salman Ahmed wants to be highly available:

We are thrilled to announce the release of pg_cirrus! First of all, you might be wondering what “cirrus” means. The term refers to the thin and wispy clouds that are often seen at high altitudes.

pg_cirrus is a simple and automated solution to deploy highly available 3-node PostgreSQL clusters with auto failover. It is built using Ansible and to perform auto failover and load balancing we are using pgpool.

Read on to see how it works. It’s also licensed under GPLv3, so it’s not only highly available but also freely available.

Comments closed

Enabling Postgres Auditing

Published 2023-06-05 by Kevin Feasel

Athar Ishteyaque enables an extension:

The PostgreSQL Audit Extension (or pgaudit) provides detailed session and/or object audit logging via the standard logging facility provided by PostgreSQL.

The goal of a PostgreSQL audit is to provide the tools needed to produce audit logs. These logs are often required to pass certain government, financial, or ISO certification audits.

I am kind of curious what the performance impact of this extension is.

Comments closed

Data Inconsistency in Postgres HA Clusters

Published 2023-06-01 by Kevin Feasel

Umair Shahid gives us an overview:

While PostgreSQL is known for its robustness, scalability, and reliability, data inconsistency can occur in PostgreSQL clusters, which can cause issues and impact the overall performance of the system. In this blog, we’ll define data inconsistency in PostgreSQL clusters, discuss the challenges it poses, its causes, and provide some tips on how to prevent and resolve it if it occurs.

Click through for the article.

Comments closed

MVCC and Vacuuming in Postgres

Published 2023-06-01 by Kevin Feasel

Ryan Booz explains one area where Postgres’s implementation differs from most other vendors:

All relational databases handle transaction isolation in some way, typically with an implementation of Multi-version Concurrency Control (MVCC). Plain ‘ol, mainline SQL Server uses a form of MVCC, but all older rows (currently retained for ongoing transactions) are stored in TempDB. Oracle and MySQL also do something similar, storing (essentially) diffs of the modified data outside of the table that is merged at runtime for ongoing transactions that still need to see the older data.

Among these databases, PostgreSQL stands alone in the specific way MVCC is implemented. Rather than storing some form of the older data outside of the current table for transactions to query/merge/etc. at runtime, PostgreSQL always creates the newly modified row in-table alongside the existing, older versions that are still needed for running transactions. Yes, every UPDATE creates a new row of data in the table, even if you just change one column.

Read on to understand some of the implications of this and how it affects the way we manage databases.

Comments closed

Keeping VLF Counts Low (Enough)

Published 2023-05-29 by Kevin Feasel

Eitan Blumin has a script for us:

In this ultimate guide, learn how to fix high VLF counts and shrink transaction logs in SQL Server to improve performance. Discover best practices for managing transaction log sizes and VLFs, and get your hands on a robust script to help you out. Boost your SQL Server’s performance and optimize your database administration with these tips.

Click through for the guide, and for a script Eitan has put together.

Comments closed

Holding and Pruning WhoIsActive Results

Published 2023-05-25 by Kevin Feasel

Andrea Allred has a script for us:

Last month I talked about how I started collecting data from sp_whoisactive. The long term goal was to analyze and tune those long running processes that are sometimes hard to quantify in Query Store. We had started collecting the data in a table (whoisactiveLog), but wanted to make a simple table that our Engineers could refer to and find the long running processes. We also wanted to archive off the whoisactiveLog into another table and save that for 70 days in case it was needed for further research on the tuning of processes. Each night, we have a break in our processes where we can do this maintenance so we decided we would empty the whoisactiveLog table completely.

There’s also a check for session length in there.

Comments closed