recompile views/procedures?

[TravisLaborde]

I have begun to notice strange behavior in my SQL Server 2000 database. From time to time, data shows up "wrong" in certain reports, or fails to return at all. I get various "general network errors" which turn out to not be network errors at all...

What happens is, I simply pull up the view or sproc in question, in Query Analyzer (right click it, select "Add to New Window as Alter&quot, and hit F5, which simply re-compiles it, and BAM, presto, everything works fine.

This of course worries me, that something nasty is lurking deep inside my database. What can I do about this? It isn't "normal" is it?

Thanks,
Travis

 

[tlbroadbent]

Doesn't sound normal. Has anyone changed the base table schema? If columns are deleted or new columns added, recompilation is usually required. You may want to use sp_recompile to recompile all SPs dependent on a table that has been changed.

There may be other times when table schemas have not changed but the data has changed significantly and the compiled procedure is no longer optimized, causing timeouts and disconnects.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm