william.rees.howells (12/20/2013)
In regards to compilations per second I have just checked and it is currently running at 726 batch requests per second with 32 compilations per second so this does not seem like overkill to me. When the server goes through one of these episodes I do not know what the compilations per second is exactly I'm afraid.
Recompiles/sec?
Can you monitor and see if that value is representative, high or low? Monitor for a couple of days, then you'll have a base line to compare to when another problem occurs
I am not really comfortable posting the stored procedure definitions onto the web so are there any suggestions that you could provide me with in regards to the compilations happening?
Not without seeing them. There are a bunch of things that cause recompiles (described in Books Online), but without seeing the procedure, it's going to be very hard to say anything useful.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability