Plan cache issue? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Plan cache issue?

All,
We have a view that, while not very complicated, does access two different databases on the same SQL server instance. Every Monday morning, the view will not return results to the application without timing out. All we have to do to get it working again is to open the view in design mode, add some white space (or some other minor change) and then save it. It then returns results very quickly. Any thoughts? My initial reaction is that there is some process happening over the weekend that severely outdates the statistics and that every time we save the view, the existing execution plan is flushed. The only thing we see happening on the weekend is a database backup, but I’m not sure why that would have anything to do with it. Our first step will be to move that backup to different day and see if the problem follows the backup. If anyone can come up with a theory on why this is happening, I’d love to hear it. Thanks,
Greg
Can you eliminate database optimization tasks on the tables & stored procedures that are involved in this view? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Not sure if this is your problem, but next Monday try executing sp_refreshview on that view, before you use your workaround and see if that changes something. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Both good points. I will do some further investigation into optimization tasks that may be running.
My understanding of sp_refreshview is that it should be run when the underlying objects change, but I *think* we have no changes there. However, I will check that out. That seems more likely then something that changes the data drastically.
One of the databases is an accounting system and I don’t know what all the periodic processes it runs are. thanks again,
Greg
BTW, is this on SQL 2005 server? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Yes, this is on SQL 2005.
]]>