We have both a V8 & V13 website running on Azure appservice which is Plan S1 and Azure SQL Server S0 / 10 DTU's. The site has about 50k+ nodes in the tree. We're seeing a huge amount of SQL timeouts and locks, which we're trying to resolve. What specification of appservice & SQL database's do other people use? The database is setup with DTU based pricing, should we be using a v-core based database? We've followed the recommended Azure setup here: https://docs.umbraco.com/umbraco-cms/13.latest/fundamentals/setup/server-setup/azure-web-apps

We only use SQL Server VMs in Azure. Only time we've had problems with timeouts and locks is when background jobs misbehave, taking locks on stuff. What plugins/addons/etc do you have? Do you have any custom background jobs?

We have a product importer for Vendr on one of the sites, but we can see that this hasn't been hit today. SQL turned up to 300 DTU's and still maxing out

What do the logs say is going on?

I'm seeing hundreds of these exceptions in logs: https://cdn.discordapp.com/attachments/1334110889472950293/1334120478469656656/message.txt?ex=679b6004&is=679a0e84&hm=c78ee0d8e54c401e079ba69080c42bf5e165d4cd9d581d2af836e0fc9bd2bf0f&

That's not an unexpected lock timeout. It looks like a good old congestion issue or database throttling issue.

UrlTracker

is mentioned a few times in that stack trace. Also, you said you set it up as in the docs but use an S0 instead of S2 (minimum recommended) database?

We've bumped the db upto S4 (200DTU's) and still maxing out at times

Cool, that's too high, even Our Umbraco only runs an S2 at 50DTUs and that's on Umbraco v7 which is far from optimized. So yes, as @kdx-perbol suggests, I would investigate where all that DB traffic is coming from, unless your editors are constantly editing it shouldn't see this amount of DB problems. And again, I do wonder what is going on with the URLTracker here, my investigation would definitely start there, especially as that seems to be the cause of the exception you posted. Try and look at all the exceptions and see what percentage of them contain the URLTracker in the trace.

If all of them have

UrlTracker.Core.Intercepting.NoLongerExistsInterceptor.<InterceptAsync>d__4.MoveNext()

in them I'd say you have some megajob that "handles missing pages" or something that never completes and fires constantly for some reason. Is that an internal Umbraco

UrlTracker

, or something plugin/addon?

Thanks @Sebastiaan @kdx-perbol we're seeing a high number of requests for url's / images that don't exist i.e autodiscover/autodiscover.xml, and I think UrlTracker is then trying to find these with db lookups. We've added some cloudflare filters to block these requests and the db usage is coming down.

Might also be worth a check to see if you have the latest version installed! And I'd check the issue tracker/PRs for similar reports https://github.com/Infocaster/UrlTracker

Don't forget to send Infocaster your SQL Azure invoice and a gold medal for outstanding DDOS enablement. 🙂

With an attitude like that I sure hope that you pay for all the software that you use 😅

now now children 🤣

@Ian Houghton there is an app setting or there was where you could turn off the writing of 404s to the database for Url Tracker. Also I agree with the 50DTUs

Hello hello, I see URL Tracker is mentioned! The URL Tracker in Umbraco 8 and below can really hog your database resources. We see this ourselves as well. We don't do any support for Umbraco 8 anymore, but I have personally put extra effort into optimizing the URL Tracker for Umbraco 13. I would definitely recommend updating to the latest v13.

Ah I thought this was v13, but might be v8! Did that error come from v8?

I noticed "IncomingRequestEventSubscriber" in the stacktrace in the .txt file, which, iirc, is specifically from the v8 version.

Okay, this is totally my bag... bear with me as I ask a LOT of questions. When you say "maxing out" what does that look like? 100% DTU utilisation on the database - do you have a graph? It would be helpful to double check the bottleneck is the database itself and not IO/thread starvation etc. If so, can do this approach to find what queries are using up all the resources? https://learn.microsoft.com/en-us/azure/azure-sql/database/query-performance-insight-use?view=azuresql You mentioned Vendr. Is that for both these sites? The performance dynamic with Vendr will be very different from a "regular" umbraco site as it talks to the database a lot more. An S1 web app is not really good enough for high traffic/performance Umbraco sites and certainly not good enough for Vendr - it uses magnetic storage and the network IO is slower. For a site that's making a lot of SQL (TCP/network) calls that matters. Starting point for us is always P0V3. Recommended database SKU is tricky as it depends on node count, content complexity and versions. It's very easy to have a site that runs fine but is incapable of a cold boot or upgrade (with inevitable database reindexing) as it needs to read every property value of every node.

- We run most of our sites in S0, S1 or S2, but most on S1. - Check is The SQL Database in Azure has performance recommendations. I once made the mistake to very actively use the ConsentService in Umbraco, which is practically just a CRUD service directly on the database. Creating an additional index on the database made the DTUs from regularly hitting 80-90% to 4%.

For reference, the load Umbraco incurs on our SQL Server Azure VMs is minimal. And we are on a relatively cheap VM tier. We use Azure SQL as well for other stuff, but we've deemed SQL Server a better fit for Umbraco. (Including Umbraco upgrades.)

the error came from V8 (which has Vendr) installed, but also a Product Importer (but this only runs on demand in hangfire)

Cool, when you say settled down, what does that mean in terms of stats? Response times? DTU utilization on the database? Fewer errors?