Overhead of Event Hub outputs with Azure Function Apps

In a world where you’re billed for what you use, it pays to really understand what exactly it is you are using. The pricing model of the Azure Function Apps consumption plan sounds pretty simple (pay based on execution count and execution time) - though as always the devil is in the detail.

In a recent project where we’ve migrated a workload from a dedicated (on-prem) server to a function app, someone asked what sounded like a fairly simple question:

  • Do we pay (on the function app side) for the output to an Event Hub, and if so how much does it cost?

This post explores the answer to that question for a trivial C# function, and provides a few pointers to help get your head around consumption billing.

If you’re wondering why we wanted to deconstruct the function’s cost when an execution unit costs a mere 16 picdollars (16x10^-12), consider what we saw after our first week at full load:

A lot of executions

5 trillion execution units… Interesting!

Execution units vs. what you see on the pricing page is covered later in the post.

[Read More]

Adding caching to your PowerShell scripts

Most of the time when some part of a script takes a long time to run and you want to re-use the result you’ll store it in a variable:

$databases = Get-ListOfDatabases # assume this is an expensive/long-running query
Invoke-SomeOperation -Server serverOne -Databases $databases
Invoke-SomeOtherOperation -Server serverOne -Databases $databases

As long as the function you’re calling allows you to pass in the ‘expensive’ argument you’re fine - but what about cases when the computation takes place inside the function:

Invoke-SomeOperation -Server serverOne # internally calls Get-ListOfDatabases
Invoke-SomeOtherOperation -Server serverOne # internally calls Get-ListOfDatabases

Sometimes it makes sense to rework the script to accept the argument, though other times it can be cleaner to modify the call site to cache the result, rather than changing every function to accept and potentially pass through that argument.

The rest of this post will go through a specific example that motivated caching, share a generic function that implements scriptblock-based caching, and call out a few and gotchas.

[Read More]
Tags: PowerShell

Resumable Online Index Rebuilds - SQL 2017's Silver Bullet

Every new version of SQL Server comes with a whole grab-bag of new features that open up exciting possibilities, or obviate the need for workarounds (or dirty hacks). In the runup to deploying SQL 2017 I thought that automatic plan correction was going to be the closest thing to a silver bullet I’d seen in any release so far (in terms of value added vs. effort to implement), but it has been eclipsed for us by the awesomeness of Resumable Online Index Rebuilds (ROIR).

In this post I’ll talk through a few of the scenarios where this feature really shines, and how it has transformed the way we think about index maintenance. If you’d like more details about how ROIR is implemented I’d encourage you to read through the excellent paper detailing ROIR - this covers how the online index rebuild algorithm was updated, and also demonstrates how in most cases ROIR outperforms the non-resumable version in terms of performance.

[Read More]
Tags: SQL

SQL Managed Backups and Operating System Error 87

We use SQL Managed backups for our on-premises SQL Servers, and have been very impressed with it (from a speed, management, and cost perspective). Shortly after deploying the solution though the SQL error logs started to log errors when attempting to read managed backups:

BackupIoRequest::ReportIoError: read failure on backup device
'https://allthebackups.blob.core.windows.net/ServerOne/LongFileName.log'.
Operating system error 87(The parameter is incorrect.).

Nothing suggested there were any issues - backups were still being taken, our backup chain wasn’t broken (restores were fine) - but this error was being logged all the time.

Understanding where the error came from and how to fix it required a better understanding of exactly how managed backup works.

[Read More]
Tags: SQL Azure

Mitigating SERVICE_BROKER_WAITFOR_MANAGER latch waits

Our production environment recently started generating alerts on huge blocking chains (100s of requests), which were accompanied by increased database response times for various procedures. The blocking chains all had the latch wait SERVICE_BROKER_WAITFOR_MANAGER in common, some of which were blocking for seconds (adding significant overhead to operations that would normally complete in a few milliseconds).

In this post I’ll walk through what an environmentin leveraging service broker might look like, show you how to reproduce the issue, and offer some mitigation strategies/general advice for service broker at scale.

Blocking

[Read More]
Tags: SQL