Press "Enter" to skip to content

Category: Administration

Dealing with Thousands of Databases

Andy Levy has some Q&A about dealing with large numbers of databases on a single server. Part one:

What was the most difficult challenge faced initially with a large environment and how does that challenge relate to now?

For me personally, it was just getting a handle on how to deal with this many databases because I didn’t “grow up” with the system. I walked into an environment with a lot of established tools and procedures for performing tasks and had to learn how those all fit together while also not breaking anything. You don’t want to be the person who walks in the door, says “why are you doing things like this, you should be doing it this other way” and then falls victim to hubris. If something seems unusual, there’s probably a reason for that and you need to understand the “why” before trying to change anything.

Part 2 is also up:

How large is the team that manages the databases? Is the knowledge shared and everyone can work on everything or do these people fill niches?

There are two of us. We each have a few specialties but we aren’t “territorial” and we try to share as much as possible. If we aren’t both directly involved in a given project, we keep each other in the loop as it progresses.

Stay tuned for part 3.

Comments closed

Rolling Windows Upgrades with AGs + WSFC

Allan Hirt shows how you can combine Availability Groups with Windows Server Failover Clusters and upgrade the operating system version while keeping your SQL Servers running:

The configuration for a cluster rolling upgrade allows for mixed Windows Server versions to coexist in the same WSFC. This is NOT a deployment method. It is an upgrade method. DO NOT use this for normal use. Unfortunately, Microsoft did not put a time limit on how long you can run in this condition, so you could be stupid and do something like have a mixed Windows Server 2012 R2/2016 WSFC. Fire, ready, aim. The WSFC knows about this and you’ll see a warning with an Event ID of 1548.

Read on for a summary of what Allan has learned in doing this.

Comments closed

Fixing Windows Power Settings

Jeff Iannucci takes us through power settings within T-SQL:

Well, not exactly, but it’s definitely like that. The default Power Setting is “Balanced” which means during periods of lower activity the clock speeds of your CPUs are reduced to conserve power and save your battery.

Apparently all Windows installations think they are on laptops. SPOILER ALERT: your database servers are probably not laptops.

Jeff has a T-SQL script to fix this. Unfortunately, it won’t fix the other power-based performance killer: power settings in BIOS.

Comments closed

Optimizing for Sequential Keys

Dennes Torres gives us a reminder of what the world was like before a new feature in SQL Server 2019:

Once upon a time a SQL Server version that hadn’t row locks. The minimal level of lock was page lock, every time you want to lock a record, an entire page was locked.

At that time we were between the devil and the deep sea: if we choose a clustered index with an ascending key we would create what was called a Hot Spot, all the records would be inserted on the same page, creating a bottleneck. On the other hand, if we create a clustered index with a non-ascending key, we would suffer from index fragmentation and page splits, having huge admin trouble to find out the correct fill factor for each index in order to support the period between the re-index job without too many page splits.

Dennes covers the specific case which this feature intends to cover and how we got there.

Comments closed

Filtering Stored Procedure Results

Thomas Rushton shows how to filter any stored procedure’s result set and uses sp_who as an example:

sp_who – useful – up to a point. Particularly when the server is busy, and you’re looking for something specific (eg to see if certain processes are out of a database before running an update)

If the server is busy – don’t you wish there was a way to run something like

sp_who WHERE dbname = 'foo'

Yeah. Unfortunately, it doesn’t work like that.

That doesn’t work, but Thomas shows you what does.

Comments closed

CPU Usage DMV in SQL Server on Linux

Tejas Shah announces an improvement to sys.dm_os_ring_buffers in SQL Server 2019 RC1 on Linux:

Sys.dm_os_ring_buffers DMV has been a key DMV used for monitoring SQL Server by built-in tools as well as third party monitoring utilities. When SQL Server 2017 was released on Linux, unfortunately this DMV did not return correct CPU usage information by SQL Server process. SQL Server team is glad to announce that the starting with SQL Server 2019 release candidate, the sys.dm_os_ring_buffers DMV returns SQL CPU utilization correctly. This improvement should benefit the SQL Server monitoring ecosystem on Linux by providing a way to monitor SQL Server CPU usage and enable decision making to engage corrective action if required.

This brings it in line with what we have on Windows.

Comments closed

SQL Server Database Recovery Models

John McCormack goes into the three database recovery models available in SQL Server:

This post is about database recovery models for SQL Server databases. Having the correct recovery model for a database is crucial in terms of your backup and restore strategy for the database. It also defines if you need to do maintenance of the transaction log or if you can leave this task to SQL Server. Let’s look at the various recovery models and how they work.

Click through for a description of each, including recommendations of when to choose each.

Comments closed

Accelerated Database Recovery and Filegroups

Randolph West shows a change to Accelerated Database Recovery in SQL Server 2019 CTP 3.2 and later:

ADR makes use of a per-database version store, instead of putting everything in the transaction log and TempDB. In most cases, the payoff more than makes up for valuable transaction log and TempDB storage. In my testing, enabling ADR requires around 10% additional storage in your database file.

The reason for this post, however, is to mention that SQL Server 2019 CTP 3.2 introduced the ability to choose which filegroup you want to use for the version store, which will help with performance.

Read on to see how you enable this.

Comments closed

Change Tracking in SQL Server

Tim Weigel covers the basics of change tracking in SQL Server:

There aren’t a lot of parameters here. You can set change tracking on or off, you can specify your retention period, and you can specify whether to enable auto-cleanup or not.

For the retention period, you have the choice of DAYS, HOURS, or MINUTES. If you don’t specify retention, SQL Server will configure change tracking with a retention period of 2 days. If you provide a number but don’t specify the interval, SQL Server will default to days. The minimum retention period is 1 minute.

This post specifically covers enabling change tracking at the database level.

Comments closed