The case of the Mysterious Azure Storage spikes

Peter Lindholm — Sat, 20 Feb 2021 15:39:15 +0000

Recently I noticed a significant increase in Azure costs on a client project. For this particular client cloud storage is used for virtual machine disks, a few assets and hosting of a set of application. Cloud storage costs is something I've almost thought of as free. Naively.

Over a short period of time storage costs increased more than 300%. Weird. I checked and compared usage over a couple of billing periods. No spikes in user traffic that could explain this.

Fortunately Azure Cost Analysis lets you dive pretty deep on all individually incurred costs. The majority of costs was incurred by 'lrs write operations'. Digging into the 'Total Requests' metric of the storage account I was seeing approx. 1.4M requests per hour.

Drilling further into ingress/egress metrics revealed almost no data moving across the network 🤔

Next step was grouping the different kinds of requests making up those crazy numbers. Only a single operation stood out with millions of operations occurring during a regular day: ChangeNotify.

What changed?

So, troubleshooting 101...what has changed? Going through the latest commit log revealed upgrades to a few Azure Functions projects (sharing storage). The projects was updated to Azure Function v3 runtime and moving from .NET Core 2.1 to .NET Core 3.1. But no code changes.

This gave a few Google'able hints to go for and surely I was not the only one seeing this.

Apparently the culprit seemed to be how IConfiguration was built and used inside individual functions. In the oldest of the functions, before DI in Azure Functions was a thing, I had this approach to accessing the configuration data:

var config = new ConfigurationBuilder()
                .SetBasePath(context.FunctionAppDirectory)
                .AddJsonFile("local.settings.json", 
                    optional: true, 
                    reloadOnChange: true)
                .AddEnvironmentVariables()
                .Build();

In particular this line below was causing the issue:

.AddJsonFile("local.settings.json", optional: true, reloadOnChange: true)

'local.settings.json' is as the name implies meant for local usage/testing scenarios. It is never committed to source control and thus never published to Azure. But even though it is marked as optional, something in the Azure Functions runtime goes crazy because of this "missing" file.

I cleaned up the old functions and allowed for them to use constructor injection of whatever configuration was needed in the functions. This was I completely removed the above code block initialising the config.

Did a few quick tests and deployed to production. Either way the functions would be better off that before. But immediately I saw the transactions count dropping.

Guess when my cleanup was deployed? 😀

Lessons Learned

I have no idea why the increased storage operations was triggered after upgrading to a newer runtime. But my best bet is that the Azure Functions team have had a regression 🤷‍♂️

This issue was a welcome opportunity for me to learn more about the Cost Management part of an Azure Subscription. Going forward monitoring of cost analysis grouped by resources will be Yet Another™️ activity to include in my day-to-day operations.

Switching the offset for a UTC date in SQL Server

Peter Lindholm — Thu, 20 Aug 2020 18:23:49 +0000

I was doing some work recently where I needed to group some data from SQL Server by year, month, week, etc.

The simplified version was something like this:

SELECT  
    COUNT(x),   
    YEAR(CreatedUtc)
FROM    
    dbo.[Order]
GROUP BY
    YEAR(CreatedUtc)

The CreatedUtc data type is datetimeoffset storing zulu datetimes.

Even though I knew I was only looking at data from 2020 I was still seeing 2 rows - one for 2019 and one for 2020.

I tested my date filtering (omitted for brevity in the sample above) and everything was fine. All dates was within the 2020 UTC bounds.

So after digging a bit more I found the offending row resulting in the 2019 group. The date was: ‘2019-12-31 23:56:43.0052080 +00:00’

Thinking of it, it of course makes perfect sense that the built-in YEAR function is not aware of timezones.

YEAR(‘2019-12-31 23:56:43.0052080 +00:00’) will (naturally) return 2019.

We need to switch the offset in the datetimeoffset to the timezone required for the task. In my use-case of a building a report that means switching to CEST/Central European Standard Time.

The YEAR snippet now instead becomes:

YEAR(SWITCHOFFSET(CreatedUtc, DATEPART(TZOFFSET, CreatedUtc AT TIME ZONE 'Central Europe Standard Time')))

The full (still simplified) sample becomes:

`
SELECT  
    COUNT(x),   
    YEAR(SWITCHOFFSET(CreatedUtc, DATEPART(TZOFFSET, CreatedUtc AT TIME ZONE 'Central Europe Standard Time')))
FROM
    dbo.[Order]
GROUP BY
    YEAR(SWITCHOFFSET(CreatedUtc, DATEPART(TZOFFSET, CreatedUtc AT TIME ZONE 'Central Europe Standard Time')))

Notice: I haven’t yet done any benchmarking on the above approach and it might make sense to extract this to a function to clean things up a bit.

Also see the official SWITCHOFFSET documentation.
https://docs.microsoft.com/en-us/sql/t-sql/functions/switchoffset-transact-sql?view=sql-server-ver15

If you have another better approach please do let me know :-)

DEV Community: Peter Lindholm

The case of the Mysterious Azure Storage spikes

What changed?

Lessons Learned

Switching the offset for a UTC date in SQL Server