Same query in Azure SQL vs. Local - different execution plan, won't use index

  • Adam Bean

    One Orange Chip

    Points: 26446

    Hey all,

    Very new to Azure SQL and running into a wall on this one. I have the exact same database locally (2019) on my laptop as I do in Azure SQL (P4).

    I've applied the same index to both instances, and locally it works perfectly. In Azure though, it's ignoring it and using an old query plan. There's no way the engine can think the old path is ideal as it causes a very expensive RID lookup. The new index alleviates that and locally I have a nice index seek.

    Thanks!

  • Site Owners

    SSC Guru

    Points: 80378

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Grant Fritchey

    SSC Guru

    Points: 396384

    If you can, post both execution plans. By and large, Azure SQL Database just works exactly the same as regular SQL Server. So, all things being equal, a good index locally should be a good index in the cloud. So, are the queries really identical? Are the statistics up to date? Differences in parameters? Something has to be there. It's possible it's a bug or a difference in the optimizer, but it's likely something else.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    when you say they are exactly the same.... does that mean data content (number of rows etc)?

     

    MVDBA

  • Adam Bean

    One Orange Chip

    Points: 26446

    Apologies for the delayed follow up, been a bit of a fire fighting exercise as of late.

    When I say the same, I mean 100% the same. I pulled down a copy of the database locally to play with both. Locally, it took to the new index immediately, Azure ... did not change, even after updating stats.

    Oddly enough, after we did a DBCC STACKDUMP (to move hosts), it all of the sudden liked the new index and began to use it! This also cleared up overall performance issues we were seeing (this view was taking 12+ hours and timing out, now it takes ~15 minutes).

    Yet now this morning, after we had another failure ... it's back to using the old index, and the performance is back to being a turd (with no other contention and very minimal utilization). Still playing, but definitely some oddities here.

  • Grant Fritchey

    SSC Guru

    Points: 396384

    Since you can't do a backup & restore locally, you're doing a BACPAC I assume? Since that rebuilds the indexes, it also resets all statistics. I'd focus there as the likely culprit. Capture the plans before and after the change. Use SSMS (or Plan Explorer), to compare the two to see what the row estimates are between the good plan and the bad plan.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Adam Bean

    One Orange Chip

    Points: 26446

    Yeah, a BACPAC (which was painfully slow to backup/restore).

    Updated stats on both sides. As soon as I did the DBCC STACKDUMP, it worked just fine. Something doesn't add up.

    Being that it's back to the old index now, I'm willing to wager that if we do the fail-over again, we'll see the same behavior.

  • Grant Fritchey

    SSC Guru

    Points: 396384

    Nuts. I'm stuck. Have you compared the plans?

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply