0

I have the same database on both SQL Azure and on premise. Most of my queries run in similar time on the two instances but I have a handful of queries where the performance difference is on the order of 60x. I suspected that maybe I just didn't have a sufficiently large instance so I scaled to a 4 vCore instance with ~20 gig of memory. As my entire database is only 4.5 GB this seemed like it would be able to hold the entire database in memory.

Turning on IO stats and running the query on the on premise box yields results like

Table 'tblCurrentPlateActivity'. Scan count 1, logical reads 201, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblViolationStatus'. Scan count 1, logical reads 300, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblCostStructureTier'. Scan count 101, logical reads 401, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblViolation'. Scan count 474, logical reads 2106236, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblCostStructure'. Scan count 0, logical reads 190, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblProvinceState'. Scan count 0, logical reads 200, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblGroup'. Scan count 0, logical reads 200, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#GroupIDs___________________________________________________________________________________________________________00000000011F'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblLot'. Scan count 0, logical reads 215, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#LotIDs_____________________________________________________________________________________________________________000000000120'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#PlateProvinceStateIDs______________________________________________________________________________________________000000000122'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#ProvinceStatesIDs__________________________________________________________________________________________________000000000121'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblViolationType'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#LotDate____________________________________________________________________________________________________________00000000011E'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

As you can see all reads are logical and the larges culprit is tblViolation which I would expect. On SQL Azure I see similar results for most tables

Table 'tblCurrentPlateActivity'. Scan count 1, logical reads 201, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'tblViolationStatus'. Scan count 1, logical reads 300, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'tblCostStructureTier'. Scan count 101, logical reads 401, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 200, logical reads 797, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'tblViolation'. Scan count 1, logical reads 2082323, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'tblCostStructure'. Scan count 0, logical reads 190, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#LotDate____________________________________________________________________________________________________________000000000072'. Scan count 1, logical reads 1336728, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'tblProvinceState'. Scan count 0, logical reads 1336728, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'tblGroup'. Scan count 0, logical reads 1336728, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#GroupIDs___________________________________________________________________________________________________________000000000073'. Scan count 1, logical reads 668364, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'tblLot'. Scan count 0, logical reads 1378509, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#PlateProvinceStateIDs______________________________________________________________________________________________000000000076'. Scan count 1, logical reads 668364, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#ProvinceStatesIDs__________________________________________________________________________________________________000000000075'. Scan count 1, logical reads 673785, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#LotIDs_____________________________________________________________________________________________________________000000000074'. Scan count 1, logical reads 871241, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'tblViolationType'. Scan count 0, logical reads 1347570, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

The query plans generated are pretty different likely because the on premise server is 2012. But looking at the query execution in SQL Azure I see some odd discrepancies for example

Number of rows

Now that #tblLot is generated as part of the query and I know that is only has those 474 records in it. What could be causing such a weird discrepancy and how would I solve it? I imagine the query plan is super inefficient because of this miscalculation of number of rows. The on premise database shows the rows correctly.

I'm happy to include query plans here but I'm not really sure what the best way is to do that. Export the .sqlplan?

enter image description here

Update I reduced the query down to a simple query which shows some of the same performance issues

SELECT top 100 Case when ISNULL(cpa.tow, 0) = 0 then '-'            
            else 'TW'   
            end as PlateActivityFormatted,          
            Case when v.ViolationStatusID = 6 then 1
            else 0 end  AS IsOverdueToday,
            v.ViolationStatusID AS OriginalViolationStatusID,               
            v.ViolationStatusID AS RevisedViolationStatusID,
            vs.StatusShortName AS RevisedViolationStatusDescription,  
            tblGroup.Name AS LotGroupName, 
            cpa.tow As PlateTowing, 
            [tblProvinceState].Name as ProvinceState, 
            [tblLot].TaxNumber, 
            [tblLot].Name LotName, 
            v.* 
       FROM [tblViolation] v INNER JOIN 
            [ViolationFinanceInformation] on [ViolationFinanceInformation].ViolationID = v.ID INNER JOIN 
            [tblViolationType] on [tblViolationType].ID = v.ViolationTypeID JOIN 
            [tblLot] ON v.LotID = [tblLot].ID JOIN 
            [tblGroup] ON [tblLot].GroupID = [tblGroup].ID JOIN 
            [tblProvinceState] ON [tblLot].ProvinceStateID = [tblProvinceState].ID LEFT JOIN 
            tblViolationStatus vs ON vs.StatusID = v.ViolationStatusID LEFT JOIN 
            tblCurrentPlateActivity cpa ON cpa.vendorId = tblGroup.vendorid and cpa.plateNumber = v.plateNumber 
            INNER JOIN #LotDate on #LotDate.ID = tblLot.ID 
        where Issued >= #LotDate.Start 

Both databases perform equally well without the where clause, returning in sub 1s, but as soon as the where clause is added the on premise results remain about the same but the Azure results increase to 22 seconds.

I put the good and bad query plans here:

Good: https://www.brentozar.com/pastetheplan/?id=SyijStC88

Bad: https://www.brentozar.com/pastetheplan/?id=BymeUK0UI

https://1drv.ms/u/s!AmN_cJhXT0rP4WuYMib6bhCMoPon?e=YvFOQb

9
  • 2
    Upload the actual plans to Paste The Plan and add the links to your question. It is common for some queries to regress on newer versions, especially those in need of tuning to begin with.
    – Dan Guzman
    Mar 29, 2020 at 14:52
  • 2
    Also drop the database compatibility level to match your on-prem server, turn on Query Store and test. learn.microsoft.com/en-us/sql/relational-databases/performance/… Mar 29, 2020 at 15:07
  • Thanks @DanGuzman I put the query plans up in my one drive. Is it better to use past the plan? Sorry, I'm not usually a SQL performance debugger.
    – stimms
    Mar 29, 2020 at 15:11
  • And rebuild your indexes on tblViolation, and ensure all the join columns have indexes or statistics. For some reason the bad plan thinks the scan of tblViolation will only output 115 rows. Mar 29, 2020 at 15:18
  • Also verify that your indexes got migrated correctly. The bad plan uses no non clustered indexes. Mar 29, 2020 at 15:23

1 Answer 1

0

One thing to check is the legacy cardinality estimator setting (PROPERTIES -> OPTIONS). When I've hit similar problems following an upgrade, this has proved to be the cause. You'll probably find it's set to 'OFF' in Azure SQL. You could turn it on and see if you get more consistent plans between the two environments.

enter image description here

https://learn.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-ver15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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