Add code to the start of the proc to log to table.  While you're at it, you might as well log all the parameter values as well, if...

You could automate the change easily enough (including grabbing the parameters), but I understand the reluctance to change all the procs.  You would likely want to capture the previous modify_dates...

Add code to the start of the proc to log to table.  While you're at it, you might as well log all the parameter values as well, if you have...

Yeah, EXCEPT should to that just fine:

SELECT * /*...list_of_cols...*/

FROM dbo.today

EXCEPT

SELECT * /*...list_of_cols...*/

FROM dbo.yesterday

Yep, exactly.

Use NOT EXISTS when possible, as it allows for better execution plans.

Are you on Standard or Enterprise Edition?

If on Enterprise Edition, you can use data compression to reduce the size of the data stored.  Row compression is very little overhead but...

Yes, you should be able to apply NOT to the conditions to get the opposite of the current conditions.

You likely shrunk the log file, but the db needs that log space so it is reallocating it.  Unfortunately, newly allocated log space requires pre-formatting.  That pre-formatting pauses all activity...

No.  There is no way to automatically have compression apply to all tables or indexes in a db.  You must specify compression as part of the table/index create.

We have a monitoring tool that reads Windows Event Log entries for Failure and Success of SQL Job executions.

Hmm, maybe?!:

When that tool reads a "job success" as the last msg...

Therefore, the clus index on the dbo.order_items should be ( order_id, $IDENTITY ) and not just ( $IDENTITY ).  Since the parent key is sequential,...

I think the current query can be tuned up and that it should definitely help performance.  Will it help it enough to meet your needs?  You'll just have to test...

Therefore, the clus index on the dbo.order_items should be ( order_id, $IDENTITY ) and not just ( $IDENTITY ).  Since the parent key is sequential, this key...

If you have very volatile tables like that, you should put them in a separate filegroup and then shrink only the files associated with that filegroup.

You especially do not want...