0

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

skaffman
  • 398,947
  • 96
  • 818
  • 769
user156241
  • 51
  • 4
  • 1
    What does the SP do? Lots of reads or writes or both? – spender May 13 '10 at 00:20
  • and is this the only thing hitting the server? If the SP is inserting rows into a table while another set of queries is running to read that table, you may be running into lock contention simply because other process If this is doing a lot of inserts, you may also be running into some sort of page allocation process. – Jim L May 13 '10 at 00:26
  • suggest you fire up SQL 2008's Data Collector: http://msdn.microsoft.com/en-us/library/bb677248.aspx – Mitch Wheat May 13 '10 at 00:31
  • The store procedure does reads, writes, creates temporary tables, calls other stored procedures. probably 10,000 lines of code total. (I didn't write it, I just have to fix it) At least one of the nested stored procedures recompiles every time it's run. It should be the only stored proc getting called – user156241 May 13 '10 at 01:15

2 Answers2

0

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

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
0

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

user156241
  • 51
  • 4