If you set the compatibility level on the database to 2008, do queries still take a long time to run?

I'm not sure, I didn't mean Regions( RegionID) as that's already the PK.
Just ignore that index for now.

Try adding the following indexes
CREATE INDEX IX_#ReportDealCore_1 ON #ReportDealCore(DealID)
CREATE INDEX IX_Activities_1 ON Activities(DealID,Type)
CREATE INDEX IX_DealProperties_1 ON DealProperties(DealID,IsMainProperty,DateRemoved)
CREATE INDEX IX_Regions_1 ON Regions(CityID)
CREATE INDEX IX_DealsToClients_1...

-- Splits a...

If got a stored procedure I use for searching for a string in a database:
IF NOT EXISTS(SELECT 1
      FROM INFORMATION_SCHEMA.ROUTINES
     WHERE ROUTINE_NAME = 'INFSearchAllTables'
      ...

Interestingly there comes a point where the original query using ROW_NUMBER() becomes the fastest. This is when there are not many QueuedAt values for ObjectType/ObjectId combinations. In my test above...

With these type of query you can sometimes get a lot of success from breaking it up into several queries inserting the data into temporary tables, adding indexes to the...

I've had a lot of success rewriting these type of queries (where it's selecting ROWNUMBER = 1) as a cte with a select distinct followed by another nested query with...

If you use a temporary table you should get better performance:
IF OBJECT_ID('tempdb..#t','U') IS NOT NULL
  DROP TABLE #t
CREATE TABLE #t (nId INTEGER IDENTITY, nVal...