Press "Enter" to skip to content

Category: Administration

Thoughts on Cloud Monitoring Solutions

Published 2025-01-23 by Kevin Feasel

Mika Sutinen takes a look at built-in ways to monitor SQL Server databases in the three major cloud providers:

Monitoring SQL Server databases is one of the main responsibilities of DBAs, both in on-premises and on the public cloud platforms. Continuing from my previous post, where I reviewed the various options of SQL Server PaaS offerings from major hyperscalers, I’ll now focus on the monitoring solutions they provide.

While I always prefer commercial tools that come packed with features, it’s not the only way to go. Whether you’re using AWS, Azure, or GCP, each platform offers unique tools and features to help you keep an eye on your managed SQL Server database services.

Read on for information about what’s available in each.

Leave a Comment

Repairing a SQL Server Instance

Published 2025-01-22 by Kevin Feasel

Vlad Drumea fixes an installation problem:

I’ve needed to run the repair process in the following cases:

  • An instance or some of its components become corrupted or missing after OS patching.
    One of the most common examples I’ve ran into is SQL Server Configuration Manager being gone from the machine.
    Not to be confused with database corruption.
  • A failed or cancelled SQL Server instance upgrade or patching.
  • SQL Server failing to uninstall.
    From what I’ve seen, this tends to be a side-effect of the first scenario in this list going undetected.

Read on to see what you need to have and how to perform the task.

Leave a Comment

Understanding the SQL Server Version Store

Published 2025-01-20 by Kevin Feasel

Haripriya Naidu digs into tempdb:

This article discusses when version store starts generating rows, when cleanup takes place, and how TempDB space gets occupied due to this activity.

I’ve hardly used PerfMon much in the past, nor was I a fan of it, just like many of you. But, when I used it a few times to determine how many data files needed to be added to TempDB while resolving contention, it helped me make a clear decision on the number of files needed. Since then, I’ve become a fan of PerfMon.
Although I don’t use it daily, I don’t hesitate to turn to it when I think it might help me understand things better.

Coming back to our topic of version store in this article, I resorted to PerfMon counters because visually watching the lines go up and down when an update is performed on a table helped me better understand how the version generation and cleanup works.

Click through for a demonstration of the process, including relevant PerfMon counters and the type of information you can get directly from SQL Server.

Leave a Comment

CPU and Memory Configuration for MySQL

Published 2025-01-20 by Kevin Feasel

Chisom Kanu continues a series on MySQL:

In the first part of the series article, we introduced MySQL Shell as a tool for managing and optimizing MySQL configurations. We discussed how to install the Shell, connect to the MySQL server, and modify basic configuration parameters. Now, we’re into performance optimization, focusing specifically on memory and CPU configurations. These two components are important because they directly impact how efficiently your database processes queries, handles connections, and stores data in memory. In this article, we will look at techniques using MySQL Shell to help you optimize both memory and CPU usage to ensure smooth and fast database performance.

Read on to learn more.

Leave a Comment

SELECT Queries Triggering INSERT/UPDATE/DELETE Audits in SQL Server

Published 2025-01-17 by Kevin Feasel

Sean Gallardy digs into an interesting problem:

Quite the interesting problem was brought to me by a friend, in that they had a database level audit setup and were having `select` queries trigger audits that the object had updates run on them. The question became, how is this possible as a select query doesn’t update anything. Let’s start with a quick setup and original output.

Read on for the solution, as well as a much more common place in which you’d find this issue.

Leave a Comment

Point-in-Time Restoration for Azure SQL Managed Instances

Published 2025-01-17 by Kevin Feasel

Andy Brownsword points and clicks:

One of the benefits which comes with a Managed Instance is having backups taken care of for you. That also includes restores. Particularly useful is a one-click (ish) restore for a specific point in time.

Restoring a database is as easy as creating a new database, as its part of the same workflow.

Read on to see how it works, as well as one limitation around existing databases.

Leave a Comment

The Power of Consistency

Published 2025-01-16 by Kevin Feasel

Kevin Wilkie doesn’t like having four answers to the same problem:

I began reviewing the first SQL Server. Backups were done via a maintenance plan. No worries there. Many places rely on maintenance plans to do their backups each day.

On the second SQL Server, I found no maintenance plans. What I did find though, was a SSIS package creating backups each night for all of the databases on the server.

On the third SQL Server, I found an older version of Ola Hallengren’s Scripts.

And it goes on from there. Kevin makes a good point around how having a consistent solution is important, especially in troubleshooting scenarios. It’s easier to resolve problems if there’s a consistent pattern versus having to remember that, oh, yeah, this is the instance we tried using this one solution, but it didn’t work so we went with something else on another instance.

Leave a Comment

Always On Lease Timeout Monitoring

Published 2025-01-10 by Kevin Feasel

Yvonne Vanslageren gives us one more thing to check:

SQL Server Always On Availability Groups are a robust solution for achieving high availability and disaster recovery for SQL Server databases. However, simply configuring them is not enough—you also need a solid monitoring strategy to ensure data integrity and system reliability. One key aspect of this monitoring process is keeping an eye on lease timeouts, which can signal larger issues and help prevent potentially catastrophic split-brain scenarios.

In this post, we’ll walk through the various health checks available for Always On Availability Groups, discuss how lease timeouts work, and explore practical methods for monitoring and troubleshooting.

Read on to learn more about the lease timeout concept, as well as where you can get this information and further recommendations around how to deal with the information.

Leave a Comment

Idle PostgreSQL Transactions and Table Bloat

Published 2025-01-10 by Kevin Feasel

Umair Shahid notes that some tables are feeling a bit bloated:

Yup, you read it right. Idle transactions can cause massive table bloat that the vacuum process may not be able to address. Bloat causes degradation in performance and can keep encroaching disk space with dead tuples. 

This blog delves into how idle transactions cause table bloat, why this is problematic, and practical strategies to avoid it.

Read on to understand how this can be and what you can do about it. And do check out the comments for a quick explanation of why connection pooling doesn’t exhibit this same problem.

Leave a Comment

Reading a SQL Server XML Deadlock Report

Published 2025-01-09 by Kevin Feasel

Stephen Planck reads a report:

SQL Server includes an Extended Events session called system_health, which runs by default and, among other things, captures information about deadlocks as they occur. When two or more sessions block each other in such a way that no progress can be made (a deadlock), SQL Server chooses one session as the “victim,” rolls back its transaction, and frees resources so other sessions can continue. By reviewing the deadlock report in the system_health session’s XML output, you can see precisely why the deadlock happened and identify which queries or procedures were involved.

Below is a walkthrough of how to interpret a sample XML deadlock report, followed by a brief note on how to access this output.

Read on for that walkthrough.

Leave a Comment