Press "Enter" to skip to content

Category: Administration

ABORT_QUERY_EXECUTION in SQL Server 2025

Joey D’Antoni pulls out the big guns:

However, SQL Server 2025 gives us a bigger hammer (DBAs love hammers). Building on top of the query store hints feature that was added in SQL Server 2022, ABORT_QUERY_EXECUTION simply blocks the exection of known problematic queries.

Read on to see how this works. Note that it will prevent the query from running at all, ever. As a result, if your main concern is “Hey, don’t run this during the busiest hours of the data for this database,” this particular hint is overkill.

Leave a Comment

Building 9’s with PostgreSQL High Availability Features

Semab Tariq explains some of our options:

When you are running mission-critical applications, like online banking, healthcare systems, or global e-commerce platforms, every second of downtime can cost millions and damage your business reputation. That’s why many customers aim for four-nines (99.99%) or five-nines (99.999%) availability for their applications

In this post, we will walk through what those nines really mean and, more importantly, which PostgreSQL cluster setup will get you there.

Read on to see what you can do to get to each 9, as well as some unexpected risks to keep in mind along the way. And, of course, each rung up move up the ladder will generally cost you more money and administrative effort.

Leave a Comment

Thoughts on Shrinking Databases

Chad Callihan lays out some thoughts:

Unlike your lawn, which benefits from a regular mowing to stay healthy and looking nice, a database isn’t meant to be regularly shrunk just to keep its size under control.

Read on for Chad’s take, which is one I agree with wholeheartedly. There are specific circumstances that merit shrinking a database. The main use case is when you have a significant level change: you’ve archived or deleted a bunch of data. There are very few other valid use cases, especially when you consider that databases typically have positive growth.

Leave a Comment

Oracle Password-Related Profile Settings

David Fitzjarrell takes a look at some settings:

Passwords expire, and, depending upon how various profiles are configured, accounts are either locked or provided a grace period during which the old password can be changed. In any recent enterprise password verification functions are provided to police new passwords to ensure a modicum of security. Let’s dig into what Oracle provides to assist in password security.

Basic elements of password security that Oracle provides start with the profile; listed below are the associated resources:

Read on for the available options you can set on a per-profile basis.

Comments closed

DBAs and Data Access

Brent Ozar wraps up a survey:

Last week, I asked if your database administrators could read all of the data in all databases. The results (which may be different from this post, because I’m writing the post ahead of time and the poll is still open):

In a lot of cases, this doesn’t really matter much. In places where it does matter (for example, reading protected health information or critical financial data), there should be controls in place. I’ve always been on the side of this issue that says that yes, you do need to be able to trust your administrators at the end of the day, because somebody’s going to need a way to get to that data in case of company emergency. But as a company grows and there are additional opportunities for division of labor and specialization, you do open up the possibility of stronger controls, proper auditing, limiting certain data access to privileged accounts, and consequences for violating the rules.

Comments closed

Column Expansion and Compression

Ed Pollack goes big or goes home:

One of the most hassle-prone changes that can be made in a SQL Server database is to widen an existing column. Depending on the column’s use and data type, this process may involve hundreds of schema changes, app downtime, and a mountain of risk-prone code to review and deploy. 

This article dives into a fun (and interesting!) strategy for widening fixed-width columns in SQL Server, to reduce downtime, risk, and runtime at the time when a column’s data type needs to be changed. 

If you have ever suffered through a lengthy INT to BIGINT release, then this guide is for you! 

Read on for a step by step guide on expanding a column with minimum downtime, as well as a quick description of row- and page-level compression and how you can use those to ensure you’re using as few bytes as necessary when storing a smaller number in a larger data type.

Comments closed

Measuring Delay in Availability Groups

Matt Gantz wants to see how far behind we are:

Availability Groups are useful for creating Highly Available (HA) database systems, but that doesn’t mean they are entirely immune to performance problems. In busy systems, limitations in the infrastructure can introduce replication lag that is severe enough to affect database performance in ways that aren’t immediately obvious.

In SQL Server Availability Groups, the relevant difference between synchronous and asynchronous replicas comes down to how and when a transaction on the primary server is considered “committed”: Although it is easy to measure the lag between asynchronous replicas by using the dashboard in SSMS or by querying the DMVs (Dynamic Management Views), it takes more work to find the latency between synchronous replicas. This article explains how to measure that latency using internal performance counters, offering a simple technique for monitoring the cost of synchronous replication.

Click through to read the whole thing.

Comments closed

Post-Install Configuration Tips for SQL Server

Kevin Hill shares some suggestions:

The SQL Server installer has gotten better: tempdb configuration, MAXDOP, and even max memory can now be configured during setup.

But don’t be fooled: there’s still a post-install checklist that can make or break your environment over time. If you’ve ever inherited a server that “just ran” for years and started getting slower over time you’ve likely seen what happens when this list gets ignored.

These are not in any particular order, but some do require a restart of the server or the SQL Server Engine service to take effect:

Click through for several tips. Most of them I agree without reservation. For a couple of them, like backup compression, there are specific circumstances in which I’d recommend against turning it on, namely when working with storage subsystems that deduplicate your data automatically. In that case, you might want to think harder about backup compression (or backup encryption, for that matter) before turning it on.

Comments closed

Managing TempDB Growth with Resource Governor

Haripriya Naidu digs into what’s new in SQL Server’s Resource Governor:

In this article, we’ll focus on how to control TempDB datafile growth using Resource Governor (RG).

  • RG is disabled by default and available only on Enterprise edition.
  • Until SQL Server 2022, RG could only manage user databases.
  • Starting with SQL Server 2025, RG can now manage TempDB as well.

Click through for the demo and additional information.

Comments closed

Stolen Memory in SQL Server

Chad Callihan is overdrawn at the memory bank:

When you specify the max amount of memory that SQL Server can use, you may think that’s all the memory to be used for the buffer pool. That’s not the case. SQL Server will use memory not only for the buffer pool but also in tasks such as sorts, memory grants, and other internal tasks. These tasks can swoop in and “steal” memory from the buffer pool, causing more work for SQL Server.

Click through for some notes on the topic, as well as your reminder that “128 GB for the buffer pool” in Standard Edition doesn’t mean “128 GB total memory usage for SQL Server.”

Comments closed