Press "Enter" to skip to content

Category: Administration

Analyzing Postgres Logs with pgbadger

Published 2023-07-10 by Kevin Feasel

Anthony Nowocien takes us through a useful tool:

This week, #PGSQLPhriday is hosted by Alicja Kucharczyk. Every month, one community member proposes a new subject to this monthly blogging event and let the world (or your family/friends/neighbors if you prefer) know all about pgBadger. It’s a tool to analyze your PostgreSQL logs and present you a nice web report.

If you like some history, it has been developed by Gilles Darold since more than 11 years, as v1.0 came along on June the 10th in 2012. At this time, pgfouine was the main log analyzer and the complete Perl rewrite was greatly performance influenced. In V4, it started to have its current look, by embarking the Bootstrap library and fonts.

Click through to see what pgbadger does and an example of how it makes log analysis understandable.

Comments closed

Impact of and Limitations to Parameter Tuning in Postgres

Published 2023-07-07 by Kevin Feasel

Henrietta Dombrovskaya wraps up a series on PostgreSQL parameters:

In this first blog, we didn’t provide any examples of the practical impact of parameters tuning on performance. Indeed, it is challenging to model such an impact on the training database. 

In this blog, we will segue from discussing PostgreSQL system parameters best practices to other ways of performance tuning. Moreover, we will demonstrate that essential database performance tuning goes beyond choosing the appropriate parameters settings.

Click through for that post.

Comments closed

model_msdb and model_replicatedmaster Databases

Published 2023-07-05 by Kevin Feasel

Sean Gallardy says hands off:

There was a question that came up on the MCM mailing list about what are these files: model_msdbdata.mdf, model_msdblog.ldf, model_replicatedmaster.mdf, model_replicatedmaster.ldf? It was pointed out that the files don’t show up in the typical DMVs and seem to not really be used at the moment.

These are the databases used as the basis for the system databases in contained availability groups. Definitely don’t touch them, just leave them be, even if you aren’t using availability groups (let alone contained).

Click through for more information on how you can find them and another warning not to mess with them.

Comments closed

Last Page Insert Contention in SQL Server

Published 2023-07-05 by Kevin Feasel

Eitan Blumin spots a wild Latch Convoy:

The “Last Page Insert Contention” in SQL Server, also known as “Latch Convoy Problem“, also known as “PageLatchEx Contention” is one of those extremely rare use cases that are very difficult to see in real-world scenarios.

Evidently, it was impactful enough that Microsoft implemented a solution for this problem back in SQL Server 2019 in the form of the new OPTIMIZE_FOR_SEQUENTIAL_KEY index option, which reportedly fixes it.

Click through to learn more about a scenario in which Eitan saw this in the wild. In fairness, I’m not sure I’d do any better at realizing that this was a last page insert contention problem.

Comments closed

Protecting Kubernetes Services

Published 2023-06-30 by Kevin Feasel

Boemo Mmopelwa gives us an idea of Kubernetes service types and how to secure them:

A Kubernetes service is a logical abstraction that enables communication between different components in Kubernetes. Services provide a consistent way to access and communicate with the application’s underlying components, regardless of where those components are located.

In Kubernetes the default type is ClusterIP. Services abstract a group of pods with the same functions. Services expose pods and clusters. Services are crucial for connecting the backend and front-end of your applications.

This is different from your containerized applications that you can deploy on Kubernetes

Comments closed

Balancing Governance and Collaboration with Fabric

Published 2023-06-29 by Kevin Feasel

Marc Lelijveld makes it sound like I can’t just say “No!” to everything as a Microsfot Fabric administrator:

Frequently, I am approached by curious individuals who inquire about my job and how I contribute to the success of our customers, especially since I am not directly involved in building solutions for each and every one of them. These questions have made me realize that it might be interesting to share insights into my role as a Fabric Administrator, or as some may refer to it, a Power BI Administrator.

In this blog post, I aim to shed light on the essence of daily activities of a Fabric Administrator, the meaningful conversations people in this role engage in, and the additional value they bring to the table.

Read on to see what people like Marc do all day.

Comments closed

When Statistics Updates Happen

Published 2023-06-28 by Kevin Feasel

Matthew McGiffen gives us the numbers:

SQL Server has had the ability to automatically update statistics since version 7.0. Nonetheless for a long part of my career working with SQL Server, whenever a performance issue raised its head everyone’s knee-jerk response would be “Update Statistics!” In most cases though the people shouting that didn’t really understand what the “Statistics” were, or what mechanisms might already be in place for keeping them up to date.

Of course SQL Server isn’t perfect and sometimes it is helpful for human intelligence to intervene. But to provide intelligent intervention one has to understand how things work.

Read on to learn what triggers automatic stats updates in various versions of SQL Server.

Comments closed

Loading Multiple Extended Events Files in SQL Server

Published 2023-06-27 by Kevin Feasel

Jose Manuel Jurado Diaz reviews the tapes:

As the volume of data grows, SQL Server creates multiple extended event files to store the captured information efficiently. These files are usually saved in a designated target folder. However, when it comes to loading and analyzing these files, administrators often face the challenge of dealing with multiple files individually. Manually loading each file can be time-consuming and inefficient, especially when dealing with a large number of extended event files.

Read on to see which function you can use to read multiple Extended Events files and how it works.

Comments closed

Loading Multiple Audit Log Files in Azure SQL DB

Published 2023-06-26 by Kevin Feasel

Jose Manuel Jurado Diaz can’t stop at one:

In Azure SQL Database, the auditing feature enables you to track and monitor database activities, providing valuable insights into the actions performed on your database. One of the key components of auditing is the audit log files, which store the recorded data.

However, when dealing with a large number of audit log files stored in a blob storage container, loading them into Azure SQL Database can be a challenging task.

This article explores a workaround using the sys.fn_get_audit_file function to load multiple audit log files without being able to define a pattern such as *.xel.

Note that, even though the example is for Azure SQL Database, the function is built into SQL Server, SQL Managed Instance, and Synapse dedicated SQL pools as well and works the same way.

Comments closed