I use SQL Server in various environments, including Azure services, virtual machines, and locally on my laptop. Although there is some information about the performance of these hosting environments, such as DTU metrics, virtual environment specifications, or physical machine specifications, I find it quite difficult to compare them.

For example, it’s challenging to compare my laptop’s performance with Azure hosting that has specific DTU ratings. Sometimes, I would like to have a comparison of my laptop’s performance with a specific number of DTUs.

I wrote a script that, while not perfect, provides some visibility into these comparisons. The results were quite surprising, showing that my private laptop performs really well.

I have two questions:

1. Do you think such a comparison makes sense?
2. Is it allowed, according to the license, to publish the results of such tests?

Code:

-- Simple test of performance SQL Server, useful for simple comparison of server performance

IF OBJECT_ID('tempdb..#sp_PerformanceTest') IS NOT NULL
    DROP PROCEDURE #sp_PerformanceTest;
GO

CREATE PROCEDURE #sp_PerformanceTest 
    @ResultOfTest INT OUTPUT 
AS
BEGIN
    DROP TABLE IF EXISTS #TempTableA

    CREATE TABLE #TempTableA
    (
        ID int PRIMARY KEY,
        RandomString nvarchar(4000)
    );

    DROP TABLE IF EXISTS #TempTableB

    CREATE TABLE #TempTableB
    (
        ID int PRIMARY KEY,
        RandomString nvarchar(4000)
    );

    DECLARE @counter int = 1;
    DECLARE @i int;
    DECLARE @randomStringA nvarchar(4000);
    DECLARE @randomStringB nvarchar(4000);
    DECLARE @characters nvarchar(62) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
    DECLARE @length int = 4000;
    DECLARE @nbOfRows int = 6000;

    WHILE @counter <= @nbOfRows
    BEGIN
        SET @randomStringA = '';
        SET @randomStringB = '';
        SET @i = 0;
    
        WHILE @i < @length
        BEGIN
            SET @randomStringA = @randomStringA + SUBSTRING(@characters, CAST((RAND(@counter * @i) * 62) + 1 AS INT), 1);
            SET @randomStringB = @randomStringB + SUBSTRING(@characters, CAST((RAND((@nbOfRows+3+@counter) * @i) * 62) + 1 AS INT), 1);
            SET @i = @i + 1;
        END;

        INSERT INTO #TempTableA (ID, RandomString) 
        VALUES (@counter, @randomStringA);

        INSERT INTO #TempTableB (ID, RandomString) 
        VALUES (@counter, @randomStringB);

        SET @counter = @counter + 1;
    END;

    DECLARE @StartTime datetime, @EndTime datetime;
    DECLARE @Duration int;

    SET @StartTime = GETDATE();   
  
    SELECT COUNT (*) AS count INTO #null
    FROM #TempTableA a
    JOIN #TempTableB b ON a.RandomString LIKE '%' + left(b.RandomString, 7) +'%'

    SET @EndTime = GETDATE();    
    SET @ResultOfTest = DATEDIFF(MILLISECOND, @StartTime, @EndTime);
END

------ The 'TestResult' returned represents only the duration of the actual test.  It does not include the time spent on data preparation.

DECLARE @RunCount int = 3; -- How many times should the test be executed if the result is an average returned from all test runs? It is recommended to execute it 3 times

DECLARE @ResultofTestItem INT;
DECLARE @TotalTime int = 0;
DECLARE @LoopCounter int = 0;

WHILE @LoopCounter < @RunCount
BEGIN  
    EXEC #sp_PerformanceTest @ResultofTest=@ResultofTestItem OUTPUT;
    set @TotalTime = @TotalTime + @ResultofTestItem
    SET @LoopCounter = @LoopCounter + 1;
END;

Declare @AverageTime int;
set @AverageTime=@TotalTime/@RunCount;

DECLARE @Minutes int = @AverageTime / (60 * 1000);
DECLARE @Seconds int = (@AverageTime % (60 * 1000)) / 1000;
DECLARE @Milliseconds int = @AverageTime % 1000;

Select  (FORMAT(@Minutes, '00') + ':' + FORMAT(@Seconds, '00') + '.' + FORMAT(@Milliseconds, '000')) as TestResult