3

What could explain this big drop in performance in an Azure SQL DB after moving the app from an hosted VPS to an Azure App service?

enter image description here

Here's a typical chart from Query Store's High Variation chart over the past two weeks. The red arrow indicates when I moved the production app from another hosting provider to an Azure App. Prior to moving the app, I experienced zero timeouts. Now, using the same Azure SQL DB, timeouts are triggering frequently for longish queries (but by no means too arduous).

The only other change I made was change the user principle in the connection string. This user only has SELECT, INSERT, UPDATE, DELETE and EXECUTE permissions.

My theories are: - something to do with networking between the app and the db. Resiliency? But I have a SQL exec plan specified - something wrong with the user I set up? - bad plan regression (I have now enabled auto FORCE PLAN tuning) - a problem caused by Hangfire running on two servers simultaneously (now mitigated by moving HF tables to a new DB) - something is triggering some kind of throttling that I cannot figure out.

Here is a chart of timeouts from Log Analytics:

enter image description here

All help appreciated. Note: this site has had almost identical traffic over the past 30 days.

In fact, take a look at this from the SQL DB metrics over the past week:

enter image description here

And here is some Wait info - last 6 hours:

Blue = PARALLELISM Orange = BUFFERIO

enter image description here

rwalter
  • 891
  • 1
  • 7
  • 19
  • You moved the web app to Azure but the DB didn't change, right? – Francisco Goldenstein Mar 08 '18 at 23:00
  • Exactly right. I also changed the db user in the connection string. – rwalter Mar 08 '18 at 23:05
  • I'm now wondering if the middle chart from Log Analytics is misleading as I'm not sure when logging began for that service. For the most part, the site is super fast - just every now and then a long running query seems to bring it down completely. – rwalter Mar 08 '18 at 23:07
  • Same DB and same application should have no impact in DB performance in my opinion. Can you undo the connection string change just to rule out a possibility? – Francisco Goldenstein Mar 08 '18 at 23:22
  • Unfortunately I cannot change the user in the connection string right now. Everything else in the string is identical. The reason I ask is because I saw an answer on SO (although not fully answered, and I can't find it now) that suggested the db user needs access to the Master db, not just your target db. – rwalter Mar 09 '18 at 09:04
  • Can you try updating stats and indexes ,also which metric is over 90% for long periods of time(cpu,io,ram,log) – TheGameiswar Mar 11 '18 at 03:28

0 Answers0