I do not have a good answer for you. However, I've reached out to see if I can find one.

Reads are not a measure of rows. They're a measure of pages. That includes disk and memory. For a tiny table, it's probably on mixed extents, so while, in theory,...

By its very nature, some of the interleaved execution that we're getting with all the intelligent query processing is going to cause some queries to slow down as the execution...

This is a big stored procedure which has multiple operations SELECTs, INSERTs within it and bunch of tempdb operations? The temp table error was something related to another operation...

First I am still learning this forum so do not know how to properly quote messages but to answer Jeff Moden 2nd statement then 1st.

I concur with your Steps...

No, to use Extended Events for this, you'd have to have a second process that monitors the events for the action in question and then respond from there. There is...

Well, technically, many of those do not match. w.astronecollege.com and ww.astronecollege.com are different more than just whether or not one has http:// or https:// in front. The others, you could...

Well, honestly, this is a tough one. However, I'd probably put Extended Events to work to watch for object changes. However, you could also look to Event Notifications, although that's...

An index on the columns involved in ordering the data may also be useful. Testing is your buddy.

What schedules the monthly change? Whatever that process is, just piggy back on it to then automate the procedure update at the same time.

Insert operations can absolutely block one another. If a lock, or a latch, is held on a page by one operation, then other operations will wait for access. Latches are...

All the DISTINCT operators... Either, your data structures are very poor, so you can't guarantee unique rows based on standard queries, or, someone thinks this is how all queries must...

and speaking of blocking.

whoever did the code does not really understand what "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" and "with (nolock)" do.

apart from the fact that it allows you...

The DBA's right about the concurrency issue.  Also, you should really only create a clustered index on a temp table and it should be created before the table...

You're on the right track. Break it up into parts. If possible, get the log to simple recovery during the operation and keep it clear between chunks of processing. Otherwise,...