Azure – SQL Azure insert query ten times slower on V12 than V11

azureperformancequerysql

I noticed performance degradation about two weeks ago impacting one of our applications with an SQL Azure backend. It's running on V12 on tier S1 90% of the time, sometimes scaling to S2, S3 or P1.

Basically i was experiencing significantly slower query execution times, especially on INSERT queries. So i tested and benchmarked, tested and benchmarked and then tested and benchmarked some more. With every step i tried to take uncertain factors out of the equation. Turned out that since we moved to V12 S1 performance was way slower than it used to be.

What i eventually came up with was easy to reproduce:
Create two new Sample databases (Adventureworks_LT) in the Azure portal. One on a new v12 server, and the other on a new V11 server. Both tier S1.

Then run my benchmark (sort of) on both:

DECLARE @start_time DATETIME, @end_time DATETIME
    SET @start_time = CURRENT_TIMESTAMP

    DECLARE @cnt INT = 0;
    DECLARE @until INT = 100;
    DECLARE @timeNow DATETIME;

    WHILE @cnt < @until
    BEGIN
       Set @timeNow = CURRENT_TIMESTAMP;

       INSERT INTO dbo.ErrorLog
        (   
            ErrorTime,
            UserName,
            ErrorNumber,
            ErrorMessage
        ) 
        VALUES 
        (    
            @timeNow,
            'BENCHMARK',
            DATEDIFF(MILLISECOND,@timeNow,CURRENT_TIMESTAMP),
            'BENCHMARK'
       )

       SET @cnt = @cnt + 1;
       WAITFOR DELAY '00:00:00:500'; /* wait 500 miliseconds*/
    END


    SET @end_time = DateAdd(MILLISECOND,(@until)*-500.,CURRENT_TIMESTAMP)
    /*subtract 500ms per iteration to make up for the built-in delay*/

    SELECT DATEDIFF(ms, @start_time, @end_time) as 'total query execution time', DATEDIFF(ms, @start_time, @end_time)/@until as 'average query execution time'

    SELECT  * FROM sys.dm_db_resource_stats;

My results:

Average execution time V11: 17ms

Average execution time V12: 131ms

Some runs the difference is bigger than others, but V11 is vastly outperforming V12 every single time.

sys.dm_db_resource_stats shows now signs of maxing out DTU limits, or even being close to that. So what do you reckon is going on here? I'm convinced i'm on to something, but my microsoft support guys keeps saying, optimize, scale up, profile your queries etc.

I guess i'm just looking for someone with a similar experience that did get to a root cause with microsoft, or even someone that can tell me my benchmark is no good.

Best Answer

I received an answer from microsoft support which sort of makes sense. Anyway for future searches:

No matter what the platform, the editions (not Web/Biz) provides capacity to ensure the DTU and response constraints as specified by https://azure.microsoft.com/en-us/documentation/articles/sql-database-benchmark-overview/#metrics (look at the Metrics section) The response time constraints for basic and standard are expressed as seconds at 90 percentile bar and thus implicitly do not guarantee ms order average.

To summarize: We cannot compare between V1 and V12 performance by measuring the average milliseconds to complete specific statement.

Related Topic