Press "Enter" to skip to content

Month: January 2025

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

Azure VM Constrained CPU and SQL Server Licensing

Published 2025-01-10 by Kevin Feasel

Rod Edwards works to save some money:

Our SQL servers have specific loads, patterns and resource requirements. We’ve monitored them, we know what they are inside and out. You know that x number of CPUs, and y GB or RAM means that your DBs happily sweat the hardware, but are sized to perfection to make sure it has just enough resource to complete what it needs to do. The negotiations with your Infra team in command of the VM resources were tough, but you’re not greedy… you appreciate that other VMs may require resource as well…

Now you have to migrate to Azure, and SQL on Azure VM is your only option. Great, loads of different types/flavours of VMs out there, one of them must be in the the goldilocks perfect porridge zone right? Right?

Licensing around this gets a little weird. Basically, Rod is absolutely right that an E64-16, for example, would require 16 cores of SQL Server, and that can save a good bit of scratch. For the OS, however, you’re paying for 64 cores, so keep that in mind while you’re rolling in a bed of the cash you’ve saved on SQL Server licensing.

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

Dealing with Duplicate Data via ROW_NUMBER()

Published 2025-01-10 by Kevin Feasel

Andy Brownsword removes the duplicates:

Data quality and consistency is key to the services we support and solutions we deliver. A gremlin which can undermine that is duplicate data. Let’s start the new year dealing with duplicate data and having a good clear-out.

For our example we’ll consider an Order Product table which contains an OrderID and ProductID, and the combination of these should be unique. Other fields for the duplicate records may differ so we may want to be selective about which records are removed.

This is where I get on my high horse and complain about laziness in data modeling, a very common problem. This takes nothing away from Andy’s post, which is a good method for solving a problem that has gotten out of hand. But if you know that some combination of attributes is unique, add a unique key constraint or a unique non-clustered index right then and there. Doing so will prevent improper duplicate data from ever being an issue. If you don’t know that some combination of attributes must be unique, discuss this with the business side in a way that makes sense for them. Yes, there’s always the risk that you’ll have a conversation later like, “Oh, it turns out that this really should be unique,” but in most cases, you can easily sort this kind of thing out up-front and save a lot of time and effort later on.

Leave a Comment

Prevalence Adjustment in Binary Classifiers

Published 2025-01-09 by Kevin Feasel

David Lindelöf deal with an issue in analyzing classification models:

When you run a binary classifier over a population you get an estimate of the proportion of true positives in that population. This is known as the prevalence.

But that estimate is biased, because no classifier is perfect. 

Read on to learn what this means for precision, as well as one technique for tracking prevalence changes over itme.

Leave a Comment

Entity Framework and Default Data Lengths

Published 2025-01-09 by Kevin Feasel

Brent Ozar points out one issue you might run into when using Entity Framework:

Most of the time, I love Entity Framework, and ORMs in general. These tools make it easier for companies to ship applications. Are the apps perfect? Of course not – but they’re good enough to get to market, bring in revenue to pay salaries, and move a company forwards.

However, just like any tool, if you don’t know how to use it, you’re gonna get hurt.

One classic example popped up again last month with a client who’d used EF Core to design their database for them. The developers just had to say which columns were numbers, dates, or strings, and EF Core handled the rest.

Read on for the scenario.

Leave a Comment

Window Function Ranges: UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING

Published 2025-01-09 by Kevin Feasel

Chad Callihan engages the limit breaker:

I’m familiar with using the OVER clause and don’t think it’s too uncommon to see it used for including row numbers by using ROW_NUMBER() and aggregating data. But even though they’ve been around since SQL Server 2012, I’m not too familiar with using the OVER clause with the UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING to affect the window being queried.

Let’s take a look at a couple of examples using UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING.

Click through for those examples. The default ranges for window functions usually make a lot of sense, but it’s good to understand your options for frames: ROWS vs RANGE, as well as the frame values (UNBOUNDED PRECEDING, {N} PRECEDING, CURRENT ROW, {N} FOLLOWING, and UNBOUNDED FOLLOWING).

Leave a Comment