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.
I have tried
- Updating statistics
cost threshold for parallelismand
max degree of parallelismaccordingly.
- Double check indexes if they're as before.
- I've looked at
- 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.