We are comparing the performance of an on-prem SQL Server and SQL Server running on Azure VM. The Azure VM is created by Azure Site Recovery(ASR). For the testing purpose, we have executed following TSQL on both servers. On-prem server, it executes under ~9 minutes whereas, on Azure VM, it takes ~26 minutes to complete.

declare @id as int;
declare @amount as money, @perc as real, @discount as money;
set @id = (select min(id) from tblCommissions)
while @id is not null
 select @amount = sum(dollar),  @perc= sum(perc) 
 from tblCommissions NOLOCK where id = @id
    update tblSales
        set Commission = Quantity * @dollar 
    where id = @id ;
 set @id = (select min(id) from tblCommissions NOLOCK where id > @id)

Instead of while loop, I tried to rewrite it using set operation which executes in second.

SET ts.Commission = tc.Quantity * tc.dollar 
FROM tblSales AS ts
         SELECT id, sum(dollar) dollar, sum(perc) perc 
         FROM tblCommissions NOLOCK 
         GROUP BY id
) tc
ON ts.id = tc.id

The dominating wait event for WHILE LOOP version is WRITELOG, however, if I use explicit commit the wait event disappears and becomes CPU bound.

As the VM on Azure was created using ASR, everything-CPU/RAM/Database Settings/SQL Server version/Patch Level should be similar. The performance remains the same no matter what kind of disks-SSD/HDD I use. I have tried to use the smallest VM size to giant 32 core VM but it makes no difference in execution time.

My question is even though the while loop is not scalable the execution time for the same set of rows should be the same for on-prem and Azure VM right? What could be wrong or where should focus from here?

  • May be I am silly but did you tried updating stats after you moved to IaaS VM. Try and let me know if you see any improvement. – Shanky May 3 '19 at 7:08
  • @Shanky: Yes, actually that was the first step I performed, after analyzing the trace report. – JSapkota May 3 '19 at 7:17
  • 1
    Do you have the execution plans for both queries? Could you add them? – Randi Vertongen May 3 '19 at 8:17

Your Answer

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

Browse other questions tagged or ask your own question.