Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
porterboy10
Starting Member
1 Post |
 Posted - 2008-01-08 : 10:03:41
|
| I have a VB app that calls a Stored procedure. It goes thru a list of items (One at a time) and Passes a few parameters to the SP. Each pass takes about 10ms to process. Lately the SP seems to get hung and each call takes up to 80 seconds to process a single transaction. While trying to trouble shoot the problem I found that if I simply open up the SP and re-compile it while the process is running it appear to jump start the SP and the remaining items process in the usual 10ms time. This is a daily process that sometimes works fine and other days it hangs. I was told that it could just be a bad execution plan in memory and when the SP gets re-compiled it creates a new execution plan. If that is the case why would the problem return the next day. It should have the new execution plan in memory. The server was not restarted between days. Does anyone know what could be causing this SP to HANG and what I can do to fix this. Thanks in advance. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-01-08 : 10:30:39
|
| My guess is it is either due to a bad plan or because of blocking.bad plan could be because:The tables that the processes use change dramatically over the course of a day. In other words are sigificant numbers of rows added/deleted/updated? This could cause the need for a different plan. Possible solutions could be either running sp_updatestats or using "with recompile" in the SP definition.blocking:when it is "hung", run: sp_who2 active to see if any blocking is going on. If so you can spid in a dbcc inputbuffer call to identify what is blocking.Be One with the OptimizerTG |
 |
|
|
|
|
|
SP performance/hang question