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;
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.