Microsoft Fabric vs Azure SQL Database Performance Comparison

Problem

Microsoft now offers databases within a Fabric (preview) workspace. How do Fabric databases compare to Azure SQL databases in terms of performance, assuming all other factors are equal? Additionally, why should you opt to create a Fabric database instead of one in Azure?

Solution

In this article, I will compare the performance of a Fabric database and an Azure SQL Database head-to-head. Going into this experiment, I don’t expect to see a significant difference in performance, as long as the vCore counts match between the two platforms. However, as I write this, I have no idea. Perhaps the experiment will surprise me. What do you think? Will one outperform the other? Keep a mental note before proceeding to the following sections.

Fabric Databases

As of the time of this article, Fabric SQL databases are in preview, but I suspect they will be generally available by the end of 2025. I’m no Nostradamus, so my prediction could be wrong. If you have a Fabric capacity, it’s easy to create a SQL database. No configurations are required for the vCores, hardware setups, or whether it will be serverless or provisioned. Fabric opts for serverless because, why not? Additionally, there’s no need to worry about any network configuration or firewall settings. In other words, a Fabric database is a Software as a Service (SaaS) offering.

In contrast, when you create an Azure SQL Database, you must make several decisions. For example, which service tier will you choose (general purpose, business critical, or hyperscale)? Will you opt for a provisioned database or a serverless one? How many vCores do you require? Now, I’m being a bit dramatic here, and I’ve always thought the process for creating an Azure SQL database was straightforward.

Microsoft maintains a list of the known differences between the two services. Below, I’ve included a few of the main features lacking in Fabric that might deter someone from using it:

• No Always Encrypted
• No Server-level Azure AD logins
• No EXECUTE AS
• No Azure PowerShell
• Database size up to 4 TB
• No free development databases—no, I don’t count the Fabric trial.

Overall, there isn’t a significant amount missing from a Fabric database unless something is specific to your use case.

Why Create a Fabric Database?

After acknowledging the known limitations of a Fabric database, why create one? For me, it boils down to two reasons. The first is the ease of creation; there is minimal friction involved in the process. Perhaps you don’t have anyone experienced in choosing the right options when creating an Azure SQL Database at your company. The second reason is that your data is already mirrored to Onelake, enabling you to perform cross-workload analytical queries out of the box.

But is the lack of friction a good thing? One could argue that simply creating a database is just the beginning of its journey, much like having a child or adopting a pet and bringing them to their new home; the hard work has only just begun. More on this idea of friction will be discussed later.

Head-to-Head Test

Originally, I planned to test the performance using HammerDB. However, Microsoft does not allow SQL authentication for Fabric databases. After several hours of trying to get HammerDB to use Entra authentication and creating an Azure VM to run it, I gave up and pivoted in another direction.

To test the performance of the two platforms, I’ll use a series of scripts to insert and update rows that I previously used in another article discussing the performance impact of constraints in SQL Server. To run the test, I’ll employ SqlQueryStress to simulate the workload and capture the metrics with the Glenn Berry-inspired DMV script below.

/*
* MSSQLTips.com
*/
SELECT t.text,
       qs.execution_count,
       qs.total_elapsed_time,
       qs.total_worker_time,
       qs.total_logical_reads / qs.execution_count AS [avg_logical_reads],
       qs.total_worker_time / qs.execution_count AS [avg_worker_time],
       qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time],
       qs.max_dop
FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
ORDER BY qs.execution_count DESC;

Database Configurations

The SKU of my Fabric capacity is set to an F2. For the Azure SQL Database, I’m utilizing the free serverless 2 vCores offered by Microsoft. I wanted to choose the two least expensive options available, as I prefer not to spend money. Both resources are situated in the East US region. According to Microsoft documentation, the Fabric database can scale up to 2 vCores at an F2, provided the logic matches that of a Data Warehouse. However, the scripts I’m using should not induce parallelism.

Build the Demo Table

To follow along, you will need to provision an Azure SQL Database and create a Fabric SQL Database. I’ll assume you’ve completed these steps. The script below creates our demo table, which contains 1 million rows.

/*
* MSSQLTips.com
*/
DROP TABLE IF EXISTS dbo.Person;
GO
CREATE TABLE dbo.Person
(
    Id INT IDENTITY(1, 1) NOT NULL,
    FirstName VARCHAR(25) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    PhoneNumber VARCHAR(100) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    BirthDate DATE NOT NULL,
    Salary DECIMAL(10, 2) NOT NULL,
    CreateDate DATETIME NOT NULL
        DEFAULT GETDATE(),
    ModifyDate DATETIME NULL,
    CONSTRAINT PK_Person_Id
        PRIMARY KEY CLUSTERED (Id)
);
GO
;WITH PersonData
 AS (SELECT TOP (1000000)
         SUBSTRING(
                      'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ',
                      (ABS(CHECKSUM(NEWID())) % 26) + 1,
                      8
                  ) AS FirstName,
         SUBSTRING(
                      'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ',
                      (ABS(CHECKSUM(NEWID())) % 52) + 1,
                      15
                  ) AS LastName,
         ABS(CHECKSUM(NEWID()) % 9999999999) + 1000000000 AS PhoneNumber,
         DATEADD(DAY, RAND(CHECKSUM(NEWID())) * (1 + 7300), '1980-01-01') AS Birthdate,
         ABS(CHECKSUM(NEWID()) % 100000) + 25000 AS Salary
     FROM sys.all_columns AS n1
         CROSS JOIN sys.all_columns AS n2
    )
INSERT INTO dbo.Person
(
    FirstName,
    LastName,
    PhoneNumber,
    Email,
    BirthDate,
    Salary
)
SELECT p.FirstName,
       p.LastName,
       p.PhoneNumber,
       CONCAT(p.FirstName, '.', p.LastName, '@sqlsavior.com') AS Email,
       p.Birthdate,
       p.Salary
FROM PersonData p;
GO

Testing Azure SQL Database

Let’s start by testing the Azure SQL Database using SqlQueryStress. For the settings, I will use 200 iterations and a thread count of 100. This configuration results in 20,000 iterations. Additionally, I will run this process three times, resulting in a total of 60,000 iterations completed.

Test 1: Inserts. The first test I will perform is inserting data into our tables one row at a time. The code below is what I used for testing. Additionally, I have included a screenshot of the SqlQueryStress settings.

INSERT INTO dbo.Person
(
    FirstName,
    LastName,
    PhoneNumber,
    Email,
    BirthDate,
    Salary
)
SELECT @FirstName,
       @LastName,
       @PhoneNumber,
       @Email,
       @Birthdate,
       @Salary;

Next, here is the code for filling in the Parameter Substitution setting.

;WITH PersonData
AS (
   SELECT TOP (1000)
          SUBSTRING(   'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ',
                       (ABS(CHECKSUM(NEWID())) % 26) + 1,
                       8
                   ) AS FirstName,
          SUBSTRING(   'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ',
                       (ABS(CHECKSUM(NEWID())) % 52) + 1,
                       15
                   ) AS LastName,
          ABS(CHECKSUM(NEWID()) % 9999999999) + 1000000000 AS PhoneNumber,
          DATEADD(DAY, RAND(CHECKSUM(NEWID())) * (1 + 7300), '1980-01-01') AS Birthdate,
          ABS(CHECKSUM(NEWID()) % 100000) + 25000 AS Salary
   FROM sys.all_columns AS n1
   )
SELECT p.FirstName AS FirstName,
       p.LastName AS LastName,
       p.PhoneNumber AS PhoneNumber,
       CONCAT(p.FirstName, '.', p.LastName, '@sqlsavior.com') AS Email,
       p.Birthdate AS Birthdate,
       p.Salary AS Salary
FROM PersonData p;

Test 2: Updates. For the second test, we will update 20,000 rows three times, resulting in a total of 60,000 updates. The script I’ll use is provided below.

UPDATE dbo.Person
SET BirthDate = DATEADD(DAY, 1, BirthDate),
    Salary = (Salary * 1.001),
    PhoneNumber = '5555555555'
WHERE Id = @Id;

Here is the code I use for parameter substitution. It generates 20,000 random integers between one and one million.

SELECT TOP 20000
       (ABS(CHECKSUM(NEWID()) % 1000000) + 1) AS Id
FROM sys.all_columns c1;

After running the test, the following metrics are from the DMV script above, along with the average clock time from SqlQueryStress. The times are measured in milliseconds (ms).

Now, let’s repeat the same process, but this time, let’s use a Fabric SQL database.

Testing Fabric SQL Database

If you try to follow along, ensure that you populate the default database name when connecting, either in SQL Server Management Studio (SSMS) or SqlQueryStress.

After repeating the same scripts but on Fabric, I’ve recorded the times below.

Before I reveal the answer, can you identify the main difference between the two tables?

Review the Results

You will notice that the primary difference between the two result sets is the clock time. Out of curiosity, I scaled up to an F16 and executed the same UPDATE and INSERT scripts, which still took about 49 seconds. In this article, I aimed to determine whether there is a performance difference between the two platforms. According to my test results, there is. One thing to keep in mind is that the Fabric SQL database is still in preview. Who knows, maybe by the time it reaches General Availability, the performance will improve. Another possibility is that I might be doing something wrong; with that in mind, I would encourage you to try to replicate my results.

Final Thoughts

One key selling point for creating a Fabric SQL database is that all the friction is removed. What do I mean by a lack of friction? For me, reducing friction typically involves building a habit. For example, if you want to build a morning walking habit, what can you do to make it easier? I would place my shoes by the door, set an early alarm, or even wear my walking clothes to bed. These steps are helpful because morning workouts are hard, and most people don’t want to do them.

Is creating an Azure SQL database difficult? Not really. With an Azure subscription, you can have one set up in under 10 minutes. Along the way, you might ask questions like: How many vCores do I need? What is a vCore? Should I enable SQL authentication? Are there legacy applications that require this? What hardware do I need? Should it be serverless or provisioned? This internal dialogue could continue indefinitely. However, you will likely learn something and appreciate the newly created database even more.

Next Steps

• Near the time Microsoft announced the preview of Fabric SQL databases, I compiled my first thoughts. You can check them out in the article SQL Databases in Fabric. Remember that I wanted to focus only on the positives in that article.
• Do you want to start using Adam Machanic’s SQLQueryStress? I wrote an article to get you started: Getting Started with SqlQueryStress for SQL Server Load Testing.
• If you want to learn more about HammerDB, Steve Shaw delivered an informative session in the YouTube video, HammerDB: A Better Way to Benchmark Your Open Source Database.