One of our systems has about 500 stored procedure scripts that need to be run on a release. To do this I concatenate all the scripts together in one file....

All other things being equal it will take longer to update the table with more columns. If only because there is going to be more disk IO in reading the...

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...