We have migrated SQL Server Standard Edition from 2008 R2 to 2014. After the migration, we are seeing a huge amount of blocking, and overall the instance is very slow.
The major wait we are seeing is
Resource_Semaphore_Query_Compile. Some of the queries are requesting more memory and waiting on compile time.
This new server is a beast, Memory : 1TB and CPU : 2 physical cores with 30 logical processors each (60 total). Since this is Standard Edition, max memory is set to 128GB, and I believe there are some issues with maxdop setting and how SQL Server 2014 is utilizing the processors. Maxdop was set to 4 and we changed it to 2, still no luck.
Regarding the resource semaphore wait types: I updated stats on all databases with full scan. We cannot tune the queries as they are encrypted vendor code which used to perform perfectly well on SQL Server 2008 R2.
I cannot change the database compatibility level. I have enabled soft NUMA nodes on this instance by enabling startup trace flag 8079. It created 4 NUMA nodes (before the change, it was 2 NUMA nodes - I can see that in the SQL Server error log).
We are still seeing the
Resource_Semaphore_Query_Compile waits. Some of the queries are requesting 24GB of memory.