SQL Server Express 2008 Stored Procedure execution time spikes periodically

Question

I have a big stored procedure on a SQL Server 2008 Express SP2 database that gets run about every 200 ms. Normal execution time is about 50ms. What I am seeing is large inconsistencies in this run time. It will execute for while, say 50-100 times at 40-60ms which is expected, then seemingly at random the same stored procedure will take way longer, say 900ms or 1.5 seconds to run. Sometimes more than one call of the same procedure in a row will take longer too.

It appears that something is causing sql server to slow down dramatically every minute or so, but I can't figure out what. There is no timing pattern between the occurences.

I have the same setup on two different computers, one of which is a clean XP Pro load with no virus checking and nothing installed except SQL server.

Also, The recovery options for all the databases are set to "Simple".

Answer 1

I would suggest breaking out applicable sections into their own stored procedures; there is only one query plan cached per batch.

Answer 2

It looks like my problems happen simultaneously with the SQL Server Plan Cache Object Counts hitting 999 and resetting.