My scenario, without getting into the detail... Our nightly operations revolve around restoring a ~700GB 2008 compatibility level database from a SQL Server 2016 machine to SQL Server 2017, let's call this database NightlyDB, to support our reporting operations.
Once a month, we perform an additional restore from the same backup file to another database on the same server - MonthlyDB. This occurs after NightlyDB is restored.
The restored to server is Windows patched/restarted on a monthly basis. After the restart, one of the overnight stored procedures takes noticeably longer that normal (this is in ReportingDB (Compatibility Level 2012) pulling from NightlyDB). It takes a couple of runs of our overnight operations for events to settle back to normal. The procedure concerned performs a truncate/large insert (~17 million records)/numerous updates. Query store isn't enabled on ReportingDB and the time taken to restore is unaffected by the restart, roughly 1 hour.
During the update steps of procedure there are a lot of global update statements that could be rolled into a few large update queries with case statements replacing the where clauses. This has clawed back some time in a testing scenario.
When we perform the restore over MonthlyDB, even though our nightly operations don't touch this database, the same ReportingDB procedure has the same symptoms as that of the patching restart. It is the following overnight job after the restore of MonthlyDB where the problem procedure is effected. I've trawled the logs for errors and nothing obvious has shown up.
Normal operations ~50-60 minutes - Truncate/insert ~15 minutes, Updates ~45 minutes
Night of most recent restart ~3 hours 15 minutes - Truncate/insert ~1 hour, Updates ~2 hours 10
Last additional restore ~2½ hours - Truncate/insert ~1 hour, Updates ~1½ hours
The restart is the key event, but I'm scratching my head as to why restoring over MonthlyDB should impact the ReportingDB procedure. I'm planning to run a SQL Server Profiler trace for the overnight period following the restore of MonthlyDB, but I'd be grateful for any further guidance/pointers to identify what's going on and for SQL Server to tell me it's having an issue.