Performance of QUERY_HISTORY merge #7
Description
OK, I'm trying to drill into some of the performance problems we are seeing with the DynamicsPerf jobs.
The collect stats job that runs every 5 minutes is taking over 20 minutes to complete.
Most of the time seems spent on the merging into QUERY_HISTORY table.
I've reproduced the issue and attach explain plan.
merge.zip
We've inherited these databases, so I don't know what the history of this system is, I suspect something configured wrong on the system rather than anything wrong with DynamicsPerf itself, but we have had to disable all DynamicsPerf jobs until we get them resolved.