Hi thanks for the response.

I have managed to replicate the compiles on our Dev box.

I set the max memory to 2GB the same as our production server (GilaMonster said that this is the max for 32 bit without /3GB switch and AWE enabled). I then ran 50 concurrent queries from my machine with the load testing tool. These queries were the query that is causing the RESOURCE_SEMAPHORE waits. I then loaded up the load testing tool on the server and started running about 30 concurrent queries. These were the additional queries that were running in the excel spread sheet attached to this discussion.

Once this was all running I looked at sys.dm_exec_requests and low and behold the additional queries that do not cause RESOURCE_SEMAPHORE waits have a mixture of the following:

METADATA: database_id = 5 SECURITY_CACHE($hash = 0x2532319d:0x1)

METADATA: database_id = 9 METADATA_CACHE($hash = 0x3317e3d:0x0)

OBJECT: 5:1417080505:0 [COMPILE]

(Ignore the IDs I just copied them from earlier in the thread).

Each time the production server starts becoming really unresponsive there is a massive queue in sys.dm_exec_requests for this query that is causing RESOURCE_SEMAPHORE waits. From my test I can clearly show that this stored proc can cause compiles on the server. This stored procedure is basically run on every web page on every page load so this is clearly an issue.

How come then, when this issue happened on production the other day, I commented out the SP and could clearly see the number of requests for this problem SP falling slowly to 0, however the compiles continued happening even after this SP was no longer showing in sys.dm_exec_requests.

The question here then is this: Could it be that the server resources are so severely limited that once this problem SP starts causing compiles that the load gets to much and the server basically just falls over and cannot reach equilibrium once more?