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.

I'm still not 100% sure of your rules, but hopefully if needed you can adjust this to get what you need:

SELECT
    jec.main, jec.sub,


select
    right(space(12)+convert(varchar(20),cast([User_Info_Currency_1] as money),1), 12) as [Replacement Cost]
from (
values(
523.88000),(
60689.76000),(
48860.26000),(
77239.26000),(
132227.26000),(
65668.18000),(
71523.34000)
) as EQSA0345(User_Info_Currency_1)

Reforrmat  the SSICat data and not the ep data, since there should be far fewer SSICat rows.

How is the EncounterProc table clustered?

Hopefully you don't actually need...

SELECT
b.Period AS B_Period,
a1.Period AS A_period,
a1.SomeValue
FROM #TableB b
OUTER APPLY (
    SELECT TOP (1) a.*
    FROM #TableA a
   ...