EXEC sp_MSforeachdb '
IF LEN(''?'') = 9 AND RIGHT(''?'', 5) = ''_prod''
BEGIN
    USE [?];
    IF EXISTS(SELECT 1 FROM sys.columns WHERE object_id =...

You need to either:

Cluster the table by timestamp (if that's how you (almost) always query against the table)
Or
Create a non-clus index on (Timestamp) include (TagName, Value)

You wouldn't typically want to cluster on status, since it tends to change.

However, you'd almost certainly be better off clustering by PROPERTY_ID and /or SOURCE_ID in whatever order.  But...

Quite right on the "out of memory" error.

Overall, though, for best performance with far fewer total indexes:
cluster the CHUB_S_CON_ADDR TABLE on ( CONTACT_ID, ROW_ID ).
Add a...

No need to use resources to recompute the string every time.

Pre-generate all the strings and store them in a permanent table.  Then just pull out the row(s) you...

Agree, a filtered index if possible.

Then lock down the requirements.  If you need to delete all B = 1, whether the date is a future date or not...

It's usually more efficient to use MAX(), particularly for char columns.  GROUP BY for an extra char column requires sorting/hashing that is typically much more overhead.

A CROSS JOIN is good for this:

SELECT o.OrderID, o.OrderPaid, o.TotalTaxes, o.OrderTotal,
    cy.CycleStartPlusOne, cy.CycleEndPlusOne
FROM dbo.Orders o
CROSS JOIN (
    SELECT...

Your ranges have gaps: where would 500,000.50 fall?

At any rate, something like:

SELECT s.sales, ss.Nota
FROM sales s
LEFT OUTER JOIN sales_setup ss ON s.sales...

Don't use that view; because:
(1) it only returns 4000 chars, no matter how long the actual proc is.
(2) INFORMATION_SCHEMA views run slowly and generate overhead that's not needed.