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 |
|
GeneB
Starting Member
2 Posts |
 Posted - 2010-06-22 : 14:39:14
|
| I have a web app that connects to a SQL Server 2005 db. One of the tasks is to display patient procedure/diagnosis history.For unknown reasons, the task times out intermittantly. If i step through the web app code, the breakdown occurs during an .ExecuteDataSet fetch to a stored procedure.Once the problem occurs, the only way to get it working again is to drop/create the stored procedure. It seems to function ok for awhile (hours) then the timeout returns.Other noteable: if i manually execute the stored procedure within mgt studio, it seems to work fine.Im not sure where to start diagnosing this problem, any help appreciated. TIA. |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-06-22 : 14:58:14
|
| Looks like case of 'parameter sniffing'. Search for it within sqlteam, you will find a lot of info. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-22 : 15:40:56
|
| "the only way to get it working again is to drop/create the stored procedure"Try recompiling the Sproce next time, rather than Drop/Re-create?EXEC sp_recompile 'YourSprocName'if that fixes it then maybe specifyWITH RECOMPILE in the SProc body?Or maybe the cause is that the Statistics have not been updated recently / are not being updated regularly (although I would expect it to run for more than a few hours before slowing down) |
|
|
|
GeneB
Starting Member
2 Posts |
Posted - 2010-06-23 : 16:43:31
|
| Kristen - followed your advice. Tried the exec, then modified the sproce to include "with recompile". It's been working great since. Clients are happy, birds are chirping, life is better. Thank you very much! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-23 : 18:38:32
|
 |
|
|
|
rdjabarov
Starting Member
8 Posts |
Posted - 2010-06-29 : 13:34:53
|
| I think "parameter sniffing" is the right answer, and the solution should be to use local variables in place of passed parameters."The data in a record depends on the Key to the record, the Whole Key, and nothing but the Key, so help me Codd." |
|
|
|
|
|
|
|
|
stored procedures needs to be refreshed?