about 1 hour ago

The Slow Reporting Server That Wasn't a Network Problem

How a Standard HDD data disk quietly throttled a database VM in Azure, and a PowerShell script to prove it with before/after numbers.

tags: [azure, windows, storage, performance, powershell, diskspd]

The Slow Reporting Server That Wasn't a Network Problem

A user reported that "everything got slow" on a Windows VM in Azure that hosts a reporting database: queries, dashboards, and a regular data export that suddenly took 20 minutes longer than usual. It had started roughly three weeks earlier, and nobody could remember changing anything.

The first instinct was to blame the network. A speed test from inside the VM showed about 120 Mbit/s, which felt low. It turned out to have nothing to do with the problem.

Looking at the disks

The VM itself was a reasonable size (4 vCPUs, 16 GB RAM) with a Premium SSD as the OS disk. The data disk holding the database was a different story:

Disk	Type	Size	Max IOPS	Max throughput	Host caching
OS	Premium SSD	512 GiB	5,000	200 MB/s	Read/write
Data (database)	Standard HDD	512 GiB	500	60 MB/s	None

A database on a Standard HDD with 500 IOPS is a problem waiting to happen. This setup had probably worked fine for years because the frequently used part of the database fit into RAM. Once the data grew past that point, queries had to go to the disk, and performance didn't degrade gradually. It fell off a cliff. That fits "suddenly slow, about three weeks ago" perfectly.

Measure before you change anything

Before paying for a faster disk, I wanted numbers, for two reasons: to confirm the diagnosis, and to have something concrete to show whoever approves the extra cost.

The tool for this on Windows is Microsoft's DiskSpd. It's free, reliable, and what Microsoft itself uses for storage testing. Its raw output, however, is a wall of text per run, and comparing two runs by hand is tedious. So I wrapped it in a small PowerShell script.

What the script does

Checks for admin rights and offers to relaunch elevated. Without admin rights DiskSpd can't allocate the test file instantly (you'll see a SeManageVolumePrivilege warning) and has to write the whole file first. The results are still valid, it just takes longer.
Validates the drive and free space, and lists available drives if you mistype the letter.
Downloads DiskSpd into C:\DiskTest if it isn't there yet.
Runs three workload profiles against a temporary 4 GB test file on the target drive.
Parses DiskSpd's XML output into IOPS, MB/s, average latency, and P99 latency.
Appends the results to a CSV with a label such as before or after.
Prints a comparison of the first and latest run per test, including improvement factors.
Deletes the test file, even if you abort with Ctrl+C.

It does not touch existing data or change any configuration. It does, however, saturate the disk for a few minutes, so run it outside business hours.

The three test profiles

Profile	DiskSpd parameters	Simulates
Random read 4K	`-b4K -r -w0 -t4 -o32`	Database lookups: many small, scattered reads
Random 8K 70/30	`-b8K -r -w30 -t4 -o32`	Mixed database workload with inserts and updates
Sequential read 64K	`-b64K -s -w0 -t1 -o8`	Exports, backups, file downloads

All tests share -d30 -W5 -Sh -L: 30 seconds of measurement after a 5-second warm-up, Windows caching disabled so you measure the disk rather than RAM, and latency percentiles captured.

-t4 -o32 means 4 threads with 32 outstanding requests each, so 128 requests in flight. That's deliberately aggressive. It shows what the disk can deliver at its limit, not what a single query experiences.

Usage

# Once, if script execution is blocked in this session
Set-ExecutionPolicy -Scope Process Bypass

# Before the change
.\Test-DiskPerformance.ps1 -Drive S -Label before

# ... make the change ...

# After the change
.\Test-DiskPerformance.ps1 -Drive S -Label after

Optional parameters: -FileSize 16G for a larger test file (reduces cache effects), -Duration 60 for longer runs, -NoPause for scheduled or remote runs. Get-Help .\Test-DiskPerformance.ps1 -Full shows the complete help.

The "before" numbers

Test	IOPS	MB/s	Avg latency	P99 latency
Random read 4K	407	1.6	361 ms	12.8 s
Random 8K 70/30	398	3.1	370 ms	12.6 s
Sequential 64K	410	25.6	22 ms	58 ms

Every test hits the same wall at roughly 400 IOPS, which is the disk's limit. The real story is in the latency columns. An average read took over a third of a second, and the slowest 1% took almost 13 seconds. A healthy SSD answers in 1 to 5 ms. The database was spending most of its time waiting.

Even sequential throughput only reached 25 MB/s, less than half of the nominal 60 MB/s. That explained the longer export times.

While the test runs, it's worth watching the Azure metric Data Disk IOPS Consumed Percentage on the VM. If it sits at 100%, you have your proof in a single screenshot.

The fix

The change itself was simple:

Stop (deallocate) the VM.
On the data disk, go to Size + performance and switch to Premium SSD. At 512 GiB that's a P20 with 2,300 IOPS and 150 MB/s, plus free credit-based bursting up to 3,500 IOPS and 170 MB/s.
On the VM, under Disks, set host caching for the data disk to Read-only.
Start the VM.

Total downtime was about 15 minutes, and the data stays in place. The cost difference was roughly €50–60 per month.

A few things to check before stopping the VM:

Public IP: a dynamic public IP changes on deallocation. Set it to static first if anything depends on it.
Temporary disk: the local temp drive is wiped on deallocation. Make sure nothing important lives there.
Snapshot: take a snapshot of the data disk first. It costs next to nothing and can be deleted after a week.
Database service: stop it cleanly before shutting down.

Why Read-only host caching?

Host caching uses local storage on the physical Azure host as a cache between VM and disk.

Read-only: frequently read blocks are served from the host cache, which is faster and doesn't count against the disk's IOPS limit. Writes go straight to the disk, so nothing is lost if the host fails. This is the standard recommendation for database data files.
Read/write: also caches writes. Risky for databases, because acknowledged writes could be lost on a host failure.
None: no caching. Reasonable for transaction log disks with purely sequential writes.

Change caching while the VM is stopped. On a running VM, Azure briefly detaches and reattaches the disk, which a running database won't appreciate.

One more limit: the VM size

After the change, the portal showed a warning: the VM size supports about 96 MB/s of uncached disk throughput, while the attached disks could deliver more combined. Every Azure VM size has its own IOPS and throughput cap on top of the disk limits.

In this case it didn't matter much. The VM allows 6,400 uncached IOPS, far more than the P20's 2,300, so latency and IOPS weren't affected. Only sequential throughput is capped at around 96 MB/s, which is still almost four times what we had. If exports are still too slow, moving to a newer VM generation with the same core count (for example from v3 to v5) raises that cap, often at the same price or less.

The "after" numbers

Running the script a second time with -Label after produces the comparison:

Test	IOPS	MB/s	Avg latency	P99 latency
Random read 4K	407 → 7,071	1.6 → 27.6	361 → 15.7 ms	12.8 s → 62 ms
Random 8K 70/30	398 → 4,970	3.1 → 38.8	370 → 29.6 ms	12.6 s → 75 ms
Sequential 64K	410 → 2,733	25.6 → 170.8	22 → 3.4 ms	58 → 49 ms

The headline number isn't IOPS, it's P99 latency: from almost 13 seconds down to under 0.1 seconds, roughly a factor of 200. Those multi-second stalls were what made every query feel stuck.

Reading the numbers honestly

The read results are flattered by the cache. 7,000 IOPS is well above what a P20 can do on its own. The 4 GB test file fits entirely into the host cache. A real database is larger, so the effect will be smaller. Anything read frequently benefits the same way, though. Use -FileSize with a larger value if you want to see the uncached disk.
The sequential numbers are burst values. The P20 sustains 150 MB/s, and the VM size caps uncached throughput at about 96 MB/s. Both are still several times the old figure.
Average latency looks high because the test is aggressive. With 128 requests in flight, requests queue even on a fast disk. As a rule of thumb, average latency ≈ requests in flight ÷ IOPS: 128 ÷ 7,071 ≈ 18 ms, which is almost exactly what was measured. Under normal database load, with far fewer concurrent requests, latency sits in the low single-digit milliseconds.

The real test, of course, is the users: after the change I asked the team to run their usual queries, reports and exports and report back.

Takeaways

"Everything is slow" is rarely the network. Check disk metrics first. In Azure, Data Disk IOPS Consumed Percentage stuck at 100% tells you most of the story.
Performance cliffs are real. A database that outgrows its RAM cache doesn't slow down linearly.
Look at tail latency, not averages. The P99 value explained the user experience far better than IOPS did.
Check the VM limits too. A fast disk behind a small VM size won't reach its full throughput.
Benchmark before and after. Fifteen minutes of testing turned "I think it's the disk" into numbers that justify the extra cost and prove the fix worked.

The full script

Save it as Test-DiskPerformance.ps1. Written for Windows PowerShell 5.1 (Windows 10/11, Windows Server).

<#
.SYNOPSIS
    Before/after disk benchmark for Windows (e.g. Azure VMs) using Microsoft DiskSpd.

.DESCRIPTION
    Runs three workload profiles against a drive, prints a readable summary
    (IOPS, MB/s, average and P99 latency) and appends the results to a CSV.
    Run it once before and once after a change (disk type, VM size, caching)
    and the script shows a side-by-side comparison with improvement factors.

    What it does:
      - Downloads DiskSpd into the work folder if it isn't there yet
      - Creates a temporary test file on the target drive and deletes it afterwards
      - Does NOT touch any existing data or change any configuration

    What to keep in mind:
      - The test saturates the disk for a few minutes. Run it outside business hours.
      - Run as Administrator. Without admin rights DiskSpd can't allocate the
        test file instantly and has to write it out first (slower, still valid).
      - With host read caching enabled (Azure "Read-only"), read results can exceed
        the disk's rated IOPS because the test file fits into the cache.

.PARAMETER Drive
    Drive letter of the disk to test, e.g. S

.PARAMETER Label
    Name for this run, e.g. before / after. Used to compare runs in the CSV.

.PARAMETER FileSize
    Size of the temporary test file (default 4G). Bigger files reduce cache effects.

.PARAMETER Duration
    Seconds per test (default 30), plus 5 seconds warm-up.

.PARAMETER NoPause
    Don't wait for Enter at the end (for scheduled or remote runs).

.EXAMPLE
    .\Test-DiskPerformance.ps1 -Drive S -Label before
    # ... change the disk ...
    .\Test-DiskPerformance.ps1 -Drive S -Label after

.LINK
    https://github.com/microsoft/diskspd
#>
[CmdletBinding()]
param(
    [Parameter(Mandatory)] [ValidatePattern('^[A-Za-z]:?\\?$')] [string]$Drive,
    [Parameter(Mandatory)] [string]$Label,
    [string]$WorkDir  = "C:\DiskTest",
    [ValidatePattern('^\d+[KMG]$')] [string]$FileSize = "4G",
    [ValidateRange(10, 600)] [int]$Duration = 30,
    [switch]$NoPause
)

$ErrorActionPreference = 'Stop'
$inv = [Globalization.CultureInfo]::InvariantCulture

function Exit-Script([int]$code = 0) {
    if (-not $NoPause) { Read-Host "`nDone - press Enter to close" | Out-Null }
    exit $code
}

# --- Admin check: offer to relaunch elevated ---
$isAdmin = ([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()
           ).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)
if (-not $isAdmin) {
    Write-Warning "Not running as Administrator. Test file preparation will be slower."
    $answer = Read-Host "Relaunch elevated? [Y/n]"
    if ($answer -notmatch '^[nN]') {
        $argList = @('-NoProfile', '-ExecutionPolicy', 'Bypass', '-File', "`"$PSCommandPath`"",
                     '-Drive', $Drive, '-Label', "`"$Label`"", '-WorkDir', "`"$WorkDir`"",
                     '-FileSize', $FileSize, '-Duration', $Duration)
        if ($NoPause) { $argList += '-NoPause' }
        try {
            Start-Process powershell.exe -Verb RunAs -ArgumentList $argList
            exit 0
        } catch {
            Write-Warning "Elevation was cancelled. Continuing without admin rights."
        }
    }
}

# --- Validate drive and free space ---
$Drive = $Drive.Substring(0,1).ToUpper()
$psDrive = Get-PSDrive -Name $Drive -PSProvider FileSystem -ErrorAction SilentlyContinue
if (-not $psDrive) {
    Write-Host "Drive $($Drive): not found. Available drives:" -ForegroundColor Red
    Get-PSDrive -PSProvider FileSystem | Format-Table Name, Description,
        @{n='Free (GB)'; e={[math]::Round($_.Free/1GB,1)}} -AutoSize
    Exit-Script 1
}

$unit   = $FileSize.Substring($FileSize.Length - 1)
$size   = [double]$FileSize.Substring(0, $FileSize.Length - 1)
$fileGB = switch ($unit) { 'G' { $size } 'M' { $size / 1KB } 'K' { $size / 1MB } }
$freeGB = $psDrive.Free / 1GB
if ($freeGB -lt ($fileGB + 2)) {
    Write-Host "Only $([math]::Round($freeGB,1)) GB free on $($Drive): - reduce -FileSize." -ForegroundColor Red
    Exit-Script 1
}

$TestFile = "$($Drive):\diskspd_test.dat"
$DiskSpd  = Join-Path $WorkDir "amd64\diskspd.exe"
$CsvFile  = Join-Path $WorkDir "disk_results.csv"

# --- Get DiskSpd ---
if (-not (Test-Path $DiskSpd)) {
    Write-Host "Downloading DiskSpd..." -ForegroundColor Cyan
    New-Item -ItemType Directory -Path $WorkDir -Force | Out-Null
    $zip = Join-Path $WorkDir "DiskSpd.zip"
    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
    try {
        Invoke-WebRequest "https://github.com/microsoft/diskspd/releases/latest/download/DiskSpd.zip" `
            -OutFile $zip -UseBasicParsing
        Expand-Archive $zip -DestinationPath $WorkDir -Force
    } catch {
        Write-Host "Download failed: $($_.Exception.Message)" -ForegroundColor Red
        Write-Host "Download DiskSpd manually and extract it to $WorkDir"
        Exit-Script 1
    }
}

# --- Test profiles ---
# -Sh  bypass Windows cache (measure the disk, not RAM)
# -L   capture latency percentiles
# -W5  5 s warm-up before measuring
$tests = @(
    @{ Name = "Random read 4K (DB lookups)";      Args = "-b4K  -r -w0  -t4 -o32" },
    @{ Name = "Random 8K 70/30 read/write (DB)";  Args = "-b8K  -r -w30 -t4 -o32" },
    @{ Name = "Sequential read 64K (exports)";    Args = "-b64K -s -w0  -t1 -o8"  }
)

$total = $tests.Count * ($Duration + 5)
Write-Host "`nTesting drive $($Drive): as '$Label' - approx. $([math]::Ceiling($total/60)) min plus file preparation" -ForegroundColor Cyan

$results = @()
try {
    for ($i = 0; $i -lt $tests.Count; $i++) {
        $t = $tests[$i]
        Write-Host "`n[$($i+1)/$($tests.Count)] $($t.Name) ($Duration s)..." -ForegroundColor Yellow
        $xmlFile = Join-Path $WorkDir ("run_{0}_{1}.xml" -f ($Label -replace '[^\w-]','_'), $i)
        $cmd = "$($t.Args) -d$Duration -W5 -Sh -L -c$FileSize -Rxml `"$TestFile`""
        $p = Start-Process -FilePath $DiskSpd -ArgumentList $cmd -NoNewWindow -Wait -PassThru `
                           -RedirectStandardOutput $xmlFile
        if ($p.ExitCode -ne 0) { Write-Warning "DiskSpd exit code $($p.ExitCode)"; continue }

        try {
            [xml]$x  = Get-Content $xmlFile
            $ts      = $x.Results.TimeSpan
            $secs    = [double]::Parse($ts.TestTimeSeconds, $inv)
            $targets = $ts.Thread.Target
            $ios     = ($targets | Measure-Object ReadCount  -Sum).Sum + ($targets | Measure-Object WriteCount -Sum).Sum
            $bytes   = ($targets | Measure-Object ReadBytes  -Sum).Sum + ($targets | Measure-Object WriteBytes -Sum).Sum
            $avg     = [double]::Parse($ts.Latency.AverageTotalMilliseconds, $inv)
            $p99node = $ts.Latency.Bucket | Where-Object { [double]::Parse($_.Percentile, $inv) -eq 99 } | Select-Object -First 1
            $p99     = if ($p99node) { [double]::Parse($p99node.TotalMilliseconds, $inv) } else { $null }

            $results += [pscustomobject]@{
                Timestamp    = Get-Date -Format "yyyy-MM-dd HH:mm"
                Label        = $Label
                Test         = $t.Name
                IOPS         = [math]::Round($ios / $secs, 0).ToString($inv)
                MBps         = [math]::Round($bytes / $secs / 1MB, 1).ToString($inv)
                AvgLatencyMs = [math]::Round($avg, 2).ToString($inv)
                P99LatencyMs = if ($p99) { [math]::Round($p99, 2).ToString($inv) } else { "" }
            }
        } catch {
            Write-Warning "Could not parse results - raw data is in $xmlFile"
        }
    }
} finally {
    # Always remove the test file, even if the run is aborted with Ctrl+C
    Remove-Item $TestFile -Force -ErrorAction SilentlyContinue
}

if (-not $results) { Write-Host "No results collected." -ForegroundColor Red; Exit-Script 1 }

# --- Output ---
Write-Host "`nResults for '$Label':" -ForegroundColor Green
$results | Format-Table Test, IOPS, MBps, AvgLatencyMs, P99LatencyMs -AutoSize
$results | Export-Csv $CsvFile -Append -NoTypeInformation -Delimiter ';' -Encoding UTF8
Write-Host "Appended to: $CsvFile" -ForegroundColor Green

# --- Comparison: first run vs. latest run per test ---
$all = Import-Csv $CsvFile -Delimiter ';'
if (($all.Label | Select-Object -Unique).Count -gt 1) {
    $toNum = { param($v) if ($v) { [double]::Parse($v, $inv) } else { $null } }
    $cmp = foreach ($grp in ($all | Group-Object Test)) {
        $first = $grp.Group | Select-Object -First 1
        $last  = $grp.Group | Select-Object -Last 1
        if ($first.Label -eq $last.Label) { continue }
        $iops1 = & $toNum $first.IOPS;         $iops2 = & $toNum $last.IOPS
        $p991  = & $toNum $first.P99LatencyMs; $p992  = & $toNum $last.P99LatencyMs
        [pscustomobject]@{
            Test           = $grp.Name
            Compared       = "$($first.Label) -> $($last.Label)"
            IOPS           = "$($first.IOPS) -> $($last.IOPS)"
            'IOPS x'       = if ($iops1) { [math]::Round($iops2 / $iops1, 1) } else { "" }
            'P99 ms'       = "$($first.P99LatencyMs) -> $($last.P99LatencyMs)"
            'P99 faster x' = if ($p991 -and $p992) { [math]::Round($p991 / $p992, 1) } else { "" }
        }
    }
    if ($cmp) {
        Write-Host "`nComparison (first run vs. latest run):" -ForegroundColor Cyan
        $cmp | Format-Table -AutoSize
    }
}

Write-Host "Note: with host read caching, read results can exceed the disk's rated limits." -ForegroundColor DarkGray
Exit-Script 0