Agreeing with Martin — no single view identifies every cause. But your follow-up narrowed it to LCS, so on that specifically.
For a live incident, SQL Insights under Environment monitoring is still your fastest route. Live View shows currently executing and blocking statements, so "is something stuck behind a lock right now" takes about a minute. The Queries tab has predefined diagnostics including deadlocks and long-running queries, and Actions lets you mitigate from the same place.
Worth knowing what's changed, since there's confusion about this. The September 2024 deprecation wave hit the Activity tab only — eleven queries covering sign-ins, form errors, slow interactions, browser sessions and similar. SQL Insights was not part of it and isn't deprecated. Health Metrics is in maintenance mode.
Also worth being clear that Application Insights is not the replacement for SQL Insights. It's application telemetry — form loads, exceptions, custom events — and Microsoft has been explicit that it isn't a SQL performance monitor and won't show you blocking statements, CPU or DTU. The two run in parallel. So keep using SQL Insights for database-level problems.
Where Application Insights does earn its place is your own code. You can emit custom events from X++ so your customizations show up in telemetry instead of being invisible during exactly this kind of investigation:
https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/monitoring-telemetry/monitoring-developer-add-custom-signals
