March 21, 2020 at 3:57 pm
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!
March 22, 2020 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 23, 2020 at 10:23 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 23, 2020 at 10:35 am
when you say they are exactly the same.... does that mean data content (number of rows etc)?
MVDBA
March 25, 2020 at 1:49 pm
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.
March 25, 2020 at 1:59 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 25, 2020 at 2:16 pm
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.
March 25, 2020 at 3:06 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply