I'm not sure how far I'd go beyond:

Name

This is what it does, in short, clear, descriptive phrases

These are the parameters

These parameters are required

These parameters have default values and here they...

So, for actively running queries, I recommend the DMVs, sys.dm_exec_requests is a good starting point. You can join that to other DMVs to get the execution plan, the SQL statement...

Well, just in case you have more issues like this, I'd like to recommend my books. Query tuning is a pretty vast topic. Follow the links below.

Good article (and yeah, I know it's older, I just saw it). I'm a huge proponent of Extended Events, so it's awesome to see them getting promoted.

One note, the deadlock...

That feeling when you want to point out that someone isn't correct about what they said, but you don't want to derail a topic and / or...

That feeling when you want to point out that someone isn't correct about what they said, but you don't want to derail a topic and / or...

That feeling when you want to point out that someone isn't correct about what they said, but you don't want to derail a topic and / or annoy said...

STATISTICS_IO and TIME is not recommended to be kept ON at all the times as you rightly mentioned and should be immediately SET to OFF once we have the...

As for the queries being slow, check out the execution plan to see how the deletes are being derived by the optimizer. It's possible to tune deletes (sometimes). You may...

I advice to use the SET STATISTICS_IO ON and SET STATISTICS_TIME ON. It will give you simple yet meaningful info for optimizing your query or choosing the best performing...

So, execution plans are a poor way to measure query behavior. The best way to measure query behavior is by capturing execution time, I/O and CPU. Execution plans are how...

Thanks a lot for suggestions

Role and permission of dba in this project allow to use Query store (no extended events)

Dba might suggest and if needed create new indexes (preferably...

correct - it is fragile. and very frustrating - but with the correct usage and a DBA checking up on it all the time it can be very useful....

In order to measure the performance of the query, you'll need all 150 parameters (or however many are required and don't have defaults). There's no escaping this.

So, probably, best bet...

Have you looked at the error logs specified in this error message to find additional information?