The customer has lots of waits on RESOURCE_SEMAPHORE_QUERY_COMPILE. To troubleshoot this, we have to look from two angles. First, did customer have many queries needing large amount of compile memory? Secondly, was it possible that other components used too much memory, causing the threshold lowered? In other words, if SQL Server had enough memory, those queries requiring same amount of compile memory would not have been put to wait.
We used this query and captured for several iterations of data to confirm that server didn’t have queries that required large amount of compile memory per se.
It’s nice to have this trick up your sleeve when you simply can’t get a better query in place.