I'm not entirely sure this is an Azure problem because I ran through the same steps on my local machine with similar results.

Hi Sorry, one crucial piece of information that I left out was the fact that I am running DBCC DROPCLEANBUFFERS before I run the query every time. This will mean...

Hi mate I have done a bit of further investigation into this and was wondering if you could help me out further.

I set perfmon running yesterday morning with every counter...

Not that I could see. The query was filtered by is user query though so it is a remote possibility

Hi thanks a lot for this answer. You two have been very helpful. I will definitely be rewriting this query in the suggested manner. Again thanks

CREATE procedure [dbo].[Problem_Stored_Procedure] (

@blank uniqueidentifier,

@blank int,

@blank bit = null,

@blank bit = null,

@blank datetime = null

)

as

if @blank...

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...

Ok so that would indicate that those stored procedures with schema lock waits are trying to modify the schema in some way? This sounds strange because the SPs that are...

So in that case would you say that the schema locks are being caused by something else? I.e. an alter table or something like that?

Also can you clarify which...

Follow up question what do these wait_resources mean?

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

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

and also could the compilations cause schema locks in any way...

We are moving to 64 bit yes I have been pushing for this. Basically this server is not up to the job and I think that they have realized this...