I'm experiencing an issue with a intermittently long running stored procedure. I've performed analysis on our production environment to isolate the stored procedure in question and noticed that when I run it, it sometimes results in the size of the TempDB to explode to 5-6 gig. This stored proedure creates 5 temp tables (2 tables containing 9 columns, and 3 with 3 columns each) of which each are populated with approximately 30 rows of data each. These temp tables are joined with each other, which is the only detail I can think of which may be resulting in tempdb size explosion
I have copied the sql code from the the stored proc to Query analyzer and run it, and surprisingly it always runs fast, completing in approximately 6 seconds. But when run as a stored procedure containing exactly the same code, it either takes 6 seconds or 5 minutes. When it takes 6 seconds, I notice only a small amount of growth in tempdb (maybe 2 or 3 meg at the max). When it takes 5 minutes, the tempdb grows to 5 or 6 gig, and processor utilization hits 100% for the full 5 mins.
What baffles me is the intermittent nature of the long run times. The only consistent observation is that when the stored proc takes 5 mins to run, it always results in the tempdb expanding to 5 gig & cpu hits 100% for the whole time. When the stored proc only takes 6 seconds, cpu utilisation is 50% and tempdb grows by only a few meg.
I have experienced long run times executing the stored proc during both busy production times, as well as 2am in the morning when database is idling. There appears to be no real correlation between the amount of available database resrouces and the execution time of the SP. I have reindexed the DB using profiler/ index tuning wizard, as well as defragment / reconstruction of indexes using DBCC and a database maintenance plan. This makes no difference to the reliability of the run times. I have also recreated/renamed the stored proc numerous times, but the same eratic execution times continue to occur. as a side note, the TempDB is sitting on a Raid 0+1 striped volume on a HP dl380 server.
Anyone who can provide some more ideas would be much appreciated.