So yesterday I posted this question regarding that we have upgraded from SQL Server 2008
to SQL Server 2017
and that we excperience some performance problems.
I got the answer to try updating statistics which worked for one database, but not the others. Should I set compability level from 2008 to 2017? At the current time, compatibility level has been left at 100 (SQL Server 2008).
Old server was a physical server, the new server is a VM (Hyper-V).
After the upgrade the execution plan looked different from the old one and it seems to use Index Spool (Eager Spool) instead of using index. Which means that execution time went from 3 seconds on the old server to 1 minute 50 seconds on the new server.
The only thing I notice that is different from the database that does not have Eager Spool with same query is that the table rows are less, it's 13.000.000
rows on the non-working vs 70.000
rows on the working with correct execution plan.
The plans below are anonymized but I hope there's enough information.
Here is the plan with Eager Spool vs The other databas with Index
I have tried
- Updating statistics
- Changing
cost threshold for parallelism
andmax degree of parallelism
accordingly. - Double check indexes if they're as before.
- I've looked at
READ_COMMITTED_SNAPSHOT
. - I've checked the NUMA configuration.
Another problem is that wait types are high
LCK_M_IS,
CXPACKET,
LCK_M_X,
ASYNC_IO_COMPLETION,
LCK_M_IX,
PAGELATCH_EX,
BACKBUFFER,
TRACEWRITE.
I am starting to run out of ideas.
Max memory is set to leave 4GB for OS, I noticed yesterday that power plan on the host machine was set to balanced but changed it to high perf, but still same problem.
On received advice to update statistics:
Seems to have worked on one of the databases, however I still have a problem on all the other databases.
The old server was a physical server with 8 cores and 32GB RAM. The new server is a VM running on Hyper-V with 4 cores and 64GB Ram.