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 begin 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) end
Instead of while loop, I tried to rewrite it using set operation which executes in second.
UPDATE ts SET ts.Commission = tc.Quantity * tc.dollar FROM tblSales AS ts INNER JOIN ( 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?