Loadtesting SQL, the sequel

Some time ago, I wrote a number of blog posts comparing the different Azure SQL options to give you some idea about performance, differences between tiers and differences between the Stock Keeping Units (SKU’s). I did this by creating data in the database and reviewing the metrics. This worked well and gave a good overview of the different tiers and SKUs. For reference, you can find those blogs here.

For the new series, I’ve thought of a process that aligns more with my regular work in data warehousing. This means ingesting a lot of data and modelling it.

In this first blog, I’ll walk you through the scripts I’ve used and the different database tiers and SKUs. As usual, don’t take the numbers you’ll see in the blogs as absolutes; compare them to find the best fit.

Disclaimer

I’m writing this series while also working on a series on the Microsoft DP-700 certification exam, and writing a book on the DP-800 certification. Testing the databases and working the results takes time. Unlike the previous series, where I published almost weekly, this will not happen with this one. I’m aiming for once every 2-3 weeks. [Update Oct 2026]: Looking at the date of this blog and the results, I was overly optimistic.

Databases

I’ve decided to test most databases. I skipped the basic and free tiers because they can’t hold all the data. Then there are the options like memory-optimised, compute-optimised, and confidential computing. I might add these later if they differ significantly from the standard series. For now, I’ve decided to not test these.
I’ve tested the standard and premium DTU (Database Transaction Unit) tiers, each in two SKUs. I’ve then moved on to the processor-based databases: General Purpose, Hyperscale and Business Critical. Each of these is tested at the 4,8 and 16-core SKUs, as these are the ones I’ve seen the most at customers. Finally, I’ve included the Azure SQL Managed Instance. Again, with 4, 8 and 16 cores.

Some databases, like the Managed Instance and Hyperscale, have a Premium option (which indicates faster hardware). I’ve included this as well.

To give you an overview of the choices, here’s a summary. Don’t hold me to it when you’re reading this, as Microsoft can add or remove SKU’s or even Tiers before I have the chance to update this blog. Feel free to comment if you think one of the numbers is wrong.

Tier Name	Standard	Premium	Premium (Memory Optimised)	Compute Optimized	Confidential Computing
Basic	1
Standard	9
Premium		6
General Purpose (Provisioned)	15			11	12
General Purpose (Serverless)	15
Hyperscale (Provisioned)	14	18	15		12
Hyperscale (Serverless)	14
Business Critical	15				12
Managed Instance (General Purpose)	8	8	7
Managed Instance (Business Critical)	8	16	16
Managed Instance (Next Gen General Purpose)	8	16*	16

The MI Next gen GP Premium and Business Critical Premium series comes with different options for RAM memory as well, maxing out at 560 GB

In total, there are 263 options to choose from, not including data size, memory size, or number of replicas. With all these options, the first question I always get is which one to choose. This blog series tries to help with that question. It won’t answer it because every workload is different, and requirements vary. But in the end, what everyone wants is a very fast database for as little money as possible. Little spoiler alert: performance doesn’t come cheap. So this blog aims to give you insights into performance differences and inspiration to model your own workload and find the sweet spot.

Fabric SQL

There’s a special kid on the block: Azure SQL DB in Microsoft Fabric. I’ve decided not to feature that specific database in this series yet.

Ingesting

The data I’ll ingest is a set of TPC-H CSV files. For those who follow my excursions in Fabric, the dataset will be familiar, since it’s the same set of files. About 500 GB of raw text. To ingest these files, I’m using DBA Tools. To learn more about this set of PowerShell cmdlets, click here! This tooling can read the file and write the data into my table. When using the import data wizard, this process fails for the simple reason it tries to load it all in memory. My machines don’t have that much memory. This is why I switched to an Azure VM where I can add memory freely. I also added a very fast disk to make sure my local disk won’t be a bottleneck.

The VM: Standard D16lds v5 (16 vCPUs, 32 GiB memory)
Extra data disk: Premium SSD LRS, 700 GB, 5000 IOPS, 200 MBps max throughput.

The tables are created from a script without indexes. I firmly believe that data ingestion is fastest when the table is a heap. Just dump it all in; we’ll sort it later (pun intended). This only applies to full loads; incremental loads are different because you need to find the highest ID, latest date, or whatever indicator you’re using. Doing that on a heap is ‘less efficient’ than on a properly indexed table.

Due to the size of my data, I created a database with 1 TB of reserved space. Not every SKU can handle that, which limited choices at times.

Techniques

To measure performance, I’m using two different techniques. The first version of this blog featured the serial ingestion of the different files I created. The code for this part is quite simple. Create an array of SKUs, iterate through it, and write the data.

I’ve since changed the code. There’s a CEO.ps1

# Code setup
# - Connect to Azure Sql Instance
# - Set the sku of the Azure Sql Instance
# - Azure SQL VM
# - Standard S2
# - Standard S7
# - Premium P2
# - Premium P6
# - General Purpose Gen5 4 vCores
# - General Purpose Gen5 8 vCores
# - General Purpose Gen5 16 vCores
# - Hyperscale Gen5 4 vCores
# - Hyperscale Gen5 8 vCores
# - Hyperscale Gen5 16 vCores
# - Hyperscale Premium Gen5 4 vCores
# - Hyperscale Premium Gen5 8 vCores
# - Hyperscale Premium Gen5 16 vCores
# - Business Critical Gen5 4 vCores
# - Business Critical Gen5 8 vCores
# - Business Critical Gen5 16 vCores
# - Managed Instance General Purpose Gen5 4 vCores
# - Managed Instance General Purpose Gen5 8 vCores
# - Managed Instance General Purpose Gen5 16 vCores
# - Create a new table
# - Insert data into the table
# - Get the insert performance from the DBA Tools module
# - Drop the table
# - Change the sku of the Azure Sql Instance
# - Repeat, except for MI, that's manual labour

Clear-Host
#$AzureCred = Get-Credential ** MyCreds
#Connect-AzAccount -Tenant '**MyTenant' -SubscriptionId '**MySubscription' -Credential $AzureCred
Write-Host 'Connect to Azure' -ForegroundColor Green
Connect-AzAccount -Identity
Set-AzContext -Subscription "**MySub"
Get-AzContext
#Connect to the Azure Sql Instance

$sqlCred = Get-Credential DbaToolsLogin
$server = Connect-DbaInstance -SqlInstance sqlmvplearn.database.windows.net -SqlCredential $sqlCred
#"S2", "S7", "P2", "P6", "GP_Gen5_4", "GP_Gen5_8", "GP_Gen5_16", "BC_Gen5_4",  "BC_Gen5_8","BC_Gen5_16","HS_Gen5_4", "HS_Gen5_8", "HS_Gen5_16", "HS_PRMS_4", "HS_PRMS_8",
$skuArray = @( "HS_S_Gen5_2","HS_Gen5_4", "HS_Gen5_8", "HS_Gen5_16", "HS_PRMS_4", "HS_PRMS_8", "HS_PRMS_16")
#$skuArray  = ("HS_Gen5_2")
try {
foreach ($sku in $skuArray){

        IF($sku -eq "S2" -or $sku -eq "S7"){
            $edition = "Standard"
        }
        IF($sku -eq "P2" -or $sku -eq "P6"){
            $edition = "Premium"
        }
        IF($sku -eq "GP_Gen5_4" -or $sku -eq "GP_Gen5_8" -or $sku -eq "GP_Gen5_16"){
            $edition = "GeneralPurpose"
        }
        IF ($sku -eq "HS_S_Gen5_2" -or $sku -eq "HS_Gen5_2" -or $sku -eq "HS_Gen5_4" -or $sku -eq "HS_Gen5_8" -or $sku -eq "HS_Gen5_16" -or $sku -eq "HS_PRMS_4" -or $sku -eq "HS_PRMS_8" -or $sku -eq "HS_PRMS_16"){
            $edition = "Hyperscale"
        }
        IF ($sku -eq "BC_Gen5_4" -or $sku -eq "BC_Gen5_8" -or $sku -eq "BC_Gen5_16"){
            $edition = "BusinessCritical"
        }
        IF($sku -eq "VM"){
            $edition = "VM"
            Write-Host "Connecting to VM" -ForegroundColor Blue
            $server = Connect-DbaInstance -SqlInstance 10.0.0.8\SQL25SD:50390 -SqlCredential $sqlCred -TrustServerCertificate
            $server
        }

        

        Write-Host "Checking if the database is free to use" -ForegroundColor DarkYellow
        $RunningProcesses = 1
        while ($RunningProcesses -gt 0) {
            $RunningProcessesQuery = "SELECT 
                                        COUNT(1)
                                    FROM 
                                        sys.dm_exec_requests r
                                    JOIN 
                                        sys.dm_exec_sessions s ON r.session_id = s.session_id
                                    CROSS APPLY 
                                        sys.dm_exec_sql_text(r.sql_handle) t
                                    WHERE 
                                        s.is_user_process = 1
                                    	AND login_name = '**MyLogin'
                                        AND s.session_id <> @@SPID"
         $GetResult = Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -Query $RunningProcessesQuery
         $RunningProcesses = $GetResult.Column1

         Write-Host "Active processes: $RunningProcesses" -ForegroundColor Magenta
         if($RunningProcesses -gt 0)
         {Start-Sleep -Seconds 60}
            
           }    

        Write-Host "Dropping old tables" -ForegroundColor Yellow

            Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -Query "DROP TABLE IF EXISTS nation;"
            Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -Query "DROP TABLE IF EXISTS region;"
            Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -Query "DROP TABLE IF EXISTS supplier;"
            Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -Query "DROP TABLE IF EXISTS part;"
            Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -Query "DROP TABLE IF EXISTS customer;"
            Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -Query "DROP TABLE IF EXISTS partsupp;"
            Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -Query "DROP TABLE IF EXISTS orders;"
            Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -Query "DROP TABLE IF EXISTS lineitem;"
           

            Write-Host "Setting sku to $sku with Edition $edition" -ForegroundColor Green

            if ($sku -ne "VM") {
            if ($sku -eq "S2")
            {
                $MaxSize = 268435456000
                Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -Query "DBCC SHRINKDATABASE(PerfDB);"
            }
            else
            {
                $MaxSize = 1099511627776
            }

            Set-AzSqlDatabase -ResourceGroupName "rg-sql" -DatabaseName "PerfDB" -ServerName "sqlmvplearn" -Edition $edition -RequestedServiceObjectiveName $sku -MaxSizeBytes $MaxSize
        }

        Write-Host "Creating a new tables" -ForegroundColor Green

            Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -File '.\tpc-h tables.sql'
            
            Write-Host "Inserting data into the table" -ForegroundColor Green

        Write-Host "Getting start Wait Stats" -ForegroundColor DarkYellow
        $GetWaitResult = Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -File .\Waitstats.sql | Format-Table
        $GetWaitResult | Out-File .\$sku-WaitPreLoad.csv -Encoding utf8
        
        pwsh .\TPCH-Controller.ps1
Write-Host "Checking if the database is free to use" -ForegroundColor DarkYellow
        $RunningProcesses = 1
        while ($RunningProcesses -gt 0) {
            $RunningProcessesQuery = "SELECT 
                                        COUNT(1)
                                    FROM 
                                        sys.dm_exec_requests r
                                    JOIN 
                                        sys.dm_exec_sessions s ON r.session_id = s.session_id
                                    CROSS APPLY 
                                        sys.dm_exec_sql_text(r.sql_handle) t
                                    WHERE 
                                        s.is_user_process = 1
                                    	AND login_name = '**MyLogin'
                                        AND s.session_id <> @@SPID"
         $GetResult = Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -Query $RunningProcessesQuery
         $RunningProcesses = $GetResult.Column1

         Write-Host "Active processes: $RunningProcesses" -ForegroundColor Magenta
         if($RunningProcesses -gt 0)
         {Start-Sleep -Seconds 60}
            
           }
        Write-Host "Getting end Wait Stats" -ForegroundColor DarkYellow

        $GetWaitResult = Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -File .\Waitstats.sql | Format-Table
        $GetWaitResult | Out-File .\$sku-WaitPostLoad.csv -Encoding utf8

        

        Write-Host "Creating Stored Procedures" -ForegroundColor Green

        Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -File '.\tpc-h procedures.sql'

		Write-Host "Running procedures" -ForegroundColor Green

		$RunETL = Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -File '.\Run SP.sql' -MessagesToOutput
		$RunETL | Out-File .\$sku-ETL.txt -Encoding utf8
}
}
catch
{Write-Output "An error occurred: $_"}

The CEO does all the preparations and calls the Controller to start doing the work.

Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 11 
Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 12 
Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 13
Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 14 
Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 21 
Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 22 
Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 23 -Wait
Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 24 
Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 31 
Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 32 
Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 33 
Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 34 
Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 41 
Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 42 
Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 43 
Start-Process -FilePath "pwsh.exe" -ArgumentList "-File", .\TPCH-Loader.ps1, 44

This will launch 16 PowerShell windows, each processing a set of files to populate the database. This many files will make the most of all available threads on the database. When running with fewer, I was just flooding the processor without knowing how much the database could actually handle.

Let’s look at the loader.

param (
    [Parameter()]
    [int]
    $FileNumber
)
try {
$FileString = $FileNumber.ToString()
$PartNumber = $FileString.Substring(0,1)
$SplitNumber = $FileString.Substring(1,1)

$password = ConvertTo-SecureString "**yourPassword" -AsPlainText -Force
$username = "**YourLogin"
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $password
$Server = Connect-DbaInstance -SqlInstance sqlmvplearn.database.windows.net -SqlCredential $cred
#$server = Connect-DbaInstance -SqlInstance 10.0.0.8\SQL25SD:50390 -SqlCredential $sqlCred -TrustServerCertificate
$Folder = "E:\tpch\files\"
$files = @("customer", "partsupp", "lineitem")
$filesS2 = @("customer", "partsupp")
$query = ("SELECT concat_ws('-',slo.edition, slo.service_objective)
FROM sys.databases d
JOIN sys.database_service_objectives slo
ON d.database_id = slo.database_id;")
$dbSKU = Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -Query $query

$SKUNAME = $dbSKU.Column1
#$SKUNAME = "VM"

$output = "This will process file number $FileNumber"
Write-Host $output

if ($SKUNAME.Contains("S2"))
    {
        $files = $filesS2
       }
$files | Out-File .\$SKUNAME-$FileNumber.txt -Append -Encoding utf8

Write-Host $files
foreach ($File in $files) {
    $now = Get-Date
    $Header = "Timestamp start: $now"
    Write-Host "Starting Data Import for $File at $now" -ForegroundColor Green
    $header | Out-File .\$SKUNAME-$FileNumber.txt -Append -Encoding utf8
    Write-Host "Checking table existence" -ForegroundColor Yellow
    $query = "SELECT name from sys.tables where name = '$File'"
    Write-Host $query
    $tableResult = Invoke-DbaQuery -SqlInstance $Server -Database PerfDB -Query $query
    Write-Host "Result" -ForegroundColor Red
    Write-Host $tableResult.name
    Write-Host "Go!" -ForegroundColor Green
    Import-DbaCsv -SqlInstance $Server -Database PerfDB -Path $Folder$File.tbl_part$PartNumber.$SplitNumber -Delimiter '|' -Table $File$PartNumber$SplitNumber -Schema dbo -NoHeaderRow -WarningAction Inquire -TableLock | Out-File .\$SKUNAME-$FileNumber.txt -Append -Encoding utf8
    $now = Get-Date
    Write-Host "File $File Part $PartNumber SplitNumber $SplitNumber done at $now" -ForegroundColor Green
    $Header = "Timestamp end: $now"
    $header | Out-File .\$SKUNAME-$FileNumber.txt -Append -Encoding utf8
}
}
catch {
    $err = "error: $_"
    $err
    $err | Out-File .\$SKUNAME-$FileNumber.txt -Append -Encoding utf8
    }

There’s a lot going on here; you can also see some of what I learned. One is that if you don’t use UTF-8 encoding, files become unreadable. Another is that you need the TABLOCK hint when loading the data.

When importing data into a heap by using the INSERT INTO <target_table> SELECT <columns> FROM <source_table> statement, you can enable minimal logging and optimal locking for the statement by specifying the TABLOCK hint for the target table. In addition, the recovery model of the database must be set to simple or bulk-logged. The TABLOCK hint also enables parallel inserts to heaps or clustered columnstore indexes.

You can find the text above here.

The rest of the commands show some output I found handy when trying to see what was happening, and they make sure the logs are entered in the correct files.

Cleansing and modelling

My personal preference is to drag data around as little as possible. So in this case, I’m not using a staging, silver, cleansing layer. I’m going from the raw data to the star schema. To load the data, I’m using the MERGE command. I know a lot of people have opinions about that, but for this use case, it works.

The code looks like this:

create or alter procedure sp_loadNations
as
BEGIN
	SET NOCOUNT ON;
	MERGE dim.nations AS Target
	USING (

SELECT DISTINCT
	n_nationkey as NationID,
	n_name as NationName,
	n_comment as NationComment
	FROM dbo.nation) AS Source
	ON Source.NationID = Target.NationID

	WHEN MATCHED 
		AND source.NationName != target.NationName
		OR source.NationComment != target.NationComment
	THEN UPDATE
		SET 
			 Target.NationName = Source.NationName,
			 Target.NationComment = Source.NationComment,
			 Target.ChangeDate = SYSDATETIME()

	WHEN NOT MATCHED BY SOURCE THEN UPDATE
		SET Target.DeleteDate = SYSDATETIME()

	WHEN NOT MATCHED BY TARGET 
	THEN 
	INSERT(NationID, NationName, NationComment, IsActual, CreateDate, ChangeDate, DeleteDate)
	VALUES (NationID, NationName, NationComment,1, SYSDATETIME(),SYSDATETIME() ,'2099-01-01' );
END

Concluding

In short, this is the setup I’ve been using to see how much the databases can handle. Don’t take my numbers from the following blogs as absolutes; compare them with each other. When you use an external tool to ingest data, performance will differ. Keep in mind that every tool has some overhead. The test results will follow in separate blog posts.

The results.

Tier	Blog link
Standard	https://sqlreitse.com/2026/10/20/load-testing-sql-the-sequel-standard-edition/
Premium	https://sqlreitse.com/2026/10/20/load-testing-sql-the-sequel-standard-edition/
General Purpose
Business Critical
Hyperscale

6 thoughts on “Loadtesting SQL, the sequel”

Leave a Reply Cancel reply