Sorry, I somehow thought was from a different person (pietlinden), totally my fault, I was going on auto-pilot.

Scott - I agree that NOLOCK has its purposes, as do all query hints.  Otherwise they wouldn't be in there to begin with.  I was just trying to...

You don't need the extra check on the flag:

WHERE @myFlag <> 1 OR name NOT IN ('A', 'C')

If the DLRID are mostly unique, or mostly come in batches together when not unique, I suggest encoding the DLRIDs into a numeric code.

That is, create a separate table with...

CXCONSUMER is waiting on parallelism.

Have you adjusted the SQL Server configuration for 'cost threshold for parallelism' to be something larger than the default?  The default is 5, IIRC, which is...

That's easy enough, you can pre-calc a proper date range, using either variables or in a cte.  I'll use variables here just because they're easier to adjust for diff test...

To be absolutely sure of correct matches, you'll need something to sequence the rows so that you know the specific order of the rows.  I added an id for that,...

Try this:

GRANT EXECUTE ON xyz.SPName TO LocalSQLaccount AS ADGroup;

SQL alerts have...

Oops,...

 Clustered index scans should definitely be adversely affected by masses of empty space in pages from deleted rows, since the scan will have to read lots more...

See if this gives you the correct totals.  I have not taken performance into consideration yet, just trying to get the correct result.

Be sure to add a date check to...

(1) Agree with ZZ.

(2) Yes, but you will also need to look at missing index stats at least, possibly cardinality data and index operations stats too.

(3) Yes, sort of.  index...

Unless your table is named "sol(nolock)", I would avoid using NOLOCK.  It is in my coding standards that NOLOCK should be avoided except where potential data inaccuracy is acceptable...

Note, too, that the OP did not indiscriminately use NOLOCK everywhere.

In fact, it was used on only 1 table out of 9 (or so).

To me, that is exactly the way...