Press "Enter" to skip to content

Category: Administration

Tracking How Many Row Versions an RCSI Query Reads

Louis Tochon works with a new bit of information:

The Table line looks like a normal scan of 330 pages. The Object line says the read walked through 220,000 row versions, 22 per row, because another session had updated these rows 22 times in a transaction it had not committed yet. The same read took about 117 ms of CPU, against 3 ms when nothing was pending.

The Object line is new. It comes from the version store read metrics Dimitri Furman announced on the Azure SQL blog last week, which also appear in the actual plan XML returned by the server (SSMS 22.12.0 drops them, both in the graphical plan and in Show Execution Plan XML; copy the XML cell from SET STATISTICS XML ON to see them), in four extended events, in sys.dm_exec_query_stats and its procedure, trigger and function counterparts, and in sys.dm_exec_requests and sys.dm_exec_sessions (Microsoft documentation). For now they exist in Azure SQL Database and SQL database in Microsoft Fabric; Managed Instance and the next SQL Server version are announced to follow.

When I recommend RCSI, the usual worry is the open transaction someone forgot in SSMS. These metrics let me check what it actually costs the readers.

The article looks to be AI-generated, but it does show off a new bit of functionality that I’d be happy to see spread beyond Azure SQL Database.

Leave a Comment

Managing tempdb Activity

Aaron Bertrand has built a solution:

Managing tempdb growth tends to be a very reactive exercise. We size tempdb using a predictive ruleset based on expected workload, database size, concurrency, and other imperfect factors. Then someone does something silly and fills the T: drive. If we catch it in time, we may be able to kill the offending session or add space to prevent the workload from dying. Then we tell them not to do it again; but they will, of course, so we expand the drive again and hope this time it’s big enough. But it rarely is. It’s like a baseball game that’s tied in the 5,662nd inning. Each team keeps scoring a single run in every new inning; the game will never end.

Our existing “drive full” alarms start going bonkers as soon as a drive is more than 90% full. We have a dedicated drive for tempdb data, and we pre-allocate the data files to just under 90% of the available space. This prevents any growth events while they’re using tempdb within the bounds of its current size. But when we fill the space already allocated to the data files, they grow. After one or two growths, we have crossed the threshold for drive space alerts, and it probably won’t be long before the volume fills and the workload grinds to a halt.

Click through for the solution scripts and Aaron’s explanation of it all.

Leave a Comment

Standing up Active Directory and a Windows Cluster

Dualcore DBA is doing some sysadmin work. First up is setting up a domain controller:

In this post, we’ll walk through the process of how I set up an Active Directory (AD) in my home lab. I know, this blog is normally database focused, so why are we looking at Active Directory? Well, yes, it isn’t strictly database related. However, where there is an enterprise SQL Server estate, there is normally Active Directory. In the next few posts, we’ll be looking at Availability Groups, which, whilst configurable without AD, generally are run in AD environments. Also, it isn’t really a great deal of work to set up in the home lab. It takes longer to install the Windows server than to actually set up AD, so it’s not really an issue to set it up to have a more realistic lab environment.

I am using Windows Server 2022 and have set up a specific VM for this purpose which is fairly modest with 2 CPU cores and 4GB of memory.

After that, try on creating a cluster without a domain setup:

This post documents the process of setting up a non-domain Windows Server Failover Cluster, officially known as a Workgroup Cluster. The process is similar to Setting Up a Windows Cluster, however there is a slight difference in this scenario which is that the nodes in the cluster are not part of an Active Directory domain. This process can be used to set up a SQL Server Availability Group where the nodes are not in an Active Directory environment.

And creating a cluster inside of a domain:

In this post, we’ll walk through setting up a Windows Server Failover Cluster (WSFC). This is a requirement of SQL Server Availability Groups, something which we’ll be using on this blog and so I will be setting one up that can be used in the home lab I use to write my blogs. A Windows Failover Cluster is a group of independent servers that work together, continuously monitoring each other, so that if one server fails or goes offline, another can automatically take over its workload with minimal interruption to users. WSFC handles the “which node should be primary and what do we do if it isn’t” on behalf of a SQL Server Availability Group.

Leave a Comment

Installing SQL Server and Creating a Cluster on a Linux VM Host

Lee Markup continues a series on building a SQL Server and Windows network on top of a Linux host:

All four VMs run on KVM/libvirt, using the SQLLab virtual network and subdomain.example.com we set up.

In this post, we will walk through an automated install of SQL Server and creating the Windows cluster needed to build an Availability Group.

Lee does not simply click next-next-next for installation.

Leave a Comment

Reseeding a Single Database in an Availability Group

John Morehouse solves a problem:

Recently, I found myself dealing with an Availability Group (AG) where I needed to remove a single database from one secondary replica and then add it back. Nothing particularly unusual about that, right? Except I wanted to use automatic seeding to rebuild that database without affecting the other databases already synchronized on the replica. That led me down an interesting rabbit hole involving how SQL Server handles automatic seeding, what happens when you remove a secondary database from an AG, and how you can rebuild just one database without disrupting the others.

Let’s take a look!

Read on to see how you can do this without disrupting the existing databases that are in good shape.

Leave a Comment

Installing SQL Server on Linux

Dualcore DBA performs an installation:

SQL Server on Linux – one of those things that you can’t believe actually exists – two worlds you never thought would collide. For years, Microsoft and Linux were very much apart, until the announcement in 2015 that Microsoft Loves Linux. SQL Server on Linux was launched in 2017 and whilst I won’t get into the differences between the closed and open source philosophies, being a fan of both Microsoft SQL Server and Linux, I felt like I had to see what SQL Server on Linux was like.

With this in mind, let’s walk through the install process.

I’ve found that I use containerized SQL Server way more often than directly installing it on Linux. But either way, I’m still glad that it’s possible.

Leave a Comment

Preparing a Domain Controller for a Home Lab

Lee Markum continues a series on building a SQL Server 2025 home lab on Linux-based hardware:

In part 1 I described laying the foundation for the lab. This post will describe building the Windows domain controller and other machines on my Linux Mint host.

My goal was to have a realistic, production-like configuration. As a result, I wanted a domain controller, two SQL Servers in a Windows cluster and an Availability Group.

Looking back through the ChatGpt thread, I apparently started cloning the Windows Server template for the SQL Servers before I made a domain controller. I don’t know why I did that, but I wouldn’t do it that way again. I’ll give you the instructions in a better order.

Please note that domain names (example.com), subdomain names (lab), and IP addresses shown in this article are examples. Some identifying details have been changed, but the configuration and procedures remain the same.

Click through for Lee’s notes.

Leave a Comment

Removing a 4TB Table in PostgreSQL

Radim Marek drops a table:

GitLab has a public story about deleting the wrong thing. In January 2017 an engineer who was trying to restore replication wiped a data directory they believed was on the secondary. It was on the primary. The postmortem they published afterwards is still one of the better things written about a database outage.

At PGConf.EU in Valencia last week a few people from GitLab came to me with the inverse problem. They have one table, 4 TB, on a primary with ten replicas, and they want it gone, on purpose, with as little impact as they can manage. It’s an archival table: nothing writes to it, and nothing is supposed to read it.

Read on for a variety of thoughts around different ways to get to the same end state at differing levels of user impact.

Leave a Comment

Clean Up the Housekeeping Jobs

Aaron Bertrand shares some advice:

When I inspect a new SQL Server instance for the first time, I am often surprised by the number of maintenance and housekeeping jobs. There can be a lot going on, and it’s not uncommon to find several of these jobs scheduled to start at the same time, usually at exactly midnight. This can create compounded resource usage and practically invites the processes to interfere with each other.

For many of these routine tasks, I would much rather have a single job that runs them sequentially. This avoids concurrency issues and gives us a much cleaner view of the job list, current activity, and execution history. And no, I don’t mean putting every maintenance operation into one ginormous job, but there are definitely some consolidation opportunities worth pursuing, particularly for things that run once a day or less.

Click through for a series of examples and what Aaron would do with each.

Comments closed

Administering Power BI at Scale

Heidi Hasting shares some notes:

PPCC26 has wrapped up and I am still buzzing. A huge thank you to everyone who came along to Power BI Administration at Scale: Governance Patterns for Enterprise Deployments with Shabnam Watson and me. I promised I would share the demo code, so here it is.

In this post, I’m going to cover:

  • A quick recap of the session
  • How this builds on my earlier tenant settings post
  • What the demo does and how it works
  • Where to download the notebook and the full kit

Click through for that recap as well as some extra details and tools.

Comments closed