4

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.

  • Just adding that although i'm no expert on the inner workings of SQL server, or SQL azure for that matter, i am aware that i'm doing 100 seperate transactions. Batching the 100 inserts in a single transaction would probably make this test perform almost equally on every possible tier on both V11 and V12. However batching is almost never possible when your queries are called from different requests in a webapplication environment. Therefore, if my S1 tier database has gotten extra overhead on each transaction, as i'm suspecting, this will impact my applications overall performance severely. – enter.net Feb 2 '16 at 9:06
1

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.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.