Recently we migrated one of our database instances to a new server. The new server has significantly higher amount of RAM (256 GB) and this has 4 physical CPUs with 10 Cores each. Before this we had a 2 CPU with 2 cores each.
The assumption was that the performance won't go south. But I have observed that some the queries that were running fine before have started to run extremely slow.
I checked the execution plans and I see that the plan hasn't changed much. They all use parallelism in the plan.
Apart from this I have verified that the stats are updated and the indexes are not fragmented.
From what I here, I should be increasing Cost threshold for parallelism to at least 25-30 to prevent simple queries from using parallelism.
Apart from that I also read that I should be setting the MAXDOP server settings to something like 8 instead of the default value 0.
To fix this I recommended that these queries use the MAXDOP=1 option and they are running just like they did before. With this option the queries run within 4-5 seconds. But if I don't use it then the queries run longer than 5-6 minutes.
Has anyone else experienced such a situation?