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 |
|
djkraz
Starting Member
2 Posts |
 Posted - 2010-08-13 : 09:03:42
|
| We started receiving timeouts for one of our stored procedures from all 3 of our web servers so I connected to the one that has management studio on it (dev server) and ran the exact same query and it came back instantly. I then tried running it again from the web site on that server and it timed out again even though all other sprocs were still running normally on the site. Just for the heck of it, I recompiled the sproc and it suddenly started running normal again on all 3 web servers... Does anyone have any idea on what could cause this and/or have a solution on how to prevent it in the future? Any help is GREATLY appreciated! |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2010-08-17 : 04:25:56
|
you need to start maintaining your databases..this is the explanation why the recompile solved your problem...FROM BOLquote:
As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after Microsoft SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time it executes Another reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer.
--------------------keeping it simple... |
 |
|
|
djkraz
Starting Member
2 Posts |
Posted - 2010-08-17 : 06:42:19
|
| Thanks for the response but I do maintain my db's. Also, that doesn't really explain why it would run fine from sql studio and not on the web servers does it? I can say with 100% confidence that I was running the sp with the exact same parameters in both scenarios... |
|
|
|
donpolix
Yak Posting Veteran
97 Posts |
Posted - 2010-08-19 : 17:29:07
|
| There might have some "SET options" difference when connecting through ssms and through your app, so each can have its plan even though the sql and parameters are the same.Donn Policarpio |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-08-19 : 17:49:01
|
| Look at your Application Pools for your web sites. If they are using a lot of memory they may page out to disk in order to retrieve and render the query results. I saw this happen a lot at my previous job. If you can recycle them, see if they perform better afterwards. You may then want to try a scheduled recycle, or a triggered recycle based on memory usage. Or get more RAM for the web server. |
|
|
|
|
|
|
|
|
Timeouts from web servers but not studio