To answer some questions:

1) do understand how SQLServer does Read Committed with a shared lock (dirty read, phantom read, repeatable read) before you read on. Read books by Thomas Kyte.

2) CTE is captured in memory which is much faster if you have enough memory (you should, unless budget, inheritance issues).

3) the 5 million records came from 25 tables joins, you cannot figure out which ones have changed in any time interval. Also I stated "from business community". As DBA does not get into business requirement background was, (BSA and developer should do so) DBA fixes inefficient queries and tells them the right things to do. You made a point, there is only so much DBA can push back business community.

4) Ideally yes, the best practice is reporting server is a different one from transactional server. Too many companies directly report on transactional data (for budget, refresh, timing or various reasons).

Select ... with (nolock) = Read Uncommitted came along with side effects of dirty read, phantom read, repeatable read. If that meets your business requirements, you can do so.

Too many people are having this problem but they did not understand. This script illustrate the core of the issue (isolation level, any users can run query all day long, they should block as brief as possible, they should make transaction short and fast). Snapshot isolation actually reduces lock/blocking or deadlock.

Snapshot isolation does row versioning which use tempdb so you have to clear the way ahead making sure tempdb is on a fast drive and has enough space.

Insert uses exclusive lock. Insert in small pieces is to allow rowlock not escalated, which the transaction is completed fast and providing breathing room to allow other queries to go thru in between (or block yours).

If you want to see how this code actually performs and solves the problem, all you need is to run the code (replace with a large select) on a server that the select tables and insert tables are busily used. Monitor the activities and record timing etc.

If you don't have the environment, just use begin tran to hold up queries. Do a simple script with begin tran Select and hold up commit, do another session with begin tran select and hold up commit, another session with begin tran insert hold up commit,. .... reverse the above orders, do the same again. You should see all.

I rest my case without charging to your credit card.