1

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.

1

RESOURCE_SEMAPHORE_QUERY_COMPILE isn't aided by more memory in older versions of SQL Server. Since you're on 2014, you can use Trace Flag 6498 if you're on certain patch levels.

That TF increases the large query compile gateway dependent on the amount of memory in your server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.