Press "Enter" to skip to content

Category: Administration

CPU Monitoring in SQL Server with Datadog

Published 2024-11-22 by Kevin Feasel

Kendra Little has a recommendation:

What makes me a raving fan is the flexibility of Datadog’s notebooks and dashboards, combined with the ability to create all sorts of custom metrics and monitors. There are always things in SQL Server monitoring packs that I have strong opinions about. Datadog lets me take what I want, build what I need that isn’t contained in that, and ignore the rest. For a team that has the budget to afford Datadog paired with dedicated database staff with the time and resources to do this work, this can be a great fit.

One of the weirdest and worst parts of the Datadog SQL Server monitoring tooling, though, is how it handles wait stats. In my opinion, it’s a case of someone reinventing a wheel that didn’t need to be reinvented, and then not documenting what they did clearly (at least not in a way I can find).

Two of the most confusing Datadog “waits” are labeled “CPU” and “Waiting on CPU”. I opened a support ticket with Datadog a while back to ask what these are, because I couldn’t find any way they correspond to actual wait stats in SQL Server. I learned they aren’t wait stats at all. In fact, I think you should largely ignore them. Here’s why.

Read on for the full story.

Comments closed

Making a SQL Server Table Read-Only

Published 2024-11-21 by Kevin Feasel

Chad Callihan has no need to write:

There are situations when you need to set a database to read-only, but what do you do when you need a single table to be read-only? It’s not as straightforward as setting the entire database read-only, but it can still be done.

Let’s take a look at the steps necessary to set both a database and a single table to read-only.

Another option is to put the table into a filegroup that you make read-only, especially if this is a permanent change. That way, you can rebuild the table’s indexes with a fill factor of 100% and reduce the number of pages. In addition, you can back up that read-only filegroup separately from active filegroups and restore filegroups individually. Now, if your read-only filegroup is a large percentage of total data in the database, you don’t need to back it up nearly as frequently because the data isn’t changing.

Comments closed

Metadata-Only NOT NULL Column Insertion

Published 2024-11-20 by Kevin Feasel

Andy Brownsword has the need for speed:

When adding a new column and wanting to default the value for existing records, it used to be a painful task. As of SQL Server 2012 that became much easier.

But nobody told me, until Simon casually mentioned it in conversation recently. I had to see it for myself, so I thought I’d share for those who weren’t aware.

Read on to see how. I rarely self-promote in other people’s blog posts (hush, person who knows all the times I’ve done it), but I do have a talk on the topic of near-zero downtime database deployment strategies which includes this and quite a few other notes on what you can do without blocking others. For these sorts of changes, what you’re looking for is asynchronous processing and a Sch-M (schema modification) lock at the very end, such as when rebuilding an index with ONLINE = ON in Enterprise Edition. Alternatively, look for a Sch-M lock only on a metadata table and not the actual data. Andy’s post is an example of the latter.

Comments closed

Renaming a Database in SQL Server

Published 2024-11-14 by Kevin Feasel

Steve Jones asks, what’s in a name?:

I had someone ask me how to rename a SQL Server database recently. They were doing some development work and wanted to rename databases to test an application. I thought I remembered, but in this post, I show I learned something.

Read on for the answer, as well as some notes about it. One additional thing I’d point out is that renaming the database doesn’t rename the underlying files.

Comments closed

The Challenge of Importing Items into a Fabric Workspace

Published 2024-11-13 by Kevin Feasel

Marc Lelijveld performs an airing of grievances:

Obviously, you don’t want to start every solution from scratch. Therefore, it might be beneficial to kick-start your new solution by just importing components you already developed at earlier stages. Recently, I wanted to import a notebook to a Fabric workspace but was a bit confused. In this blog, I will further elaborate on the confusion and show how, in the end, I successfully imported the notebook to the workspace.

Read on for a story of pain.

Comments closed

Scenarios Leading to Autovacuum in Postgres

Published 2024-11-11 by Kevin Feasel

Semab Tariq covers autovacuum:

However, one side effect of MVCC is the creation of dead tuples—old versions of data rows that are no longer needed but still occupy space. 

Dead tuples also lead to a phenomenon known as table bloat, which refers to the excessive unused space in a table caused by dead tuples that haven’t been cleaned up, resulting in inefficient storage and reduced performance

To address the issues of dead tuples and table bloat, autovacuum comes into play. It’s an automatic process designed to clean up these dead tuples and maintain optimal database performance.

Read on for a list of scenarios that can trigger autovacuum.

Comments closed

Configuring Azure Database Watcher

Published 2024-11-08 by Kevin Feasel

Rod Edwards configures Azure Database Watcher to watch databases in Azure:

First off, at the time of writing, this is still in Preview, and is only for Azure SQL PaaS offerings, namely Azure SQL DB and SQL Managed Instance, so if you’re out of luck if you’re using SQL on VM. Expect this to be added at some point in future, its number 2 on the published roadmap.

Preview or GA…the long and short of it is that it allows collection of performance AND config data into a central datastore for ALL of your SQL MI and Azure DB estate. With all of the data in one place, then dashboards are connected to here for easier estate-wide visualisations.

Read on for a step-by-step guide on configuring it. But also pay attention to Rod’s note near the end that troubleshooting setup is a pain—there aren’t many useful logs that show exactly why it isn’t working.

Comments closed

Verification of PostgreSQL Checksums with WAL-G

Published 2024-11-08 by Kevin Feasel

Marat Bogatyrev talks checksums with us:

A key aspect of maintaining backup integrity is understanding data checksums. Without proper checksum validation, detecting data corruption becomes virtually impossible. Therefore, we will start with The Importance of Data Checksums.

Read on to learn more about how PostgreSQL, in particular, deals with checksums. SQL Server has similar functionality for pages and backup files and it’s worth knowing about and enabling checksum capabilities in both database platforms.

Comments closed

Configuring SQL Server Alerts in SQLMonitor

Published 2024-11-07 by Kevin Feasel

Ajay Dwivedi has an update to SQLMonitor:

If you are responsible for managing & monitoring SQLServers, then it is mandatory to have monitoring and alerting for critical issues of your SQL Servers.

For this purpose, open source SQLMonitor now has built-in Alert Engine. With the capability of this alert engine, alerts can be sent to Slack & Email to various teams. The following are some advantages –

Read on to see some of those capabilities and how to set things up.

Comments closed