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 |
|
notmyrealname
98 Posts |
 Posted - 2011-10-31 : 08:42:49
|
| Hi,My users are experiencing performance issues including timeouts and slow execution speeds on my database. I have found that if i open a a view that is performing poorly in my SQL designer and simply save without making any changes, the problem will go away. Because of this i am led to believe that the act of saving is "cleaning" something up. Is anyone familiar with this behavior? Might there be some database or table properties that i have not set correctly that are causing my views to slow down over time.Thanks for the help. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-10-31 : 08:52:57
|
| Try sp_recompile on the underlying tables - might need to update statistics as well.Are you updating indexes?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
notmyrealname
98 Posts |
Posted - 2011-10-31 : 10:05:40
|
| Quickly looking at sp_recompile it could be a cause. I will see if it helps. |
|
|
|
notmyrealname
98 Posts |
Posted - 2011-11-18 : 11:10:48
|
| Hi nigelrivett,My application executes stored procedures that reference various tables and views. I add the WITH RECOMPILE clause to these stored procedures thinking that it might help. It didn't. Do you know why this doesn't work? Does the WITH RECOMPILE clause on recompile the stored procedure itsel and not the underlying tables and views? Do i need to execute sp_recompile in the stored procedure on all referenced tbales and views?Thanks again for your help. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-18 : 11:41:04
|
| recompile just causes a new query plan to be generated. A recompile ion the table causes a new query plan to be generated for everything that accesses it (probably). I would expect that to fix the issue as the view is part of the query plan of the sp.Try a recompile on the views. Also try a sp_refreshview which should have the same effect as what you were doing to cure the problem.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Timeouts and Slow performance