sp execution plans - (Timeout)
-
I have 2 stored procs that run probably 50 to 60,000 times a day. Both of these sps run very fast and are not pulling back much data they are returning on avg 100-200 rows. But for some reason the 2 sps will start to time out. I tried running sp_recompile (i'm guessing the execution plan is stale) but that doesn't seem to work. The only thing that works is if i drop and recreate the stored procs. They will both run for about a day and then start timing out again. Yes both sps are using small temp tables. Anyone else experiencing the same problem and if so is there a better solution than dropping and recreating the sps? Thanks!!
-
Update stats nightly on the temp tables and see what happens
-
Are those global temp tables or are they created on the fly and destroyed after each execution.
-
Created on the fly and destroyed after each execution is there a better way?
-
It's only better if it works
.Try changing the code to table variables instead. Make sure you test first to see if you have the same basic performance. Then into production to see if you avoid the performance degradation.
Also, have you checked for dead locks, or intensive queries on the server at the times where it fails (like a reindexing job, anti-virus running, screen saver taking all the cpu (don't laugh, I heard this one on this site)).
-
LOL! You're right only if it works.
This is a dedicated db machine but i will check all of those things. The table the sp is pulling from holds 80 million records and we had deadlocks and indexing problems in the beginning but that's under control. Just have to figure out a better way for the sps. I'll try the table variables and see what happens? Thank you for your time i really appreciate it!!!
-
NP. If you still have problems, you'll have to post the entire procedure so we may have a better understanding of what going on.
Good luck with that project.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply