unfilled

Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

ЕМНИП, по-умолчанию сетевые соединения запрещены, нужно явно разрешить их через SQL Server Configuration Manager. Возможно, потребуется и перезапуск сервера

Предполагаю, что проблема в том, что:

1. колонка [ClientTableId] в дочерней таблице не индексирована, соответственно, каждое срабатывание триггера вычитывает всю таблицу
   
2. у [ClientTableId] тип nvarchar, который не совпадает с типом колонки Id в родительской таблице, поэтому при каждом срабатывании триггера, требуется преобразование типа nvarchar к int, что "отключит" возможность использования индекса, даже при его наличии
   

Какая у вас версия SQL Server? OPENJSON появился в 2016, ЕМНИП.
Если отдельно подзапрос с OPENJSON выполняется, я бы вынес его в cte, а в USING указал имя cte.

Я бы начал с того, что убрал все подзапросы, джойнил таблицы напрямую. Скорее всего у этого не будет никакого результата, но читать будет проще.
Второе, что нужно сделать - это избавиться от этого:

join (select DocumentRef, DocumentNodeStaticGUIDRef, DocumentNodeValue from DocumentNode) dn2 on dn2.DocumentRef = (select T.[value].value ('.', 'int') from DocumentSettingXML.nodes('/DocumentSetting/PreviousDocumentRef') as T([value]))
 and dn1.DocumentNodeStaticGUIDRef = dn2.DocumentNodeStaticGUIDRef
 where 
 (select T.[value].value ('.', 'int') from DocumentSettingXML.nodes('/DocumentSetting/PreviousDocumentRef') as T([value])) is not null

Попробуйте выкинуть это в нормальную временную таблицу (не табличную переменную) и делать джойн с ней

Nested Loops далеко не всегда плохо, проблема с ним в этом месте в том, что sql server думает, что вернётся 70 тыщ строк (Estimated Number of Rows), а возвращается (Actual Number of Rows) на несколько порядков больше. Дайте ему временную таблицу по которой он сможет построить статистику и он, скорее всего, сам построит приемлемый план.

Если @StartDate и @EndDate - это переменные, а не параметры SP, попробуйте выполнить запрос в том виде, в котором он есть, но явно подсунув туда даты, без переменных. Если это хранимая процедура и это её параметры, попробуйте выполнить её после sp_recompile. Этот абзац актуален только в том случае, если запрос не всегда работает медленно.

Ну и от distinct надо избавляться, да.

внутри рекурсивного cte я бы добавил "счётчик" уровней, типа

WITH [tree] ([sp_id], [sp_parent], [lvl])
    AS
    (
    SELECT [sp_id],
    [sp_parent],
    1 as [lvl]
    FROM [site_pages_new]
    WHERE [sp_id] = 2
    UNION ALL
    SELECT [inner].[sp_id],
    [inner].[sp_parent],
    [tree].[lvl] + 1
    FROM [site_pages] AS [inner]
    JOIN [tree]
    ON [inner].[sp_parent] = [tree].[sp_id]
    WHERE [tree[.[lvl] < @level
    )

Ну а <= или < использовать - по месту надо смотреть, что вы и как передавать будете

Не уверен, что это будет самым простым способом, но чтобы это сделать - вам нужно заскриптовать все внешние ключи в текущем состоянии, потом сделать что-то вроде такого (не работает с композитными ключами), удалить ваши данные и восстановить внешние ключи по сохранённым скриптам.
Учтите, что при пересоздании внешних ключей, SQL Server будет сканировать таблицы/индексы (при наличии) по этим ключам, чтобы убедиться, что в них нет некорректных данных. Этого можно избежать с помощью NOCHECK, но тогда ключи не будут trusted.

Файл позволяет вернуться к любой точке времени и восстановить базу на указанное время.

Этот файл не позволяет этого сделать. Правильно настроенное резервное копирование, включающее в себя резервные копии журнала транзакций позволяет восстановить БД на любой (с оговорками) момент времени.

Подскажите как через планировщик настроить сжатие и очистку этого файла после успешно сделанного бэкапа?

Полный бэкап не влияет усечение журнала транзакций. Если ваша база в полной модели восстановления (что, видимо, так), и вы делаете только полные резервные копии - ваш журнал транзакций будет только расти, сколько бы шринков (DBCC SHRINKDATABASE/SHRINKFILE) вы не делали.

Грамотный подход для того, чтобы не страдать от разрастания журнала транзакций: почитать про модели восстановления; виды резервных копий (включая резервные копии журнала транзакций); настроить резервное копирование журнала транзакций с такой частотой, которая обеспечит оптимальные для вас: размер файла журнала транзакций и объём допустимой потери данных; разово обрезать журнал транзакций с помощью DBCC SHRINKFILE.

Быстрый подход: перевести бд в простую модель восстановления (alter database set recovery simple), выполнить инструкцию из первого ответа и забыть про рост журнала транзакций и восстановление на момент времени.

Вы делаете очень странную вещь - бэкапите логи, потом переводите БД в симпл, ломаете цепочку восстановления и возвращаетесь обратно в полную модель восстановления.
Вы не сможете нормально воспользоваться своими бэкапами журнала транзакций после перевода в простую модель восстановления.
Если вы хотите всё-таки использовать полную модель восстановления и иметь возможность восстановления на любой момент времени - делайте резервные копии журнала транзакций чаще и после того, как копия снята делайте DBCC SHRINKFILE без перехода в симпл.
Если это не помогает - смотрите log_reuse_wait_desc в sys.databases - что именно мешает обрезать файл.
Не забывайте, что на время приращения журнала транзакций, БД переходит в режим "только для чтения" - формально, конечно, ничего никуда не переходит, просто все операции записи ждут пока ЖТ вырастет.

Размещать witness на отдельной машине. Кворум: как следящий сервер влияет на доступность базы данных.

Раньше был DBCC PINTALBE, сейчас такого механизма нет (да и PINTABLE особо не рекомендовалось к использованию).
ИМХО, вы зря не хотите разбираться с запросами и индексами. «Закрепление» таблицы в памяти может вам и не помочь. Например, вы таблицу «закрепили», а пользователь бабахнул запрос с 10 кросс джойнами этой таблицы — в данном случае, вне зависимости от того «закреплена» она или нет, памяти уйдёт намного больше чем предполагалось.
Плюс, suser_name() никак на кэширование данных не влияет — почему вы считаете, что кэширование не дееспособно?

Бэкапить основную файловую группу отдельно. Пример есть здесь (там же, в ответе, ссылка на пост где приводится пример восстановления). Только надо будет дополнительно потренироваться в восстановлении.

Тут надо понимать, что такого типа данных datBlob в SQL Server не существует, то есть это какой-то самописный пользовательский тип, описание которого можно достать только у автора этого типа (нельзя посмотреть код которым он был создан) — отсюда могут быть и проблемы при CAST/CONVERT (теоретически). Плюс, типа String в SQL Server тоже нет вместо него используется VACRHCAR/NVARCHAR.
Если вы еще не пробовали делать CAST(Picture AS nvarchar(max)), то попробуйте — вдруг получится. А если пробовали, но не получается — ищите автора и исходники этого типа данных.